> 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.
-Kevin
