COUNT and Performance ...

This patch adds a note to the documentation describing why the
performance of min() and max() is slow when applied to the entire table,
and suggesting the simple workaround most experienced Pg users
eventually learn about (SELECT xyz ... ORDER BY xyz LIMIT 1).

Any suggestions on improving the wording of this section would be
welcome.

Cheers,


------

ORDER and LIMIT work pretty fast (no seq scan).
In special cases there can be another way to avoid seq scans:


action=# select tuple_count from pgstattuple('t_text');tuple_count
-------------      14203
(1 row)

action=# BEGIN;
BEGIN
action=# insert into t_text (suchid) VALUES ('100000');
INSERT 578606 1
action=# select tuple_count from pgstattuple('t_text');tuple_count
-------------      14204
(1 row)

action=# ROLLBACK;
ROLLBACK
action=# select tuple_count from pgstattuple('t_text');tuple_count
-------------      14203
(1 row)


If people want to count ALL rows of a table. The contrib stuff is pretty 
useful. It seems to be transaction safe.

The performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz):


root@actionscouts:~# time psql action -c "select tuple_count from 
pgstattuple('t_text');"tuple_count
-------------      14203
(1 row)


real    0m0.266s
user    0m0.030s
sys     0m0.020s
root@actionscouts:~# time psql action -c "select count(*) from t_text"count
-------14203
(1 row)


real    0m0.701s
user    0m0.040s
sys     0m0.010s


I think that this could be a good workaround for huge counts (maybe 
millions of records) with no where clause and no joins.
   Hans

<http://kernel.cybertec.at>