I didn't think pgstattuple had proper visibility checks.
--------------------------------------------------------------------------- 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 > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org