postgresql查询慢的排查脚本

– 连接到 PostgreSQL 数据库

\c dbname user

– 查看当前会话的所有进程

SELECT * FROM pg_stat_activity;

– 查看当前会话的所有锁

SELECT * FROM pg_locks;

– 查看当前会话的所有等待事件

SELECT * FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;

– 查看当前会话的所有等待事件和等待时间

SELECT pid, wait_event_type, wait_event, query_start, state_change, now() - state_change AS waiting_duration FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;

– 查看当前会话的所有等待事件和等待时间，按等待时间排序

SELECT pid, wait_event_type, wait_event, query_start, state_change, now() - state_change AS waiting_duration FROM pg_stat_activity WHERE wait_event_type IS NOT NULL ORDER BY waiting_duration DESC;

– 查看当前会话的所有等待事件和等待时间，按等待时间排序，只显示等待时间超过 1 秒的会话

SELECT pid, wait_event_type, wait_event, query_start, state_change, now() - state_change AS waiting_duration FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND now() - state_change > interval '1 second' ORDER BY waiting_duration DESC;

– 查看当前数据库的所有表

SELECT * FROM pg_tables WHERE schemaname = 'public';

– 查看当前数据库的所有索引

SELECT * FROM pg_indexes WHERE schemaname = 'public';

– 查看当前数据库的所有表和表的大小

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

– 查看当前数据库的所有索引和索引的大小

SELECT relname, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_catalog.pg_statio_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;

– 查看当前数据库的所有表和表的行数

SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

– 查看当前数据库的所有索引和索引的扫描次数

SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

– 查看当前数据库的所有表和表的索引使用情况

SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY seq_scan DESC;

– 查看当前数据库的所有索引和索引的使用情况

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

– 查看当前数据库的所有表和表的自增序列

SELECT c.relname, a.attname, pg_get_serial_sequence(c.relname, a.attname) FROM pg_class c, pg_attribute a WHERE c.relname LIKE 'table_name' AND a.attname LIKE 'column_name' AND a.attrelid = c.oid AND a.attnum > 0;

– 查看当前数据库的所有表和表的外键

SELECT conname, conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE confrelid = 'table_name'::regclass;

– 查看当前数据库的所有表和表的触发器

SELECT tgname, tgrelid::regclass, tgtype FROM pg_trigger WHERE tgrelid = 'table_name'::regclass;

– 查看当前数据库的所有表和表的触发器函数

SELECT proname, proargtypes FROM pg_trigger JOIN pg_proc ON tgfoid = pg_proc.oid WHERE tgrelid = 'table_name'::regclass;

– 查看当前数据库的所有表和表的触发器函数的源代码

SELECT prosrc FROM pg_trigger JOIN pg_proc ON tgfoid = pg_proc.oid WHERE tgrelid = 'table_name'::regclass;

– 查看当前数据库的所有表和表的触发器函数的依赖关系

SELECT proname, proargtypes, pg_get_functiondef(pg_proc.oid) FROM pg_trigger JOIN pg_proc ON tgfoid = pg_proc.oid WHERE tgrelid = 'table_name'::regclass;

– 查看当前数据库的所有表和表的触发器函数的依赖关系，只显示依赖于其他函数的函数

SELECT proname, proargtypes, pg_get_functiondef(pg_proc.oid) FROM pg_trigger JOIN pg_proc ON tgfoid = pg_proc.oid WHERE tgrelid = 'table_name'::regclass AND pg_proc.proname <> 'exec_trigger_func';

– 查看当前数据库的所有表和表的触发器函数的依赖关系，只显示依赖于其他函数的函数，按依赖关系排序

SELECT proname, proargtypes, pg_get_functiondef(pg_proc.oid) FROM pg_trigger JOIN pg_proc ON tgfoid = pg_proc.oid WHERE tgrelid = 'table_name'::regclass AND pg_proc.proname <> 'exec_trigger_func' ORDER BY pg_proc.proname; 

– 查看当前数据库的所有表和表的触发器函数的依赖关系，只显示依赖于其他函数的函数，按依赖关系排序，只显示函数名和参数类型

SELECT proname, proargtypes FROM pg_trigger JOIN pg_proc ON tgfoid = pg_proc.oid WHERE tgrelid = 'table_name'::regclass AND pg_proc.proname <> 'exec_trigger_func' ORDER BY pg_proc.proname;