CODEWITHSUNDEEP
postgresqlstatistics
StormByte
StormByte

Reputation: 1335

Get PostgreSQL statistics for all tables?

I have seen that PostgreSQL have several statistics depending on every table's OID (like the number of inserts, and so) 1. What I am looking for is some kind of SELECT query or something that would sum up everything for every table. It would be something like:

SELECT SUM(pg_stat_get_db_tuples_returned(SELECT oid FROM pg_class)); Or something like that. I would apreciate any help in here.

Upvotes: 0

Views: 1460

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324395

Something like this, with your choice of stats function, aggregates the stats function on all tables except temporary and system catalog tables:

SELECT
  sum(pg_stat_get_db_tuples_returned(c.oid))
FROM pg_catalog.pg_class c
  INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE NOT (n.nspname LIKE ANY(ARRAY['pg_temp%','pg_catalog','information_schema']));

Note that pg_toast schemas are included in this, as I presume you want your stats to include any TOAST side-tables. If you don't, add pg_toast% to the exclusions.

Edit: I was using the construct:

(quote_ident(n.nspname)||'.'||quote_ident(c.relname))::regclass

to get the table oid, but that's just silly when it's right there in pg_class; it's ridiculously roundabout as shown by a_horse_with_no_name.

Upvotes: 1

user330315
user330315

Reputation:

Do you mean this:

SELECT SUM(pg_stat_get_db_tuples_returned(oid))
from pg_class;

Upvotes: 1

Related Questions