Tom Lane wrote on 26.08.2012 16:31:
Thomas Kellerer <spam_ea...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
