Hi,
I have a prototype for an ALL_CANDIDATES option for EXPLAIN. The goal
of this option is to print all plan candidates instead of only the
cheapest plan. It will output the plans from the most expensive at the
top to the cheapest. Here's an example:
explain (all_candidates) select * from pgbench_accounts where aid=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Plan 1
-> Gather (cost=1000.00..3375.39 rows=1 width=97)
Workers Planned: 1
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..2375.29 rows=1 width=97)
Filter: (aid = 1)
Plan 2
-> Seq Scan on pgbench_accounts (cost=0.00..2890.00 rows=1 width=97)
Filter: (aid = 1)
Plan 3
-> Bitmap Heap Scan on pgbench_accounts (cost=4.30..8.31 rows=1 width=97)
Recheck Cond: (aid = 1)
-> Bitmap Index Scan on pgbench_accounts_pkey
(cost=0.00..4.30 rows=1 width=0)
Index Cond: (aid = 1)
Plan 4
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.29..8.31 rows=1 width=97)
Index Cond: (aid = 1)
This can provide very useful insight on the planner's decisions like
whether it tried to use a specific index and how much cost difference
there is with the top plan. Additionally, it makes it possible to spot
discrepancies in generated plans like incorrect row estimation [1].
The plan list is generated from the upper_rel's pathlist. However, due
to how planning mutates the PlannerGlobal state, we can't directly
iterate the path list generated by the subquery_planner and create a
planned statement for them. Instead, we need to plan from scratch for
every path in the pathlist to generate the list of PlannedStmt.
The patch is split in two mostly to ease the review:
001: Propagate PlannerInfo root to add_path. This is needed to prevent
add_path from discarding path if all_candidates is enabled which will
be stored in PlannerGlobal.
002: Add the planner_all_candidates function and print of candidate
list in explain
[1] https://www.postgresql.org/message-id/flat/CAO6_Xqr9+51NxgO=XospEkUeAg-p=EjAWmtpdcZwjRgGKJ53iA@mail.gmail.com
Regards,
Anthonin