Skip site navigation (1) Skip section navigation (2)

Strange count(*) implementation?

From: Henk Ernst Blok <h(dot)e(dot)blok(at)utwente(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange count(*) implementation?
Date: 2004-10-26 08:16:56
Message-ID: (view raw or whole thread)
Lists: pgsql-general
Hi Posgres users/developers,

Can anyone explain why PosgreSQL (version 7.4.5 on Linux) does a full 
table scan to compute a count(*) on a base table after a vacuum analyze 
has been done with no following updates that might have outdated any 
statistics. Strangly the explain command does give the correct number of 
tuples instantaniously from the catalog, as one would expect. Still the 
optimizer thinks it needs a full table scan to do count.

See example below:


TestDB=# \d test_tbl;
 Table "public.test_tbl"
 Column |  Type   | Modifiers
 pre    | integer | not null
 name   | text    | not null
    "test_tbl_pkey" primary key, btree (pre)
    "test_tbl_pre_index" unique, btree (pre)
    "test_tbl_name_index" btree (name)

TestDB=# explain select count(*) from test_tbl;
                                 QUERY PLAN                                
 Aggregate  (cost=34293.60..34293.60 rows=1 width=0)
   ->  Seq Scan on test_tbl  (cost=0.00..34293.60 rows=166558 width=0)
(2 rows)

Time: 25.188 ms
TestDB=# select count(*) from test_tbl;
(1 row)

Time: 1024.803 ms


The consequence of this seemingly odd count implementation is a very 
very slow count.


Henk Ernst Blok

address: DB group, Computer Science, EEMCS Dept., University of Twente,
         PO Box 217, 7500 AE, ENSCHEDE, THE NETHERLANDS
phone:   ++31 (0)53 489 3754 (if no response: 3690)
email:   h(dot)e(dot)blok(at)utwente(dot)nl


pgsql-general by date

Next:From: HMDate: 2004-10-26 08:26:04
Subject: Kill a postgres session
Previous:From: Justin WyerDate: 2004-10-26 06:29:17
Subject: Re: list fieldnames in table? (from PHP)

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group