Re: GiST index performance

From: Tom Lane
Subject: Re: GiST index performance
Date: ,
Msg-id: 26222.1239904772@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: GiST index performance  (Matthew Wakeling)
Responses: 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, )

Matthew Wakeling <> writes:
> On Thu, 16 Apr 2009, Tom Lane wrote:
>> Also, what are the physical sizes of the two indexes?

>   location_object_start_gist | 193 MB
>   location_object_start      | 75 MB

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

Hmm, and what is shared_buffers set to?  How big are the tables and
other indexes used in the query?  We still have to explain why the
inner nestloop got slower, and it's hard to see that unless something
stopped fitting in cache.

            regards, tom lane


pgsql-performance by date:

From: Kris Jurka
Date:
Subject: Re: No hash join across partitioned tables?
From: Kris Jurka
Date:
Subject: Re: No hash join across partitioned tables?