[postgis-users] Optimizing nearest neighbor search with conditions

[Doug Cole]

On Mon, Oct 29, 2012 at 8:37 PM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> Unless you can draw a set from the ordering that is large enough to
> always contain a result that passes your filters, I think you're out
> of luck. The reality is that the ordering operators are quite limited,
> there's all sorts of useful things one would like to do with them
> (table join on nearest, for example) that one cannot.

Thanks for the info Paul. Is this written up anywhere? It seems like
something worth adding to the documentation. I'm probably the least
qualified person around to do it, but I'm happy to write it up if
there's more information somewhere that I could summarize.
Doug


> On Mon, Oct 29, 2012 at 8:11 PM, Doug Cole <dougcole at gmail.com> wrote:
>> 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 at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users