PostgreSQLでデータベース・テーブル・インデックス容量とレコード数を一発で取得する方法

$ pgbench -i -s 100 -d test -U postgres

$ pgbench -i -s 100 -d test -U postgres
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 18.46 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 25.13 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 18.62 s, vacuum 2.76 s, primary keys 3.73 s).

$ psql -U postgres -d test
psql (13.18)
Type "help" for help.

test=# \dt
               List of relations
+--------+------------------+-------+----------+
| Schema |       Name       | Type  |  Owner   |
+--------+------------------+-------+----------+
| public | pgbench_accounts | table | postgres |
| public | pgbench_branches | table | postgres |
| public | pgbench_history  | table | postgres |
| public | pgbench_tellers  | table | postgres |
+--------+------------------+-------+----------+
(4 rows)

test=# \di
                           List of relations
+--------+-----------------------+-------+----------+------------------+
| Schema |         Name          | Type  |  Owner   |      Table       |
+--------+-----------------------+-------+----------+------------------+
| public | pgbench_accounts_pkey | index | postgres | pgbench_accounts |
| public | pgbench_branches_pkey | index | postgres | pgbench_branches |
| public | pgbench_tellers_pkey  | index | postgres | pgbench_tellers  |
+--------+-----------------------+-------+----------+------------------+
(3 rows)

test=# SELECT COUNT(*) FROM pgbench_accounts;
+----------+
|  count   |
+----------+
| 10000000 |
+----------+
(1 row)

test=# SELECT COUNT(*) FROM pgbench_branches;
+-------+
| count |
+-------+
|   100 |
+-------+
(1 row)

test=# SELECT COUNT(*) FROM pgbench_history;
+-------+
| count |
+-------+
|     0 |
+-------+
(1 row)

test=# SELECT COUNT(*) FROM pgbench_tellers;
+-------+
| count |
+-------+
|  1000 |
+-------+
(1 row)

SELECT
  oid
  ,table_schema
  ,table_name
  ,row_estimate
  ,pg_size_pretty(total_bytes) AS total
  ,pg_size_pretty(index_bytes) AS index
  ,pg_size_pretty(toast_bytes) AS toast
  ,pg_size_pretty(table_bytes) AS table
FROM (
  SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes
  FROM (
      SELECT
        c.oid
        ,nspname AS table_schema
        ,relname AS table_name
        ,c.reltuples::numeric AS row_estimate
        ,pg_total_relation_size(c.oid) AS total_bytes
        ,pg_indexes_size(c.oid) AS index_bytes
        ,pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r'
  ) a
) a;

+-------+--------------+------------------+--------------+---------+---------+-------+---------+
|  oid  | table_schema |    table_name    | row_estimate |  total  |  index  | toast |  table  |
+-------+--------------+------------------+--------------+---------+---------+-------+---------+
| 25032 | public       | pgbench_accounts |     10000000 | 1495 MB | 214 MB  |       | 1281 MB |
| 25035 | public       | pgbench_branches |          100 | 56 kB   | 16 kB   |       | 40 kB   |
| 25026 | public       | pgbench_history  |            0 | 0 bytes | 0 bytes |       | 0 bytes |
| 25029 | public       | pgbench_tellers  |         1000 | 120 kB  | 40 kB   |       | 80 kB   |
+-------+--------------+------------------+--------------+---------+---------+-------+---------+
(4 rows)

SELECT
  db.datname AS Name
  ,pg_catalog.pg_get_userbyid(db.datdba) AS Owner
  ,CASE WHEN pg_catalog.has_database_privilege(db.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(db.datname))
        ELSE 'No Access'
   END as Size
FROM pg_catalog.pg_database db
ORDER BY
  CASE WHEN pg_catalog.has_database_privilege(db.datname, 'CONNECT')
       THEN pg_catalog.pg_database_size(db.datname)
       ELSE NULL
  END DESC;

+-----------+-----------------+---------+
|   name    |      owner      |  size   |
+-----------+-----------------+---------+
| test      | radiuslog_owner | 1503 MB |
| postgres  | postgres        | 8141 kB |
| template0 | postgres        | 8117 kB |
| template1 | postgres        | 7181 kB |
+-----------+-----------------+---------+
(4 rows)

SELECT
  pn.nspname || '.' || pc.relname AS "relation",
  pg_size_pretty(pg_relation_size(pc.oid)) AS "size"
FROM pg_class pc
LEFT JOIN pg_namespace pn ON (pn.oid = pc.relnamespace)
WHERE pn.nspname NOT IN ('pg_catalog', 'information_schema')
AND pn.nspname = 'public'
ORDER BY
  pg_relation_size(pc.oid) DESC;

+------------------------------+------------+
|           relation           |    size    |
+------------------------------+------------+
| public.pgbench_accounts      | 1281 MB    |
| public.pgbench_accounts_pkey | 214 MB     |
| public.pgbench_tellers       | 48 kB      |
| public.pgbench_tellers_pkey  | 40 kB      |
| public.pgbench_branches_pkey | 16 kB      |
| public.pgbench_branches      | 8192 bytes |
| public.pgbench_history       | 0 bytes    |
+------------------------------+------------+
(7 rows)