Небольшой список полезных запросов в СУБД Postgres.

Индексы

Список индексов

\di+

Список индексов конкретной таблицы с выводом команд для их создания

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'my_table_name';

Список индексов с указанием размера и статистики по использованию

SELECT
    t.schemaname,
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
        ELSE 'N'
    END AS UNIQUE,
    number_of_scans,
    tuples_read,
    tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename = c.relname
LEFT OUTER JOIN (
    SELECT
        c.relname AS ctablename,
        ipg.relname AS indexname,
        x.indnatts AS number_of_columns,
        idx_scan AS number_of_scans,
        idx_tup_read AS tuples_read,
        idx_tup_fetch AS tuples_fetched,
        indexrelname,
        indisunique,
        schemaname
    FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class ipg ON ipg.oid = x.indexrelid
    JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
) AS foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1,2;

Еще один пример отображения статистики по индексам

SELECT * FROM pg_stat_user_indexes;

Пример создания BRIN-индекса

CREATE INDEX super_index ON public.super_table USING brin (((created_at)::date))

Анализ запросов

Explain

EXPLAIN (analyze true, buffers true, timing true)
SELECT .... 

Отключение bitmap scan

set enable_bitmapscan to off;

Анализ данных

Корреляция данных в таблице по полям

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('super_table')
ORDER BY 1, 2;

Статистика по БД

SELECT * FROM pg_stat_database;

Статистика по таблицам

SELECT * FROM pg_stat_user_tables;

Настройки БД

Получить значение work_mem

show work_mem;

Получить значение random_page_cost

show random_page_cost;

Получить значение seq_page_cost

show seq_page_cost;

Получить значение effective_cache_size

show effective_cache_size;

Обслуживание БД

Vacuum

VACUUM FULL VERBOSE ANALYZE;

Посмотреть прогресс по vacuum

SELECT * FROM pg_stat_progress_vacuum;

Кластеризация таблицы по конкретному индексу

CLUSTER VERBOSE super_table USING super_index;

Текущая активность БД

SELECT * FROM  pg_stat_activity;