Now here are some issues in the other answers that this function avoids: This produces a 3-columns output with the schema, the table and the rows count. Select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n) To work with a specific list of schemas or a list coming from a query without modifying the function, it can be called from within a query like this: WITH rc(schema_name,tbl) AS ( It takes a schema name as parameter, or public if no parameter is given. RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I', WHERE c.relkind = 'r' AND s.nspname=schema_name JOIN pg_namespace s ON (c.relnamespace=s.oid) RETURNS table(table_name text, cnt bigint) asįor table_name in SELECT c.relname FROM pg_class c Here's a version that's hopefully better: CREATE FUNCTION rowcount_all(schema_name text default 'public') To get exact counts, the other answers so far are plagued with some issues, some of them serious (see below). To get estimates, see Greg Smith's answer. For basic counting purposes just to see how big things are in general, either should be accurate enough. Normally I make that decision based on whether there's more useful information I also want to use inside of pg_class or inside of pg_stat_user_tables. Which of these queries is better to use is hard to say. Nspname NOT IN ('pg_catalog', 'information_schema') AND LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) The third way is to note that the system ANALYZE command, which is executed by the autovacuum process regularly as of PostgreSQL 8.3 to update table statistics, also computes a row estimate. That can also show you how many rows are dead, which is itself an interesting number to monitor. This value can be off by a bit under heavy activity, but is generally a good estimate: SELECT schemaname,relname,n_live_tup The second approach notes that the statistics collector tracks roughly how many rows are "live" (not deleted or obsoleted by later updates) at any time. (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))::text::int AS rows_n You could automate this to run against every table in the database, but you probably don't need that level of accuracy or want to wait that long. You're getting a count of what that transaction sees at the point in time when it executes. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can only be relative to some transaction. If you want a true count, you have to execute the SELECT statement like the one you used against each table. There's three ways to get this sort of count, each with their own tradeoffs.
0 Comments
Leave a Reply. |