PG如何实现查看历史SQL语句的执行计划

郑全

我们知道ORACLE有ASH可以记录历史SQL语句，那PG呢

PG本身只是记录历史慢SQL，但要看到当时的执行计划，需要增加auto_explain扩展，该扩展默认是自带的，只是需要设置一下。
下面就来描述一下如何实现：

1.设置配置文件

shared_preload_libraries='auto_explain'

--重启生效
pg_ctl restart

2.设置相关参数
alter system set auto_explain.log_analyze=on;
alter system set auto_explain.log_min_duration='5s';
--配置生效
select pg_reload_conf();

3.验证
  select pg_sleep(10);

  这个时候，去日志文件中，可以看到超过5S的SQL语句执行计划

登录/注册后可看大图

郑全

或者具体的SQL语句

1)模拟表数据
  create table t1 as select * from pg_class;
  insert into t1 select * from t1;
  select count(1) from t1 a;

2.执行SQL
  select count(1) from t1 a,t1 b,t1 c;

3.查看执行计划
  2025-07-26 23:43:28.925 CST,"postgres","postgres",26522,"[local]",6884f680.679a,13,"SELECT",2025-07-26 23:38:40 CST,2/16776,0,LOG,00000,"duration: 113114.265 ms  plan:
Query Text: select count(1) from t1 a,t1 b,t1 c;
Aggregate  (cost=9417597.40..9417597.41 rows=1 width=8) (actual time=113114.234..113114.238 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..7849542.36 rows=627222016 width=0) (actual time=0.026..81300.633 rows=627222016 loops=1)
        ->  Nested Loop  (cost=0.00..9230.46 rows=732736 width=0) (actual time=0.020..171.669 rows=732736 loops=1)
              ->  Seq Scan on t1 a  (cost=0.00..34.56 rows=856 width=0) (actual time=0.013..2.589 rows=856 loops=1)
              ->  Materialize  (cost=0.00..38.84 rows=856 width=0) (actual time=0.000..0.077 rows=856 loops=856)
                    ->  Seq Scan on t1 b  (cost=0.00..34.56 rows=856 width=0) (actual time=0.004..0.258 rows=856 loops=1)
        ->  Materialize  (cost=0.00..38.84 rows=856 width=0) (actual time=0.000..0.042 rows=856 loops=732736)
              ->  Seq Scan on t1 c  (cost=0.00..34.56 rows=856 width=0) (actual time=0.004..0.095 rows=856 loops=1)",,,,,,,,,"psql","client backend",,8235289341370696194
2025-07-26 23:43:28.925 CST,"postgres","postgres",26522,"[local]",6884f680.679a,14,"SELECT",2025-07-26 23:38:40 CST,2/0,0,LOG,00000,"duration: 113115.019 ms",,,,,,,,,"psql","client backend",,8235289341370696194