Re: GiST index performance

От: Matthew Wakeling
Тема: Re: GiST index performance
Дата: ,
Msg-id: alpine.DEB.2.00.1003221322080.9798@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: GiST index performance  (Yeb Havinga)
Ответы: Re: GiST index performance  (Yeb Havinga)
Список: pgsql-performance

Скрыть дерево обсуждения

GiST index performance  (Matthew Wakeling, )
 Re: GiST index performance  (Robert Haas, )
  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  (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  (Adam Gundy, )
        Re: GiST index performance  (Heikki Linnakangas, )
      Re: GiST index performance  (Greg Smith, )
       Re: GiST index performance  (Robert Haas, )
 Re: GiST index performance  (Bruce Momjian, )
  Re: GiST index performance  (Robert Haas, )
  Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Robert Haas, )
   Re: GiST index performance  (Yeb Havinga, )
    Re: GiST index performance  (Yeb Havinga, )
     Re: GiST index performance  (Yeb Havinga, )
      Re: GiST index performance  (Yeb Havinga, )
       Re: GiST index performance  (Yeb Havinga, )
        Re: GiST index performance  (Kenneth Marshall, )
         Re: GiST index performance  (Yeb Havinga, )
          Re: GiST index performance  (Matthew Wakeling, )
           Re: GiST index performance  (Yeb Havinga, )
            Re: GiST index performance  (Matthew Wakeling, )

On Sat, 20 Mar 2010, Yeb Havinga wrote:
> The gist virtual pages would then match more the original blocksizes that
> were used in Guttman's R-tree paper (first google result, then figure 4.5).
> Since the nature/characteristics of the underlying datatypes and keys is not
> changed, it might be that with the disk pages getting larger, gist indexing
> has therefore become unexpectedly inefficient.

Yes, that is certainly a factor. For example, the page size for bioseg
which we use here is 130 entries, which is very excessive, and doesn't
allow very deep trees. On the other hand, it means that a single disc seek
performs quite a lot of work.

> But I am also not really into the core-gist code, but do have a motivation
> to dive into it (more than 200% performance increase in Mathew's test case).
> However I'd like to verify for community support before working on it.

I'd also love to dive into the core gist code, but am rather daunted by
it. I believe that there is something there that is taking more time than
I can account for. The indexing algorithm itself is good.

Matthew

--
 "The problem with defending the purity of the English language is that
 English is about as pure as a cribhouse whore. We don't just borrow words;
 on occasion, English has pursued other languages down alleyways to beat
 them unconscious and rifle their pockets for new vocabulary." - James Nicoll


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

От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
От: "Pierre C"
Дата:
Сообщение: Re: Block at a time ...