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 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users