KNN GIST with a Lateral twist: Coming soon to a database near you

One of the things that really frustrated me about the KNN GIST distance box box centroid operators that came in PostgreSQL 9.1 and PostGIS 2.0 was the fact that one of the elements needed to be constant to take advantage of the index. In PostGIS speak, this meant you couldn't put it in the FROM clause and could only enjoy it in one of two ways.

The very constant

What are the closest 10 apartment parcels closest to a spot
This is in Massachusetts State Plane meters (srid 26986) - data is Cambridge, MA
WHERE l.land_type = 'apartment'
ORDER BY ST_GeomFromText('POINT(234054 902182)',26986) <-> l.geom LIMIT 10;

The exploited correlated query constant

This approach forces a query for each row and each subquery in row references a constant geometry which is the current row thus is constant enough

What is the closest aparmentment to each educational parcel?
SELECT AS reference_parcel 
  , (SELECT pid 
  FROM As s 
    ORDER BY l.geom <#> s.geom LIMIT 1) As closest_parcel   
WHERE l.land_type = 'education';

And if you needed to get more than one row back, like what is the N closest (N > 1), it was some really ugly code you had to resort to if you wanted to use KNN gist. So ugly you'd be better off just sticking with ST_DWithin and guess an expansion to check.

Coming in PostgreSQL 9.3: LATERAL correlated query

But in 9.3 we have LATERAL which allows us to put our subquery back in the FROM clause and still utilize a KNN GIST index and adorn with as many additional computed columns as we want. It looks like this:

SELECT, As closest_apartment, s.dist_m
(SELECT pid, ST_Distance(b.geom,l.geom) as dist_m 
  FROM As b WHERE b.land_type = 'apartment'
   ORDER BY b.geom <-> l.geom LIMIT 10  ) As s 
   WHERE l.land_type = 'education'
ORDER BY, dist_m;

The explain plan demonstrates the spatial index is being used

Sort  (cost=15074.71..15081.26 rows=2620 width=24) (actual time=129.435..129.495 rows=2620 loops=1)
  Output:,, (st_distance(b.geom, l.geom))
  Sort Key:, (st_distance(b.geom, l.geom))
  Sort Method: quicksort  Memory: 301kB
  ->  Nested Loop  (cost=0.56..14925.95 rows=2620 width=24) (actual time=0.411..119.107 rows=2620 loops=1)
        Output:,, (st_distance(b.geom, l.geom))
        ->  Index Scan using idx_land_land_type on l  (cost=0.29..178.63 rows=262 width=277) 
         (actual time=0.040..0.163 rows=262 loops=1)
              Output:, l.bldg_val, l.land_val, l.other_val, l.total_val, l.addr_num, l.full_str, l.land_type, l.units, l.geom
              Index Cond: ((l.land_type)::text = 'education'::text)
        ->  Limit  (cost=0.28..56.09 rows=10 width=277) (actual time=0.176..0.452 rows=10 loops=262)
              Output:, (st_distance(b.geom, l.geom)), ((b.geom <-> l.geom))
              ->  Index Scan using idx_land_geom_gist on b  (cost=0.28..5882.65 rows=1054 width=277) 
              (actual time=0.174..0.449 rows=10 loops=262)
                    Output:, st_distance(b.geom, l.geom), (b.geom <-> l.geom)
                    Order By: (b.geom <-> l.geom)
                    Filter: ((b.land_type)::text = 'apartment'::text)
                    Rows Removed by Filter: 224
Total runtime: 129.687 ms