Re: Much Ado About COUNT(*)

> Date: Wed, 12 Jan 2005 18:45:09 -0800
> From: Jeff Davis <jdavis-pgsql@empires.org>
> To: Alvaro Herrera <alvherre@dcc.uchile.cl>
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: Much Ado About COUNT(*)
> Message-ID: <1105584309.2886.410.camel@jeff>

(cut)

> Thanks for the link. It looks like it breaks it up into chunks of about
2KB. I think the
> conversation was mostly assuming the tables were somewhat closer to the
size of an
> index. If you have more than 2KB per tuple, pretty much anything you do
with an index
> would be faster I would think.


Hi Jeff/Alvaro,

I'm considering an application at the moment whereby I would need to do lots
of COUNT(*) on lots of separate tables without a WHERE clause. Would
something like the following help speed up the COUNT(*) by reducing the
tuple size being used for the count?


CREATE SEQUENCE id_seq;

CREATE TABLE person_count (id int8);

CREATE TABLE person (id int8 DEFAULT nextval('id_seq');first_name text,surname text,age int,address1 text,address2
text,address3text,address4 text,postcode texttel text); 

For each insert:
BEGIN;INSERT INTO person (first_name, .... Tel) VALUES ('Fred', ....
'12345');INSERT INTO person_count(id) VALUES (currval('id_seq'));COMMIT;


So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to
know the current number of person records. How much quicker would a COUNT(*)
be if visibility were included in the indices as opposed to a "hacked"
approach like this?


Many thanks,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk