PostgreSQL thống kê | PostgreSQL statistics

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)

https://www.yugabyte.com/blog/row-counts-of-tables-in-a-sql-schema-database-postgresql-yugabytedb/#:~:text=Aggregate%20row%20counts%20per%20schema,-Next%2C%20let%20us&text=This%20can%20be%20achieved%20by,table_name)%20AS%20row_count%20FROM%20information_schema.

Leave a Reply

Your email address will not be published. Required fields are marked *