PGSQL-日常查询统计

--1.1查看数据库大小
SELECT 
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname = 'TY-355'; 


--2.1查看普通表、超表大小（包括gis表和超表）
SELECT
    n.nspname || '.' || c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS data_size,
    pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
FROM pg_class c
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

--3.1各表数据行
WITH hypertable_chunks AS (
    SELECT
        nt.nspname AS hypertable_schema,
        ht.relname AS hypertable_name,
        nc.nspname AS chunk_schema,
        c.relname AS chunk_name
    FROM pg_class ht
    JOIN pg_namespace nt ON ht.relnamespace = nt.oid
    JOIN pg_depend d ON ht.oid = d.objid
    JOIN pg_class c ON d.refobjid = c.oid
    JOIN pg_namespace nc ON c.relnamespace = nc.oid
    WHERE nt.nspname NOT IN ('pg_catalog', 'information_schema')
),
chunk_sizes AS (
    SELECT
        hypertable_schema || '.' || hypertable_name AS hypertable,
        chunk_schema || '.' || chunk_name AS chunk,
        pg_total_relation_size( (chunk_schema || '.' || chunk_name)::regclass ) AS total_bytes,
        pg_relation_size( (chunk_schema || '.' || chunk_name)::regclass ) AS table_bytes,
        pg_indexes_size( (chunk_schema || '.' || chunk_name)::regclass ) AS index_bytes
    FROM hypertable_chunks
)
SELECT
    hypertable,
    chunk,
    pg_size_pretty(total_bytes) AS total_size,
    pg_size_pretty(table_bytes) AS table_size,
    pg_size_pretty(index_bytes) AS index_size
FROM chunk_sizes
ORDER BY total_bytes DESC;