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.