public inbox for [email protected]
help / color / mirror / Atom feedApproximate count(*)
6+ messages / 5 participants
[nested] [flat]
* Approximate count(*)
@ 2005-03-24 17:01 David Fetter <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David Fetter @ 2005-03-24 17:01 UTC (permalink / raw)
To: pgsql-docs; PostgreSQL Patches <[email protected]>
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 -0000 1.241
--- doc/src/sgml/func.sgml 24 Mar 2005 16:25:55 -0000
***************
*** 7330,7339 ****
</para>
<para>
! Unfortunately, there is no similarly trivial query that can be
! used to improve the performance of <function>count()</function>
! when applied to the entire table.
</para>
</note>
</sect1>
--- 7330,7348 ----
</para>
<para>
! When the table has been <command>VACUUM</command>ed recently, but
! only then, a good approximation of count(*) for an entire table
! can be obtained as follows:
! <programlisting>
! SELECT reltuples FROM pg_class WHERE relname = 'sometable';
! </programlisting>
! </para>
!
! <para>
! Unfortunately, there is not yet a general trivial query that can
! be used to improve the performance of <function>count()</function>.
</para>
+
</note>
</sect1>
Attachments:
[text/plain] approx_count.diff (1.1K, 2-approx_count.diff)
download | inline diff:
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 -0000 1.241
--- doc/src/sgml/func.sgml 24 Mar 2005 16:25:55 -0000
***************
*** 7330,7339 ****
</para>
<para>
! Unfortunately, there is no similarly trivial query that can be
! used to improve the performance of <function>count()</function>
! when applied to the entire table.
</para>
</note>
</sect1>
--- 7330,7348 ----
</para>
<para>
! When the table has been <command>VACUUM</command>ed recently, but
! only then, a good approximation of count(*) for an entire table
! can be obtained as follows:
! <programlisting>
! SELECT reltuples FROM pg_class WHERE relname = 'sometable';
! </programlisting>
! </para>
!
! <para>
! Unfortunately, there is not yet a general trivial query that can
! be used to improve the performance of <function>count()</function>.
</para>
+
</note>
</sect1>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Approximate count(*)
@ 2005-03-24 17:34 Tom Lane <[email protected]>
parent: David Fetter <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-03-24 17:34 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs; PostgreSQL Patches <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCHES] Approximate count(*)
@ 2005-03-24 17:58 David Fetter <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David Fetter @ 2005-03-24 17:58 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-docs; PostgreSQL Patches <[email protected]>
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCHES] Approximate count(*)
@ 2005-03-25 02:34 Greg Sabino Mullane <[email protected]>
parent: David Fetter <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Sabino Mullane @ 2005-03-25 02:34 UTC (permalink / raw)
To: pgsql-docs
-----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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCHES] Approximate count(*)
@ 2005-03-25 03:19 Josh Berkus <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2005-03-25 03:19 UTC (permalink / raw)
To: pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCHES] Approximate count(*)
@ 2005-03-27 21:24 Jim C. Nasby <[email protected]>
parent: Josh Berkus <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Jim C. Nasby @ 2005-03-27 21:24 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: pgsql-docs
Josh, David-
Do you think it's worth adding a function that would do the select for
you? IE: tuplecount('schema', 'tablename')?
On Thu, Mar 24, 2005 at 07:19:00PM -0800, Josh Berkus wrote:
> 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
>
--
Jim C. Nasby, Database Consultant [email protected]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-03-27 21:24 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-24 17:01 Approximate count(*) David Fetter <[email protected]>
2005-03-24 17:34 ` Tom Lane <[email protected]>
2005-03-24 17:58 ` David Fetter <[email protected]>
2005-03-25 02:34 ` Greg Sabino Mullane <[email protected]>
2005-03-25 03:19 ` Josh Berkus <[email protected]>
2005-03-27 21:24 ` Jim C. Nasby <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox