Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)

Поиск
Список
Период
Сортировка
От Andrew Sagulin
Тема Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)
Дата
Msg-id 463024718.20060627161408@yandex.ru
обсуждение исходный текст
Ответы Re: Large index scan perfomance and indexCorrelation (PG  (Simon Riggs <simon@2ndquadrant.com>)
Список pgsql-performance
Hello all

I have a big amount of phone calls data (280M records, 25 Gbytes).The best decision
for this task is partitioning and I use it now. But at first I tried put all
data in a single table indexed by call date&time. Because of nature of the
data the records clustered by date and near ordered by time.

The table definition:

CREATE DOMAIN datetime AS timestamp NOT NULL;
CREATE DOMAIN cause  AS int2  DEFAULT 16  NOT NULL;
CREATE DOMAIN conn_num  AS varchar(34);
CREATE DOMAIN dur AS int4  NOT NULL;
CREATE DOMAIN lno AS int2;
CREATE DOMAIN tgrp AS char(6);

CREATE TABLE conn
(
  datetime datetime,
  anum conn_num,
  bnum conn_num,
  dur dur,
  itgrp tgrp,
  ilno lno,
  otgrp tgrp,
  olno lno,
  cause cause
)
WITHOUT OIDS;

CREATE INDEX conn_dt
  ON conn
  USING btree
  (datetime);

Usual tasks on the table are export and search calls on one or more days. This
cause the scan of 400K or more records, selected by 'conn_dt' index. The best data
access path is a bitmap heap scan. Tests I've made showed incredible bitmap scan
perfomance almost equal to a seq scan. But PG always prefered simple index scan
which is 20 times slower. Digging in the PG internals brought me to
indexCorrelation. For the 'datetime' column it was about 0,999999. But why despite
of this the index scan was so slow? In the next step I ran

select ctid from conn where ... order by datetime;

Result showed up that there were no page seq scan at all - true random access
only.
The simple model which can explain the situation: the sequence of numbers 2, 1,
4, 3, 6, 5, ..., 100, 99 has correlation about 0,9994. Let's imagine it's the page
order of an index scan. H'm, bad choice, isn't it?

I think indexCorrelation can help to estimate page count but not page
fetch cost. Why not to use formula

min_IO_cost = ceil(indexSelectivity * T) * random_page_cost

instead of

min_IO_cost = ceil(indexSelectivity * T) ?





В списке pgsql-performance по дате отправления:

Предыдущее
От: Simon Riggs
Дата:
Сообщение: Re: Some performance numbers, with thoughts
Следующее
От: "Gourish Singbal"
Дата:
Сообщение: unregister