Re: COUNT(*) and index-only scans

>> Jeff Janes wrote:
>> Kevin Grittner wrote:
>
>>> 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.
>
> Clever way to get a full-table test.
>
> It turns out that for the above, with your trick to use the index
> only scan, it comes out 12% faster to do a seqscan, even when the
> table and index are fully cached (based on the average time of ten
> runs each way). There's very little overlap, so the difference looks
> real. But that's on a very narrow record, having just the one column
> used in the index. I added one wide column like this:
>
> alter table t add column x text;
> update t set x = (repeat(random()::text, (random() * 100)::int));
> cluster t USING t_pkey;
> vacuum freeze analyze;
>
> With that change the index-only scan time remained unchanged, while
> the seqscan time grew to about 2.6 times the index only scan time.
> That was mildly surprising for me, considering it was all still
> cached.

Moving data around in memory isn't that cheap, and you have probably made
the row size 5 times larger by the above thing. However if you added 4000
bytes instead of 100, you should be back to the previous results since
data then ends up (most likely,otherwise add a bit more) in a toast table.

Jesper