Re: GiST index performance

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Matthew Wakeling <matthew@flymine.org> wrote:
>> I have been doing some queries that are best answered with GiST
>> indexes

> For what definition of "best answered"?

> Since an index is only a performance tuning feature (unless declared
> UNIQUE), and should never alter the results (beyond possibly affecting
> row order if that is unspecified), how is an index which performs
> worse than an alternative the best answer?

The main point of GIST is to be able to index queries that simply are
not indexable in btree.  So I assume that Matthew is really worried
about some queries that are not btree-indexable.  One would fully
expect btree to beat out GIST for btree-indexable cases.  I think the
significant point here is that it's winning by a factor of a couple
hundred; that's pretty awful, and might point to some implementation
problem.

Matthew, can you put together a self-contained test case with a similar
slowdown?  Also, what are the physical sizes of the two indexes?
I notice that the inner nestloop join gets slower too, when it's not
changed at all --- that suggests that the overall I/O load is a lot
worse, so maybe the reason the query is falling off a performance cliff
is that the GIST index fails to fit in cache.

            regards, tom lane