Table of Contents
PostgreSQL thống kê
Create a function for row count
create function count_rows_of_table(
schema text,
tablename text
)
returns integer
security invoker
language plpgsql
as
$body$
declare
query_template constant text not null :=
'
select count(*) from "?schema"."?tablename"
';
query constant text not null :=
replace(
replace(
query_template, '?schema', schema),
'?tablename', tablename);
result int not null := -1;
begin
execute query into result;
return result;
end;
$body$;
example=# SELECT count_rows_of_table('northwind', 'orders');
count_rows_of_table
---------------------
830
(1 row)
Per-table row counts in a given database
select
table_schema,
table_name,
count_rows_of_table(table_schema, table_name)
from
information_schema.tables
where
table_schema not in ('pg_catalog', 'information_schema')
and table_type = 'BASE TABLE'
order by
1 asc,
3 desc;
table_schema | table_name | count_rows_of_table
--------------+------------------------+---------------------
northwind | order_details | 2155
northwind | orders | 830
northwind | customers | 91
northwind | products | 77
...
sportsdb | affiliations_events | 13052
sportsdb | stats | 9398
sportsdb | participants_events | 8700
sportsdb | events_documents | 7915
...
(121 rows)
Aggregate row counts per schema
SELECT table_schema, SUM(row_count) AS total_rows FROM (
SELECT table_schema,
count_rows_of_table(table_schema, table_name) AS row_count
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_type='BASE TABLE'
) AS per_table_count_subquery
GROUP BY table_schema
ORDER BY 2 DESC;
table_schema | total_rows
--------------+------------
sportsdb | 79138
northwind | 3362
(2 rows)
Aggregate row count across all tables
with per_table_counts as (
select count_rows_of_table(table_schema, table_name) as row_count
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_type='BASE TABLE'
) select sum(row_count) as total_rows
from per_table_counts;
total_rows
------------
82500
(1 row)