Jeff Davis wrote:
> Currently, we use correlation to estimate the I/O costs of an index
> scan. However, this has some problems:
It certainly helps some cases.
Without the patch, the little test script below ends up picking the
third fastest plan (a seq-scan) instead of a faster bitmapscan, or
an even faster-than-that indexscan for the query below.
With the patch, it finds the fastest index scan.
Without Patch Estimated_cost Actual_Time
Index Scan 39638.36 331ms
Bitmap Scan 22218.43 415ms
Seq Scan 20125.83 595ms
With Patch Estimated_cost Actual_Time
Index Scan 17684.18 333ms
Bitmap Scan 22110.60 400ms
Seq Scan 20117.51 573ms
I was somewhat surprised that the bitmap cost estimates didn't
also change much. Wouldn't the estimated # of data blocks
read for the bitmap be roughly the same as for the index?
And yes, I know that table's a contrived one that is almost
ideal for this patch - but I have some large clustered-by-zip
address tables where I can find queries that show similar results.
Back in 8.0 I cared a lot since I had a number of real-world
queries picking Seq-Scans instead of Index-Scans. With 8.3,
though, AFAICT the vast majority of my similar real-world queries
pick the bitmap scans which in practice are pretty close in speed
to the index scans.
======================================================================
-- [1] Test script variation from this 2005 thread:
-- http://archives.postgresql.org/pgsql-hackers/2005-02/msg00298.php
create temporary table tmp1mil as select * from (select generate_series as a from
generate_series(0,9))as a, (select generate_series as b from generate_series(0,9)) as b,
(selectgenerate_series as c from generate_series(0,9)) as c, (select generate_series as d from
generate_series(0,9))as d, (select generate_series as e from generate_series(0,9)) as e,
(selectgenerate_series as f from generate_series(0,9)) as f order by a,b,c,d,e,f;
create index tmp1mil__c on tmp1mil(c);
vacuum analyze tmp1mil;
select * from pg_stats where tablename='tmp1mil';
\timing
explain select count(*) from tmp1mil where c<5;
select count(*) from tmp1mil where c<5;
select count(*) from tmp1mil where c<5; -- 615 ms seqscan
set enable_seqscan = false;
explain select count(*) from tmp1mil where c<5;
select count(*) from tmp1mil where c<5;
select count(*) from tmp1mil where c<5; -- 425 ms bitmapscan
set enable_bitmapscan to false;
explain select count(*) from tmp1mil where c<5;
select count(*) from tmp1mil where c<5;
select count(*) from tmp1mil where c<5; -- 342 ms indexscan