Re: 9.2 and index only scans

Tom Lane wrote on 26.08.2012 16:31:
> Thomas Kellerer <spam_eater@gmx.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