Yes, this works nicely. Thanks! Didn’t know about the ‘WITH’ statement
From: postgis-users [mailto:[email protected]] On Behalf Of Rémi Cura Sent: vrijdag 16 december 2016 18:33 To: PostGIS Users Discussion <[email protected]> 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 <[email protected]<mailto:[email protected]>>: 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 [email protected]<mailto:[email protected]> http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
