From: Andrew Sagulin <andrews42@yandex.ru>
To: pgsql-performance@postgresql.org
Subject: Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)
Date: Tue, 27 Jun 2006 16:14:08 +0400
Message-ID: <463024718.20060627161408@yandex.ru> (raw)
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) ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrews42@yandex.ru
Subject: Re: Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)
In-Reply-To: <463024718.20060627161408@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox