auto_explain
The Postgres auto_explain
module will log execution plans of qualifying
statements at the time of their execution. That can be especially helpful in
understanding the circumstances of the execution of a query you are working to
optimize. The Crunchy Data Blog has an introduction to decyphering EXPLAIN
plans here:
Explaining Your Postgres Query Performance
You can enable auto_explain
for your Crunchy Bridge cluster by adding auto_explain
to the
session_preload_libraries
parameter.
Once enabled, you can update the parameters used by auto_explain
using configuration parameters.
cb config-param set --cluster f2rhvrmi4fhjpn23qxs3mitqza postgres:auto_explain.log_min_duration=2000
cb config-param set --cluster f2rhvrmi4fhjpn23qxs3mitqza postgres:auto_explain.log_analyze=on
cb config-param set --cluster f2rhvrmi4fhjpn23qxs3mitqza postgres:auto_explain.log_triggers=on
cb config-param set --cluster f2rhvrmi4fhjpn23qxs3mitqza postgres:auto_explain.log_nested_statements=on
Subsequent Postgres sessions will then be configured with those settings and
will automatically log the EXPLAIN
plan for any qualifying statements to your
Postgres logs. As an example of how these statements will
look in logs, this statement was logged for exceeding 2000 ms:
2022-08-18 18:11:03.563 GMT [281544][client backend][29/306][22122] [user=application,db=postgres,app=pgbench] LOG: duration: 2141.270 ms plan:
Query Text: UPDATE pgbench_branches SET bbalance = bbalance + -3579 WHERE bid = 1;
Update on pgbench_branches (cost=0.00..4.13 rows=0 width=0) (actual time=2141.267..2141.267 rows=0 loops=1)
-> Seq Scan on pgbench_branches (cost=0.00..4.13 rows=1 width=10) (actual time=0.034..0.036 rows=1 loops=1)
Filter: (bid = 1)
Rows Removed by Filter: 9
For more information on available settings and options, refer to the Postgres docs on auto_explain.