<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Hey, there is a dedicated "slow query" protocol on postgres user list, and its quite sane.<br></div><div class="gmail_default" style="font-family:monospace,monospace">For instance, it would suggest you to give the version number you use, your hardware, etc etc.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">About you query, I guess your photo are geotagged (i.e. each photo is a point, and maybe you have a precision attribute).<br></div><div class="gmail_default" style="font-family:monospace,monospace">Using ST_Intersects thus could be replaced by ST_DWithin with your precision / default tolerancey, which is safer.<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Index not kicking may have many causes, such as outdated statistics (have you vacuum analyse -ed often?), wrong config regarding your hardware (<tt class="gmail-VARNAME">seq_page_cost, and so</tt>),<br></div><div class="gmail_default" style="font-family:monospace,monospace">poorly written query, etc.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">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.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">So steps to fix your problem<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - update postgres / postgis if you can<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - check stats / vacuum<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - check postgres config<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - rewrite your query for a better form (see example 1 )<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - post on list, this might be a bug<br> - rewrite query to force to perform first geometry test then the other (example query 2)<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - create a "proxy" photo_proxy table that contains only photo_id and photo.geometry<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - force use of index via settings (usually a very bad idea)<br> - ...<br><br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Here is how your query could be simplified :<br><p class="MsoNormal">SELECT </p><p class="MsoNormal" style="text-indent:36pt">"id", </p><p class="MsoNormal" style="text-indent:36pt">"filename", </p><p class="MsoNormal" style="text-indent:36pt">ST_AsText("geometry") AS "geometry", </p><p class="MsoNormal" style="text-indent:36pt">ST_AsText("center") AS "center", </p><p class="MsoNormal" style="text-indent:36pt">"angle"</p><p class="MsoNormal">FROM "photo" </p><p class="MsoNormal">WHERE (<b>ST_DWithin</b>("geometry", st_GeomFromText( 'POINT(4.5063099203616 51.923602970634)', 4326)<b>,your_precision</b>)) </p><p class="MsoNormal" style="text-indent:36pt">AND "id" IN (</p><p class="MsoNormal" style="margin-left:18.72pt;text-indent:36pt">SELECT DISTINCT "photoId" <br></p><p class="MsoNormal" style="margin-left:18.72pt;text-indent:36pt">FROM "photoMetadata" </p><p class="MsoNormal" style="margin-left:54.72pt">WHERE ("value" = 'KADASTER') AND ("key" = 'source'))) </p><p class="MsoNormal"> AND ("photoId" IN (</p><p class="MsoNormal" style="margin-left:48.48pt;text-indent:36pt">SELECT DISTINCT "photoId" <br></p><p class="MsoNormal" style="margin-left:48.48pt;text-indent:36pt">FROM "photoMetadata" </p><div style="margin-left:120px">WHERE (key = 'year' AND ( cast(value as int ) >= 1866 AND cast ( value as int ) <= 1981))<br>))<br></div>ORDER BY "filename" LIMIT 36<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br><br></div><div class="gmail_default" style="font-family:monospace,monospace">here is the query to force use of geometric index :<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">WITH photos_spatialy_close AS (<br></div><div class="gmail_default" style="font-family:monospace,monospace"> SELECT id AS photoId</div><div class="gmail_default" style="font-family:monospace,monospace"> FROM photo<br></div><div class="gmail_default" style="font-family:monospace,monospace"> WHERE <b>ST_DWithin</b>("geometry", st_GeomFromText( 'POINT(4.5063099203616 51.923602970634)', 4326)<b>,your_precision</b>)<br></div><div class="gmail_default" style="font-family:monospace,monospace"> LIMIT 36<br></div><div class="gmail_default" style="font-family:monospace,monospace">)<br></div><div class="gmail_default" style="font-family:monospace,monospace">, photo_with_correct_metatadata AS (<br></div><div class="gmail_default" style="font-family:monospace,monospace"> <br> SELECT DISTINCT "photoId" <br> FROM "photoMetadata" <p class="MsoNormal"> WHERE ("value" = 'KADASTER') <br></p><p class="MsoNormal"> AND ("key" = 'source'))) </p><p class="MsoNormal"> AND ("photoId" IN (</p><p class="MsoNormal" style="text-indent:36pt">SELECT DISTINCT "photoId" <br></p><p class="MsoNormal" style="text-indent:36pt">FROM "photoMetadata" </p> WHERE (key = 'year' <br> AND ( cast(value as int ) >= 1866 <br> AND cast ( value as int ) <= 1981))</div><div class="gmail_default" style="font-family:monospace,monospace">)<br></div><div class="gmail_default" style="font-family:monospace,monospace">, keeping_photo_id_in_both_set AS (<br></div><div class="gmail_default" style="font-family:monospace,monospace"> SELECT photoId</div><div class="gmail_default" style="font-family:monospace,monospace"> FROM photos_spatialy_close<br></div><div class="gmail_default" style="font-family:monospace,monospace"> INTERSECTS<br></div><div class="gmail_default" style="font-family:monospace,monospace"> SELECT photoId</div><div class="gmail_default" style="font-family:monospace,monospace"> FROM photo_with_correct_metatadata</div><div class="gmail_default" style="font-family:monospace,monospace">)<br></div><div class="gmail_default" style="font-family:monospace,monospace">SELECT <br><p class="MsoNormal" style="text-indent:36pt">"id", </p><p class="MsoNormal" style="text-indent:36pt">"filename", </p><p class="MsoNormal" style="text-indent:36pt">ST_AsText("geometry") AS "geometry", </p><p class="MsoNormal" style="text-indent:36pt">ST_AsText("center") AS "center", </p><p class="MsoNormal" style="text-indent:36pt">"angle"</p><p class="MsoNormal">FROM keeping_photo_id_in_both_set LEFT OUTER JOIN photo ON ( photoId = id) <br></p><p class="MsoNormal">LIMIT ...</p><p class="MsoNormal"><br></p><p class="MsoNormal"><br></p><p class="MsoNormal"><br></p></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers<br>Remi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2016-12-16 17:48 GMT+01:00 Sandro Santilli <span dir="ltr"><<a href="mailto:strk@kbt.io" target="_blank">strk@kbt.io</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman wrote:<br>
> QUERY PLAN<br>
<br>
[...]<br>
<span class=""><br>
> What does this tell you?<br>
<br>
</span>That your query is too complex ?<br>
Check out <a href="http://explain.depesz.com" rel="noreferrer" target="_blank">http://explain.depesz.com</a><br>
<div class="HOEnZb"><div class="h5"><br>
--strk;<br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>