pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRow counts/data changes. Any catalog table that has this info?
3+ messages / 3 participants
[nested] [flat]
* Row counts/data changes. Any catalog table that has this info?
@ 2004-02-20 02:35 David B <postgresql@thegatelys.com>
0 siblings, 2 replies; 3+ messages in thread
From: David B @ 2004-02-20 02:35 UTC (permalink / raw)
To: pgsql-sql
Folks,
Is there a catalog table or location where I can go to find data counts for
tables?
It would be nice if I could do a query which returned something like:
table_name #Rows
cust 1000
order 5000
order_detail 9500
without having to have the overhead of querying each table in turn.
If there is such a source then is it accurate as of now or last vacuum or
???
On a related note...any location where I can see the most recent date a
table was changed (insert/update/delete).
Tx in advance...
D
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Row counts/data changes. Any catalog table that has this info?
@ 2004-02-20 07:44 daq <daq@ugyvitelszolgaltato.hu>
parent: David B <postgresql@thegatelys.com>
1 sibling, 0 replies; 3+ messages in thread
From: daq @ 2004-02-20 07:44 UTC (permalink / raw)
To: David B <postgresql@thegatelys.com>; +Cc: pgsql-novice@postgresql.org
DB> Folks,
DB> Is there a catalog table or location where I can go to find data counts for
DB> tables?
DB> It would be nice if I could do a query which returned something like:
DB> table_name #Rows
DB> cust 1000
DB> order 5000
DB> order_detail 9500
DB> without having to have the overhead of querying each table in turn.
DB> If there is such a source then is it accurate as of now or last vacuum or
DB> ???
The pg_class table has a reltuples field but only the vacuum process updates it.
DAQ
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Row counts/data changes. Any catalog table that has this info?
@ 2004-02-20 18:53 Josh Berkus <josh@agliodbs.com>
parent: David B <postgresql@thegatelys.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2004-02-20 18:53 UTC (permalink / raw)
To: David B <postgresql@thegatelys.com>; pgsql-sql
David,
> table_name #Rows
> cust 1000
> order 5000
> order_detail 9500
If you're willing to live with some inaccuracy, do:
SELECT relname, reltuples FROM pg_class
WHERE relkind='r';
This count gets updated when you do a VACUUM, and is seldom 100% accurate;
however, if you VACUUM regularly it will be within 5-10%.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-20 18:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 02:35 Row counts/data changes. Any catalog table that has this info? David B <postgresql@thegatelys.com>
2004-02-20 07:44 ` daq <daq@ugyvitelszolgaltato.hu>
2004-02-20 18:53 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox