Re: count(*) bag ?

On Sat, 26 Oct 2002, Konstantin Tokar wrote:

> I use PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC
> 2.95.3 . count(*) very slowly.
>
> CREATE TABLE r2 (
> r2_id int4 NOT NULL,
> a int4 DEFAULT 1,
> label varchar(255),
> CONSTRAINT r2_pkey PRIMARY KEY (r2_id)
> ) WITH OIDS;
>
> insert into r2 select * from r; # The table with the same structure
>
> vacuum;
>
> select count(*) from r2;
>
> Aggregate (cost=100010594.30..100010594.30 rows=1 width=0)
> -> Seq Scan on r2 (cost=100000000.00..100009620.04 rows=389704 width=0)
>
> Why not used primary key index?

Because even if you used the index, you'd still have to read all
the pages in the table to get the validity information (can your
transaction see this row) and that'd be even more expensive.

There's occasionally been talk about keeping (optionally presumably)
a copy of the validity information in the indexes but there are issues
with that and afaik noone has stepped up to take them on.