Небольшой список полезных запросов в СУБД 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;
CREATE INDEX super_index ON public.super_table USING brin (((created_at)::date))
EXPLAIN (analyze true, buffers true, timing true)
SELECT ....
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;
show work_mem;
show random_page_cost;
show seq_page_cost;
show effective_cache_size;
VACUUM FULL VERBOSE ANALYZE;
SELECT * FROM pg_stat_progress_vacuum;
CLUSTER VERBOSE super_table USING super_index;
SELECT * FROM pg_stat_activity;