Re: COUNT(*) and index-only scans

Bruce Momjian <bruce@momjian.us> wrote:
> I talked to Robert Haas and he said that index-only scans do not
> optimize COUNT(*).  Is this something we can do for PG 9.2?  Is
> anyone working on this?
Well, it's not that it doesn't optimize COUNT(*) -- it's that it
doesn't yet cost the index scan as cheaper than a table scan when
you're accessing every row.
create table t (id int not null primary key);
insert into t select generate_series(1, 1000000);
vacuum freeze analyze;
explain analyze select count(*) from t where id between 500000 and 500010;
That gives you an index-only scan; but without the WHERE clause it
uses a seq scan.  I think it's mainly a matter of doing enough
benchmarks to figure out how best to model the costs of the index
scan so that it can be picked for that case.
-Kevin