<div dir="ltr">I had change the query since yesterday. See below. <div>It is now taking 115ms, which is quite slow.</div><div>I'm now ordering by ST_Distance_Sphere instead of ST_Distance. </div><div><br></div><div><div>
WITH knn_query AS</div><div> (</div><div> SELECT</div><div> *</div><div> FROM places</div><div> WHERE "places"."place_category_id" = 10</div><div> ORDER by geom <#> 'SRID=4326;POINT(-79.3937393 43.6526704)' LIMIT 50</div>
<div> )</div><div> SELECT *,</div><div> ST_Distance_Sphere(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') as distance</div><div> FROM knn_query</div><div> ORDER BY distance ASC<br></div><div> LIMIT 5</div><div>
<br>
</div><div>Here's the explain analyze:</div><div><br></div><div><div><div> Limit (cost=169.13..169.14 rows=5 width=1704) (actual time=115.621..115.622 rows=5 loops=1)</div><div> CTE knn_query</div><div> -> Limit (cost=0.00..154.68 rows=50 width=3406) (actual time=0.179..1.006 rows=50 loops=1)</div>
<div> -> Index Scan using index_places_on_geom on places (cost=0.00..3498.78 rows=1131 width=3406) (actual time=0.178..0.995 rows=50 loops=1)</div><div> Order By: (geom <#> '0101000020E61000009829520633D953C09B8823B48AD34540'::geometry)</div>
<div> Filter: (place_category_id = 10)</div><div> Rows Removed by Filter: 136</div><div> -> Sort (cost=14.46..14.58 rows=50 width=1704) (actual time=115.620..115.620 rows=5 loops=1)</div>
<div> Sort Key: (_st_distance(geography(knn_query.geom), '0101000020E61000009829520633D953C09B8823B48AD34540'::geography, 0::double precision, false))</div><div> Sort Method: top-N heapsort Memory: 29kB</div>
<div> -> CTE Scan on knn_query (cost=0.00..13.62 rows=50 width=1704) (actual time=67.064..115.540 rows=50 loops=1)</div><div> Total runtime: 115.698 ms</div></div></div><div class="gmail_extra"><br>
<br><br><div class="gmail_quote">On Mon, Jul 22, 2013 at 11:01 AM, David Rowley <span dir="ltr"><<a href="mailto:dgrowleyml@gmail.com" target="_blank">dgrowleyml@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div class="im"><div class="gmail_quote">On Tue, Jul 23, 2013 at 2:17 AM, Ilia <span dir="ltr"><<a href="mailto:ilia@lobsanov.com" target="_blank">ilia@lobsanov.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div dir="ltr">Thanks David for your feedback. I got it down to 4ms or so. Below is the explain analyze. It's 2 subqueries now. Any ideas for simplification of the query?<br><div dir="ltr"><div><br></div><div><div>explain analyze WITH knn_query AS</div>
<div>(</div><div> SELECT</div><div> *</div><div> FROM places</div><div><div> WHERE "places"."place_category_id" = 10</div></div><div><div> ORDER by geom <#> 'SRID=4326;POINT(-79.3937393 43.6526704)' LIMIT 50</div>
<div> )</div></div><div><div>SELECT *,</div><div>ST_Distance_Sphere(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') as distance</div></div><div>FROM (</div><div>SELECT * FROM knn_query</div><div>ORDER BY ST_Distance(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') ASC</div>
<div>LIMIT 5</div><div>) places;</div><div></div></div></div></div></blockquote><div><br></div></div></div>I thought maybe:<br><br>SELECT *,ST_Distance_Sphere(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') as distance<br>
FROM (SELECT * FROM places<div class="im"><br> WHERE "places"."place_category_id" = 10<br> ORDER by geom <#> 'SRID=4326;POINT(-79.3937393 43.6526704)'<br></div> LIMIT 50) AS places<div class="im">
<br>ORDER BY ST_Distance(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') ASC<br></div>
LIMIT 5;<br><br><br>I don't think you really need the extra nest level in the sub query for the one you wrote, but I assume you added it because you tried to do ORDER BY distance? and got an error?<br>The good news is that ST_Distance is an immutable function, so PostgreSQL likely won't execute it twice for the same inputs on the same query.<br>
I also noticed you are using both ST_Distance_Sphere and ST_Distance(), is this meant? I'm quite new to postgis, but I think the only difference is ST_Distance_Sphere is faster is it's not working with a complex spheroid shape. So if you're doing this for performance then it's not a good idea as it means 2 functions need executed instead of 1 (at least with my query).<br>
<br>SELECT *,ST_Distance(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') as distance<br>FROM (SELECT * FROM places<div class="im"><br> WHERE "places"."place_category_id" = 10<br> ORDER by geom <#> 'SRID=4326;POINT(-79.3937393 43.6526704)'<br>
</div>
LIMIT 50) AS places<div class="im"><br>ORDER BY ST_Distance(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') ASC<br></div>LIMIT 5;<br><br>Will likely be slightly faster, though I highly doubt you'll notice much difference on 5 rows. But obviously it will give slightly different results.<br>
<br>I take it you're happy enough with the 4ms from 200ms?<br><br>Regards<span class=""><font color="#888888"><br><br>David<br>
</font></span><br>_______________________________________________<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/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div>