查看执行计划

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)