Flame Graph — PostgreSQL Backend Running a Slow Report Query (perf)
perf samples from the one backend running a 41-second month-end revenue report (4,360 samples): 50.5% is a sequential scan feeding the hash join's build side, 18.8% of it waiting on reads from disk, and the sort that spills to temporary files costs a further 20%. Planning is 1.1% — the plan is the problem, not the planner. Illustrative values.
Make it your own.
title "postgres backend — month-end revenue report, perf 4,360 samples (illustrative)"
# perf record -F 99 -g -p <backend pid> -- sleep 40; perf script | stackcollapse-perf.pl
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;tuplesort_performsort;qsort_tuple;comparetup_heap 610
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;ExecHashJoin;ExecScanHashBucket;ExecQual 455
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;ExecHashJoin;MultiExecHash;ExecSeqScan;heap_getnextslot;heapgettup_pagemode 1380
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;ExecHashJoin;MultiExecHash;ExecSeqScan;heap_getnextslot;ReadBufferExtended;smgrread;pread64 820
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;ExecHashJoin;ExecIndexScan;index_getnext_slot;_bt_next 240
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;ExecHashJoin;ExecIndexScan;index_fetch_heap;heapam_index_fetch_tuple 205
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;ExecHashJoin;MultiExecHash;ExecHashTableInsert 330
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;PortalRun;PortalRunSelect;standard_ExecutorRun;ExecutePlan;ExecSort;tuplesort_puttupleslot;writetup_heap;BufFileWrite 260
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;pg_plan_queries;planner;standard_planner 48
postgres;PostmasterMain;ServerLoop;BackendStartup;PostgresMain;exec_simple_query;pg_analyze_and_rewrite 12