On 3/17/06, Dan Harris <fbsd@drivefaster.net> wrote:
> Merlin Moncure wrote:
> 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.
earth distance is a contrib module that has to be built and installed.
it does use some pg-isms so I guess that can be ruled out. GIST is a
bit more complex and I would consider reading the documentation very
carefully regarding them and make your own determination.
> Furthermore, by doing so, I am tying my queries directly to
> "postgres-isms". [snip]
> 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.
did you try both flavors of the multiple key index I suggested? (there
were other possiblities, please experiment)
> Is the "8.2. upcoming row-wise comparison" something that would be
> likely to help me?
possibly. good news is that rwc is ansi sql. you can see my blog
about it here: http://people.planetpostgresql.org/merlin/
Specifically, if you can order your table with an order by statement
such that the records you want are contingous, then yes. However,
even though it's ansi sql, various commercial databases implement rwc
improperly or not at all (mysql, to their credit, gets it right) and I
still feel like an exotic index or some other nifty pg trick might be
the best performance approach here).
Merlin