> 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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
