Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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) ?






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux