[postgis-users] Optimizing nearest neighbor search with conditions

[Doug Cole]

I recently updated a postgresql database to use postgis 2.0.1 - I am
trying to improve the performance of our nearest neighbor queries
using the new <-> operator, but ran into an issue. Our nearest
neighbor query also has a handful of other conditions beyond just
distance. For the majority of queries using <-> speeds up the queries
by an order of magnitude, unfortunately in the cases where the other
conditions can't be met for the query, it performance a sequential
scan of the entire index, taking a query that normally takes 20-30ms
take 7 seconds.

 I've tried several things to work around this:
 * adding a bounding box similar to what we used with postgis 1.x - it
always uses the index with the bounding box, even when using the index
with the order by would be 10x faster
 * increasing statistics value for the geometry column in case that
was causing the poor choice of index usage, this had no effect
 * adding a distance constraint that can't be used for the index
(st_distance_sphere) - this reverts back to the full index scan, but
doesn't halt the full index scan in the worst case. It doesn't seem
able to recognize the fact that logically no further items in the
ordered index could match

Is this a known limitation for nearest neighbor queries with postgis
or is there a known workaround for this kind of situation? Thanks in
advance for your help,
Doug