Re: GiST index performance

From: Tom Lane
Subject: Re: GiST index performance
Date: ,
Msg-id: 20089.1239900406@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: GiST index performance  ("Kevin Grittner")
Responses: Re: GiST index performance  (Matthew Wakeling)
Re: GiST index performance  (Matthew Wakeling)
List: pgsql-performance

Tree view

GiST index performance  (Matthew Wakeling, )
 Re: GiST index performance  ("Kevin Grittner", )
  Re: GiST index performance  (Matthew Wakeling, )
  Re: GiST index performance  (Tom Lane, )
   Re: GiST index performance  (Matthew Wakeling, )
    Re: GiST index performance  (Tom Lane, )
     Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Matthew Wakeling, )
    Re: GiST index performance  (Matthew Wakeling, )
     Re: GiST index performance  (Tom Lane, )
 Re: GiST index performance  (Matthew Wakeling, )
 Re: GiST index performance  (dforum, )
  Re: GiST index performance  (Tom Lane, )
  Re: GiST index performance  (Craig Ringer, )
 Re: GiST index performance  (Matthew Wakeling, )
  Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Matthew Wakeling, )
    Re: GiST index performance  (Tom Lane, )
     Re: GiST index performance  (Oleg Bartunov, )
 Re: GiST index performance  (Matthew Wakeling, )
  Re: GiST index performance  (Bruce Momjian, )
   Re: GiST index performance  (Robert Haas, )
    Re: GiST index performance  (Bruce Momjian, )

"Kevin Grittner" <> writes:
> Matthew Wakeling <> 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


pgsql-performance by date:

From: Matthew Wakeling
Date:
Subject: Re: GiST index performance
From: Kris Jurka
Date:
Subject: No hash join across partitioned tables?