Re: GiST index performance

От: Matthew Wakeling
Тема: Re: GiST index performance
Дата: ,
Msg-id: alpine.DEB.2.00.0904161843330.22330@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: GiST index performance  (Tom Lane)
Ответы: Re: GiST index performance  (Tom Lane)
Список: 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, )

On Thu, 16 Apr 2009, Tom Lane wrote:
> Matthew, can you put together a self-contained test case with a similar
> slowdown?

It isn't the smoking gun I thought it would be, but:

CREATE TABLE a AS SELECT a FROM generate_series(1,1000000) AS a(a);
CREATE TABLE b AS SELECT b FROM generate_series(1,1000000) AS b(b);

ANALYSE;

CREATE INDEX a_a ON a (a);

EXPLAIN ANALYSE SELECT * FROM a, b WHERE a.a BETWEEN b.b AND b.b + 2;

DROP INDEX a_a;
CREATE INDEX a_a ON a USING gist (a);

EXPLAIN ANALYSE SELECT * FROM a, b WHERE a.a BETWEEN b.b AND b.b + 2;


I see four seconds versus thirty seconds. The difference was much greater
on my non-test-case - I wonder if multi-column indexing has something to
do with it.

> Also, what are the physical sizes of the two indexes?

           relname           | pg_size_pretty
----------------------------+----------------
  location_object_start_gist | 193 MB
  location_object_start      | 75 MB
(2 rows)

> 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.

Memory in the machine is 16GB.

Matthew

--
 [About NP-completeness] These are the problems that make efficient use of
 the Fairy Godmother.                    -- Computer Science Lecturer


В списке pgsql-performance по дате сообщения:

От: Kris Jurka
Дата:
Сообщение: Re: No hash join across partitioned tables?
От: Kris Jurka
Дата:
Сообщение: Re: No hash join across partitioned tables?