查看执行计划
explain analyze verbose select * from t2,t3 where t2.n1=t3.n2;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=85.58..257.60 rows=6950 width=82) (actual time=0.029..0.031 rows=1 loops=1) Output: t2.n1, t3.n2 Merge Cond: (t2.n1 = (t3.n2)::bpchar) -> Index Only Scan using idx_t2 on public.t2 (cost=0.15..65.10 rows=1130 width=44) (actual time=0.013..0.014 rows=1 loops=1) Output: t2.n1 Heap Fetches: 1 -> Sort (cost=85.43..88.50 rows=1230 width=38) (actual time=0.011..0.011 rows=2 loops=1) Output: t3.n2 Sort Key: t3.n2 USING < Sort Method: quicksort Memory: 25kB -> Seq Scan on public.t3 (cost=0.00..22.30 rows=1230 width=38) (actual time=0.004..0.005 rows=2 loops=1) Output: t3.n2 Planning Time: 0.078 ms Execution Time: 0.049 ms (14 rows)
analyze 实际执行一次语句
verbose 是否显示详细信息
收集统计信息
analyze t2;
\x
postgres=# select * from pg_stats where tablename='t2';
-[ RECORD 1 ]----------+-------
schemaname | public
tablename | t2
attname | n1
inherited | f
null_frac | 0
avg_width | 11
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds |
correlation |
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
postgres=# select * from pg_stat_user_tables where relname='t2'; -[ RECORD 1 ]-------+------------------------------ relid | 24627 schemaname | public relname | t2 seq_scan | 1 seq_tup_read | 0 idx_scan | 4 idx_tup_fetch | 4 n_tup_ins | 1 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | 2023-03-27 18:17:12.591177+08 last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 1 autoanalyze_count | 0
设置抓取慢日志
pg 参数设置途径:1、修改postgresql.conf 2、通过alter system 修改,alter system 写入postgresql.auto.conf文件中,此文件启动时会在postgresql.conf后加载
alter system set log_statement='all';
alter system set log_min_duration_statement=5000;
重启pg pg_ctl restart
postgres=# show log_statement; 2023-03-28 09:50:30.849 CST [4002] LOG: statement: show log_statement; log_statement --------------- all (1 row) postgres=# show log_min_duration_statement; 2023-03-28 09:50:41.863 CST [4002] LOG: statement: show log_min_duration_statement; log_min_duration_statement ---------------------------- 5s (1 row)
查看当前正在执行的慢sql
postgres=# select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '5 s' order by query_start ; (0 rows)