> 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