Re: GiST index performance

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Matthew Wakeling <matthew(at)flymine(dot)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