[postgis-users] Query choces on searching too small area

[Arjen Haayman]

Yes, this works nicely. Thanks! Didn’t know about the ‘WITH’ statement

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
Sent: vrijdag 16 december 2016 18:33
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Query choces on searching too small area

Hey, there is a dedicated "slow query" protocol on postgres user list, and its quite sane.
For instance, it would suggest you to give the version number you use, your hardware, etc etc.
About you query, I guess  your photo are geotagged (i.e. each photo is a point, and maybe you have a precision attribute).
Using ST_Intersects thus could be replaced by ST_DWithin with your precision / default tolerancey, which is safer.

Index not kicking may have many causes, such as outdated statistics (have you vacuum analyse -ed often?), wrong config regarding your hardware (seq_page_cost, and so),
poorly written query, etc.
Anyway your query should not freeze on only 500k geometries, so I'm also guessing that in the table "photo" you not only store photo point / geometry, but also the binary of the photo, which is bound to be dangerous.
So steps to fix your problem
 - update postgres / postgis if you can
 - check stats / vacuum
 - check postgres config
 - rewrite your query for a better form (see example 1 )
 - post on list, this might be a bug
 - rewrite query to force to perform first geometry test then the other (example query 2)
 - create a "proxy" photo_proxy table that contains only photo_id and photo.geometry
 - force use of index via settings (usually a very bad idea)
 - ...

Here is how your query could be simplified :
SELECT
"id",
"filename",
ST_AsText("geometry") AS "geometry",
ST_AsText("center") AS "center",
"angle"
FROM "photo"
WHERE (ST_DWithin("geometry", st_GeomFromText( 'POINT(4.5063099203616 51.923602970634)', 4326),your_precision))
AND "id" IN (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
WHERE ("value" = 'KADASTER') AND ("key" = 'source')))
           AND ("photoId" IN (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
WHERE (key = 'year' AND ( cast(value as int ) >= 1866 AND cast ( value as int ) <= 1981))
))
ORDER BY "filename" LIMIT 36

here is the query to force use of geometric index :
WITH photos_spatialy_close AS (
  SELECT id AS photoId
  FROM photo
  WHERE ST_DWithin("geometry", st_GeomFromText( 'POINT(4.5063099203616 51.923602970634)', 4326),your_precision)
  LIMIT 36
)
, photo_with_correct_metatadata AS (

  SELECT DISTINCT "photoId"
  FROM "photoMetadata"
  WHERE ("value" = 'KADASTER')
    AND ("key" = 'source')))
    AND ("photoId" IN (
SELECT DISTINCT "photoId"
FROM "photoMetadata"
       WHERE (key = 'year'
        AND ( cast(value as int ) >= 1866
        AND cast ( value as int ) <= 1981))
)
, keeping_photo_id_in_both_set AS (
  SELECT photoId
   FROM photos_spatialy_close
  INTERSECTS
  SELECT photoId
  FROM photo_with_correct_metatadata
)
SELECT
"id",
"filename",
ST_AsText("geometry") AS "geometry",
ST_AsText("center") AS "center",
"angle"
FROM keeping_photo_id_in_both_set LEFT OUTER JOIN photo ON ( photoId = id)
LIMIT ...



Cheers
Remi-C

2016-12-16 17:48 GMT+01:00 Sandro Santilli <strk at kbt.io<mailto:strk at kbt.io>>:
On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman wrote:
> QUERY PLAN

[...]

> What does this tell you?

That your query is too complex ?
Check out http://explain.depesz.com

--strk;
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161219/557c59cc/attachment.html>