Postgres Professional
en

Re: GiST index performance

От: Tom Lane
Тема: Re: GiST index performance
Дата: ,
Msg-id: 20089.1239900406@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: GiST index performance  ("Kevin Grittner")
Ответы: Re: GiST index performance  (Matthew Wakeling)
Re: GiST index performance  (Matthew Wakeling)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

Предыдущее:
От: Matthew Wakeling
Дата:
Сообщение: Re: GiST index performance
Следующее:
От: Kris Jurka
Дата:
Сообщение: No hash join across partitioned tables?