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: 417E07F8.5030607@utwente.nl (view raw or flat)
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:

------8<---------------------------------------------------------------------------------------------

TestDB=# \d test_tbl;
 Table "public.test_tbl"
 Column |  Type   | Modifiers
--------+---------+-----------
 pre    | integer | not null
 name   | text    | not null
Indexes:
    "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;
 count 
--------
 166558
(1 row)

Time: 1024.803 ms
TestDB=#

------8<---------------------------------------------------------------------------------------------

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

Regards,


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
WWW:     http://www.cs.utwente.nl/~blokh


Responses

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-2014 The PostgreSQL Global Development Group