Re: COUNT(*) and index-only scans

On Mon, Oct 10, 2011 at 10:36 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> 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.

If you convert the where clause to "where id is not null" it uses the
index only scan again, but only if you nudge it too with
enable_seqscan=off.

I'm not sure why it needs the nudge in one case but not the other.

Cheers,

Jeff