Postgresql Analysis

pg.md

Postgres Analysis

Table Size

List sizes of tables in current schema.

SELECT
    table_name                                                      AS table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size,
    pg_size_pretty(pg_table_size(quote_ident(table_name)))          AS table_size,
    pg_size_pretty(pg_indexes_size(quote_ident(table_name)))        AS indexes_size
FROM
    information_schema.tables
WHERE
    table_schema = current_schema()
ORDER BY
    pg_total_relation_size(quote_ident(table_name)) DESC;

Uses:

• current_schema
• pg_indexes_size
• pg_size_pretty
• pg_table_size
• pg_total_relation_size
• quote_ident
• tables

Index Size & Usage

SELECT
    n.nspname                                      AS namespace_name,
    t.relname                                      AS table_name,
    pg_size_pretty(pg_relation_size(t.oid))        AS table_size,
    t.reltuples::bigint                            AS num_rows,
    psai.indexrelname                              AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END  AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched
FROM
    pg_index i
    INNER JOIN pg_class t               ON t.oid = i.indrelid
    INNER JOIN pg_namespace n           ON n.oid = t.relnamespace
    INNER JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    n.nspname = current_schema()
ORDER BY 1, 2, 5;

Concurrent Index Creation Progress

SELECT
  now()::TIME(0),
  a.query,
  p.phase,
  round(p.blocks_done / p.blocks_total::numeric * 100, 2) AS "% done",
  p.blocks_total,
  p.blocks_done,
  p.tuples_total,
  p.tuples_done,
  ai.schemaname,
  ai.relname,
  ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;

Locks

SELECT
    a.application_name AS app,
    a.query            AS query,
    l.locktype         AS locktype,
    l.mode             AS mode,
    l.granted          AS granted,
    r.relname          AS relation
FROM
    pg_catalog.pg_locks AS l
    JOIN pg_catalog.pg_stat_activity AS a
        ON a.pid = l.pid
    LEFT OUTER JOIN pg_catalog.pg_class AS r
        ON r.oid = l.relation
WHERE
    a.pid != pg_backend_pid();