Re: Help optimizing a slow index scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Merlin Moncure wrote:

As others will probably mention, effective queries on lot/long which
is a spatial problem will require r-tree or gist.  I don't have a lot
of experience with exotic indexes but this may be the way to go.

One easy optimization to consider making is to make an index on either
(incidentid, entrydate) or (incident_id,long) which ever is more
selective.

This is 'yet another query' that would be fun to try out and tweak
using the 8.2 upcoming row-wise comparison.

merlin
Thanks to everyone for your suggestions. One problem I ran into is that apparently my version doesn't support the GIST index that was mentioned. "function 'box' doesn't exist" ).. So I'm guessing that both this as well as the Earth Distance contrib require me to add on some more pieces that aren't there.

Furthermore, by doing so, I am tying my queries directly to "postgres-isms". One of the long term goals of this project is to be able to fairly transparently support any ANSI SQL-compliant back end with the same code base. If I had full control over the query designs, I could make stored procedures to abstract this. However, I have to deal with a "gray box" third-party reporting library that isn't so flexible. I'll certainly consider going with something postgre-specific, but only as a last resort.

I tried the multi-column index as mentioned above but didn't see any noticeable improvement in elapsed time, although the planner did use the new index.

What is the real reason for the index not being very effective on these columns? Although the numbers are in a very limited range, it seems that the records would be very selective as it's not terribly common for multiple rows to share the same coords.

Is the "8.2. upcoming row-wise comparison" something that would be likely to help me?

Thanks again for your input


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux