"Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes: > Matthew Wakeling <matthew@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
