[postgis-users] speeding up KNN query

[Ilia]

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?

explain analyze WITH knn_query AS
(
 SELECT
 *
 FROM places
 WHERE "places"."place_category_id" = 10
 ORDER by geom <#> 'SRID=4326;POINT(-79.3937393 43.6526704)' LIMIT 50
 )
SELECT *,
ST_Distance_Sphere(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') as
distance
FROM (
SELECT * FROM knn_query
ORDER BY ST_Distance(geom, 'SRID=4326;POINT(-79.3937393 43.6526704)') ASC
LIMIT 5
) places;
                                                                     QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on places  (cost=107.29..108.62 rows=5 width=1672) (actual
time=2.893..3.564 rows=5 loops=1)
   CTE knn_query
     ->  Limit  (cost=0.00..92.96 rows=50 width=2062) (actual
time=0.141..1.434 rows=50 loops=1)
           ->  Index Scan using index_places_on_geom on places
 (cost=0.00..2102.78 rows=1131 width=2062) (actual time=0.140..1.424
rows=50 loops=1)
                 Order By: (geom <#>
'0101000020E61000009829520633D953C09B8823B48AD34540'::geometry)
                 Filter: (place_category_id = 10)
                 Rows Removed by Filter: 136
   ->  Limit  (cost=14.33..14.34 rows=5 width=1672) (actual
time=2.275..2.277 rows=5 loops=1)
         ->  Sort  (cost=14.33..14.46 rows=50 width=1672) (actual
time=2.274..2.276 rows=5 loops=1)
               Sort Key: (st_distance(knn_query.geom,
'0101000020E61000009829520633D953C09B8823B48AD34540'::geometry))
               Sort Method: top-N heapsort  Memory: 27kB
               ->  CTE Scan on knn_query  (cost=0.00..13.50 rows=50
width=1672) (actual time=0.496..2.201 rows=50 loops=1)
 Total runtime: 3.642 ms
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130722/00a14f03/attachment.html>