Re: COUNT and Performance ...

For a more accurate view of the time used, use the \timing switch in psql.
That leaves out the overhead for forking and loading psql, connecting to
the database and such things.

I think, that it would be even nicer if postgresql automatically choose
to replace the count(*)-with-no-where with something similar.

Regards,

Arjen

Hans-Jürgen Schönig wrote:
> 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>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org