pg_plan_alternatives: Tracing PostgreSQL Query Plan Alternatives Using eBPF
11 hours ago
- #eBPF
- #PostgreSQL
- #Query Optimization
- PostgreSQL uses a Cost-Based Optimizer (CBO) to determine the best query execution plan by evaluating multiple alternatives based on estimated costs.
- The EXPLAIN command only shows the chosen plan, not the alternatives; pg_plan_alternatives addresses this gap by using eBPF to trace all considered plans during optimization.
- pg_plan_alternatives consists of an eBPF kernel-space program, a user-space script to collect events, and a visualization tool to display alternative plans and costs.
- The tool captures paths dynamically by instrumenting PostgreSQL functions like add_path and create_plan, extracting field offsets from debug info to handle opaque structs.
- Examples demonstrate how pg_plan_alternatives reveals alternative plans for various queries (e.g., SELECT, JOIN, GROUP BY), helping users understand optimizer decisions and tune parameters like random_page_cost.