Re: 9.2 and index only scans

Tom Lane wrote on 26.08.2012 16:31:
> Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
>> I'm playing around with 9.2 beta4 and was looking into the new Index Only Scan feature.
>> I was a bit surprised that a "count(*)" query does not use an index.
>
> Works for me. However, the cost estimate for that is heavily dependent
> on how much of the table is known all-visible. If the table is getting
> a lot of churn, or even just hasn't been vacuumed since it quiesced,
> the planner will prefer a seqscan for this --- and it will be right.
>

Hmm. So it's something with my environment.

Should the following setup qualify for an index scan?

postgres=# select version();
version
----------------------------------------------------------------
PostgreSQL 9.2beta4, compiled by Visual C++ build 1600, 32-bit
(1 row)

postgres=#
postgres=# create table foo (id integer not null primary key, some_data text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
postgres=#
postgres=# insert into foo (id, some_data)
postgres-# select i, rpad('x',2500,'*')
postgres-# from generate_series(1,100000) i;
INSERT 0 100000
postgres=#
postgres=# vacuum analyze foo;
VACUUM
postgres=#
postgres=# explain (analyze on, buffers on, verbose on) select count(*) from foo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2185.00..2185.01 rows=1 width=0) (actual time=67.622..67.622 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=935
-> Seq Scan on public.foo (cost=0.00..1935.00 rows=100000 width=0) (actual time=0.020..37.531 rows=100000 loops=1)
Output: id, some_data
Buffers: shared hit=935
Total runtime: 67.670 ms
(7 rows)

Regards
Thomas