Re: COUNT and Performance ...

I didn't think pgstattuple had proper visibility checks.

---------------------------------------------------------------------------

Hans-Jrgen Schnig 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(at)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(at)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
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073