Re: GiST index performance

From: Matthew Wakeling
Subject: Re: GiST index performance
Date: ,
Msg-id: alpine.DEB.2.00.0904201503570.22330@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: GiST index performance  (Matthew Wakeling)
Responses: Re: GiST index performance  (Tom Lane)
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, )

On Fri, 17 Apr 2009, Matthew Wakeling wrote:
> I have done a bit of investigation, and I think I might have found the
> smoking gun I was looking for.

I have found a bug in the contrib package seg, which has been copied into
the bioseg data type as well. It causes the index to be created with
horribly bad unselective trees, so that when a search is performed many of
the branches of the tree need to be followed. This explanation does not
extend to btree_gist, so I will have to further investigate that. Apply
the following patch to contrib/seg/seg.c:

*** seg.c    2006-09-10 18:36:51.000000000 +0100
--- seg.c_new    2009-04-20 15:02:52.000000000 +0100
***************
*** 426,432 ****
           else
           {
               datum_r = union_dr;
!             size_r = size_alpha;
               *right++ = i;
               v->spl_nright++;
           }
--- 426,432 ----
           else
           {
               datum_r = union_dr;
!             size_r = size_beta;
               *right++ = i;
               v->spl_nright++;
           }


Matthew

--
 The early bird gets the worm. If you want something else for breakfast, get
 up later.


pgsql-performance by date:

From: Scott Marlowe
Date:
Subject: Re: SQL With Dates
From: "Mark Lewis"
Date:
Subject: Re: SQL With Dates