To: Daniel Kalchev <daniel@digsys.bg>
cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage 
In-reply-to: <200201100939.LAA28676@dcave.digsys.bg> 
References: <200201100939.LAA28676@dcave.digsys.bg>
Comments: In-reply-to Daniel Kalchev <daniel@digsys.bg>
	message dated "Thu, 10 Jan 2002 11:39:31 +0200"
Date: Thu, 10 Jan 2002 10:03:10 -0500
Message-ID: <20453.1010674990@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Daniel Kalchev <daniel@digsys.bg> writes:
> I agree, that with the 'wrong' clustering the index scan is not so
> much faster than the sequential scan.

It would be interesting to check whether there is any correlation
between ipaddr and ipdate in your test data.  Perhaps clustering
on ipaddr might not destroy the ordering on ipdate as much as you
thought.  A more clearly random-order test would go:

select * into iplog_test from iplog_gate200112 order by random();
create index iplog_test_ipdate_idx on iplog_test(ipdate);
vacuum verbose analyze iplog_test;
<< run queries >>

> Perhaps I need to tune this machine's costs to prefer more disk intensive 
> operations over CPU intensive operations?

Possibly.  I'm not sure there's much point in tuning the cost estimates
until the underlying model is more nearly right (ie, knows something
about correlation).  Do you care to try your dataset with 7.2 beta?

			regards, tom lane