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 running (as superuser):

ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
SELECT pg_reload_conf();

Then, in a new Postgres session (with the auto_explain module loaded) configure parameters as needed, for example:

ALTER SYSTEM SET auto_explain.log_min_duration = 2000;
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_triggers = on;
ALTER SYSTEM SET auto_explain.log_nested_statements = on;
SELECT pg_reload_conf();

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.