Re: Moving postgresql.conf tunables into 2003...

От: Tom Lane
Тема: Re: Moving postgresql.conf tunables into 2003...
Дата: ,
Msg-id: 9730.1060299112@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden)
Ответы: Index correlation (was: Moving postgresql.conf tunables into 2003... )  (Manfred Koizar)
Список: pgsql-performance

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

Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
 Re: Moving postgresql.conf tunables into 2003...  (Rod Taylor, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
 Re: Moving postgresql.conf tunables into 2003...  ("scott.marlowe", )
  Re: Moving postgresql.conf tunables into 2003...  (Brian Hirt, )
   Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
    Re: Moving postgresql.conf tunables into 2003...  (Matthew Hixson, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
    Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
 Re: Moving postgresql.conf tunables into 2003...  ("Michael Mattox", )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Martin Foster, )
   Re: Moving postgresql.conf tunables into 2003...  (Bruce Momjian, )
 Re: Moving postgresql.conf tunables into 2003...  (Ron, )
 Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Rod Taylor, )
   Re: Moving postgresql.conf tunables into 2003...  (Manfred Koizar, )
    Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
     Re: Moving postgresql.conf tunables into 2003...  (Manfred Koizar, )
      Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
       Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
        Index correlation (was: Moving postgresql.conf tunables into 2003... )  (Manfred Koizar, )
     Use of multipart index with "IN"  (Rob Messer, )
      Re: Use of multipart index with "IN"  (Tom Lane, )
 Re: Moving postgresql.conf tunables into 2003...  (Josh Berkus, )
  Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Josh Berkus, )
    Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
     Re: Moving postgresql.conf tunables into 2003...  (Josh Berkus, )
   Re: Moving postgresql.conf tunables into 2003...  ("Matthew Nuzum", )
    Re: Moving postgresql.conf tunables into 2003...  (Michael Pohl, )
     Re: Moving postgresql.conf tunables into 2003...  (Martin Foster, )
     Re: Moving postgresql.conf tunables into 2003...  (Bruce Momjian, )
    Re: Moving postgresql.conf tunables into 2003...  (Chris Travers, )
     Re: Moving postgresql.conf tunables into 2003...  ("Jim C. Nasby", )
   Re: Moving postgresql.conf tunables into 2003...  (Andrew Sullivan, )
    Re: Moving postgresql.conf tunables into 2003...  ("Matthew Nuzum", )
     Re: Moving postgresql.conf tunables into 2003...  ("Michael Mattox", )
     Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
      Re: Moving postgresql.conf tunables into 2003...  (Kaarel, )
       Re: Moving postgresql.conf tunables into 2003...  ("scott.marlowe", )
        Re: Moving postgresql.conf tunables into 2003...  (Martin Foster, )
  Re: Moving postgresql.conf tunables into 2003...  ("Matthew Nuzum", )

Sean Chittenden <> writes:
>> If you CLUSTER on an index and then ANALYSE, you get a correlation of
>> 1.0 (== optimum) for the first column of the index.

> Correlating of what to what?  Of data to nearby data?  Of data to
> related data (ie, multi-column index?)? Of related data to pages on
> disk?  Not 100% sure in what context you're using the word
> correlation...

The correlation is between index order and heap order --- that is, are
the tuples in the table physically in the same order as the index?
The better the correlation, the fewer heap-page reads it will take to do
an index scan.

Note it is possible to measure correlation without regard to whether
there actually is any index; ANALYZE is simply looking to see whether
the values appear in increasing order according to the datatype's
default sort operator.

One problem we have is extrapolating from the single-column correlation
stats computed by ANALYZE to appropriate info for multi-column indexes.
It might be that the only reasonable fix for this is for ANALYZE to
compute multi-column stats too when multi-column indexes are present.
People are used to the assumption that you don't need to re-ANALYZE
after creating a new index, but maybe we'll have to give that up.

> But that value will degrade after time and at what rate?  Does ANALYZE
> maintain that value so that it's kept acurrate?

You keep it up to date by ANALYZE-ing at suitable intervals.  It's no
different from any other statistic.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Moving postgresql.conf tunables into 2003...
От: Yaroslav Mazurak
Дата:
Сообщение: Re: PostgreSQL performance problem -> tuning