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