[postgis-users] speeding up KNN query

[David Rowley]

On Sat, Jul 20, 2013 at 8:46 AM, Ilia <ilia at lobsanov.com> wrote:

> I have the following query which I'd like to speed up from the current
> 200ms. I do need the distance in meters which is why I'm using
> ST_Distance_Sphere. Your help is very much appreciated.
>
>
> SELECT "places".* FROM (
>  SELECT
>  *,
>  ST_Distance_Sphere(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') as distance
>  FROM places
>  ORDER by geom <#> 'SRID=4326;POINT(-79.3937393 43.6526704)' LIMIT 50
>
>  ) AS places
>  WHERE "places"."place_category_id" = 10 ORDER BY distance ASC LIMIT 5
>
>
>
I'm a bit new to gist indexes and KNN searches, but I did read a bit last
week about them and I'm a bit confused looking at your query when you do
further filtering on the place_category_id in the outer query. It really
looks like you're making an attempt to get the true 5 nearest items since
you're doing a query on the approx 50 on the inner query, but then why
would you filter on the category. Is it not possible that it would return
no records after wasting effort on finding the 50 nearest? Should the
category filter not be on the inner query?
It's not much but your call to ST_Distance_Sphere could be on the outer
query, and then it would only be executed not more than 5 times, instead of
not more than 50 times. That's small potatoes though, let's see what
indexes are being used.

Please post the Explain analyze of the query so we can see what's going on
and what index are being used.

David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130721/6b9644bd/attachment.html>