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

Re: GiST index performance

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-performance(at)postgresql(dot)org
Subject: Re: GiST index performance
Date: 2009-04-17 17:18:45
Message-ID: alpine.DEB.2.00.0904171809040.22330@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 16 Apr 2009, Tom Lane wrote:
> Matthew, can you put together a self-contained test case with a similar
> slowdown?

I have done a bit of investigation, and I think I might have found the 
smoking gun I was looking for. I just added a load of debug to the gist 
consistent function on the bioseg type, and did a single overlap lookup in 
the index.

The index contains values ranging from 1 to 28,000,000 or so.
The range I looked up was 23503297..23504738 (so a very small proportion).
The index contains 375154 entries.
The index returned 59 rows.
The consistent method was called 54022 times - 5828 times for branch
     (internal) index entries, and 48194 times for leaf entries.

Obviously this is a really bad index layout - scanning that many entries 
for such a small output. In fact, I saw lots of overlapping branch index 
entries, so the index isn't actually differentiating between the different 
branches of the tree very well. This indicates a failure of the picksplit 
or the penalty functions. I shall investigate this further next week.

I shall also investigate whether this is the exact same problem that I had 
with the int4 gist system.

Matthew

-- 
So, given 'D' is undeclared too, with a default of zero, C++ is equal to D.
  mnw21, commenting on the "Surely the value of C++ is zero, but C is now 1"
  response to "No, C++ isn't equal to D. 'C' is undeclared [...] C++ should
  really be called 1" response to "C++ -- shouldn't it be called D?"

In response to

Responses

pgsql-performance by date

Next:From: Grzegorz JaƛkiewiczDate: 2009-04-17 23:12:49
Subject: stats are way off on 8.4 b1
Previous:From: Kris JurkaDate: 2009-04-17 17:05:32
Subject: Re: No hash join across partitioned tables?

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