Skip site navigation (1) Skip section navigation (2)

Re: GiST index performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Matthew Wakeling" <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: GiST index performance
Date: 2009-04-16 16:46:46
Message-ID: 20089.1239900406@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-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

In response to

Responses

pgsql-performance by date

Next:From: dforumDate: 2009-04-16 17:19:18
Subject: Re: GiST index performance
Previous:From: Matthew WakelingDate: 2009-04-16 16:37:58
Subject: Re: GiST index performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group