The auto_explain module provides a means for logging execution plans of slow statements automatically, without having to run EXPLAIN by hand. This is especially helpful for tracking down un-optimized queries in large applications.
https://www.postgresql.org/docs/current/static/auto-explain.html
postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_buffers = true
auto_explain.log_buffers = true
auto_explain.log_timing = true
auto_explain.log_triggers = true
auto_explain.log_nested_statements = true
[root@test01 pg_log]# vim postgresql-2016-05-28_004047.csv
2016-05-28 00:41:23.990 CST,"postgres","postgres",7263,"[local]",574878a4.1c5f,3,"SELECT",2016-05-28 00:41:08 CST,2/3,0,LOG,00000,"duration: 0.808 ms plan:
Query Text: SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;
Aggregate (cost=22.07..22.08 rows=1 width=0) (actual time=0.785..0.785 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=46 read=1
-> Hash Join (cost=5.71..21.80 rows=109 width=0) (actual time=0.352..0.736 rows=109 loops=1)
Hash Cond: (pg_class.oid = pg_index.indrelid)
Buffers: shared hit=46 read=1
-> Index Only Scan using pg_class_oid_index on pg_catalog.pg_class (cost=0.15..12.81 rows=311 width=4) (actual time=0.088..0.285 rows=313 loops=1)
Output: pg_class.oid
Heap Fetches: 124
Buffers: shared hit=40 read=1
-> Hash (cost=4.20..4.20 rows=109 width=4) (actual time=0.187..0.187 rows=109 loops=1)
Output: pg_index.indrelid
Buckets: 1024 Batches: 1 Memory Usage: 12kB
Buffers: shared hit=3
-> Seq Scan on pg_catalog.pg_index (cost=0.00..4.20 rows=109 width=4) (actual time=0.008..0.094 rows=109 loops=1)
Output: pg_index.indrelid
Filter: pg_index.indisunique
Rows Removed by Filter: 11
Buffers: shared hit=3",,,,,,,,"explain_ExecutorEnd, auto_explain.c:333","psql.bin"
2016-05-28 00:41:23.990 CST,"postgres","postgres",7263,"[local]",574878a4.1c5f,4,"SELECT",2016-05-28 00:41:08 CST,2/0,0,LOG,00000,"duration: 5.327 ms",,,,,,,,"exec_simple_query, postgres.c:1174","psql.bin"