Re: Help optimizing a slow index scan

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