Search Postgresql Archives

Re: 9.2 and index only scans

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom Lane wrote on 26.08.2012 16:31:
Thomas Kellerer <spam_eater@xxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux