[DOCS] Approximate count(*)
Folks, Please find enclosed a patch that shows how to get a quick approximation of count(*) on a table. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.241 diff -c -r1.241 func.sgml *** doc/src/sgml/func.sgml 14 Mar 2005 18:31:19 - 1.241 --- doc/src/sgml/func.sgml 24 Mar 2005 16:25:55 - *** *** 7330,7339 ! Unfortunately, there is no similarly trivial query that can be ! used to improve the performance of count() ! when applied to the entire table. --- 7330,7348 ! When the table has been VACUUMed recently, but ! only then, a good approximation of count(*) for an entire table ! can be obtained as follows: ! ! SELECT reltuples FROM pg_class WHERE relname = 'sometable'; ! ! ! ! ! Unfortunately, there is not yet a general trivial query that can ! be used to improve the performance of count(). + ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [DOCS] Approximate count(*)
David Fetter <[EMAIL PROTECTED]> writes: > Please find enclosed a patch that shows how to get a quick > approximation of count(*) on a table. I'm not sure we should be encouraging people to look at reltuples... for one thing, it's deliberately a moving average under 8.0. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [DOCS] Approximate count(*)
On Thu, Mar 24, 2005 at 12:34:51PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Please find enclosed a patch that shows how to get a quick > > approximation of count(*) on a table. > > I'm not sure we should be encouraging people to look at reltuples... > for one thing, it's deliberately a moving average under 8.0. Should there be more caveats? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [DOCS] Approximate count(*)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 David Fetter wrote: > Please find enclosed a patch that shows how to get a quick > approximation of count(*) on a table. You should mention that ANALYZE will also populate reltuples. To be real anal, you should say pg_catalog.pg_class too. :) Tom Lane asked: >> I'm not sure we should be encouraging people to look at reltuples... >> for one thing, it's deliberately a moving average under 8.0. > Should there be more caveats? Well, it already says "approximate", the value-laden word is "good". Perhaps if that went away... - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503242126 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCQ3iivJuQZxSWSsgRAm+PAKCNJPUl7Xns0kLKvbDjOiuNN2g4agCfbmg+ hX8RHO4R2Ad2fQyCPl+Ha3I= =2Gal -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] Approximate count(*)
David, If Jim and I finish our work for 8.1, then you'll be able to do: SELECT approx_records FROM pg_sysviews.pg_tables WHERE schema_name = 'schema' AND table_name = 'table' But the same caveats will apply. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
