Re: GiST index performance

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

От: Scott Marlowe
Дата:
Сообщение: Re: SQL With Dates
От: "Mark Lewis"
Дата:
Сообщение: Re: SQL With Dates