Planner debug views - Mailing list pgsql-hackers

Here is a proposal introducing some debugging infrastructure into the
core. The basic idea is to allow us to query the planner search space.
To so do, we can dump related information to csv files and use foreign
table to query them. So here I propose two foreign tables:
 create foreign table pg_planner_rels(rel text, content text) ... create foreign table pg_planner_paths(rel text, path
text,
replacedby text, totalcost float, cheapest text, content text) ...

Where
1. pg_planner_rels consists of RelOptInfo information.
2. pg_planner_paths consists of paths belong to each rel.
3. Field "rel" is a RelOptInfo* pointer so these two tables can join each other.
4. We can further adding subqueries view, or expand pg_planner_paths
table easily adding startup_cost etc.

And an example output is attached. The nice thing about it, is it
reusing the database query power to diagnose database query problems
:-). From the tables, we can find out all paths considered and why
they are discarded.

I have a draft implementation of above idea - main changes including:
1. Keep removed path in a separate list, also the reason of removal
(see "replacedby" field);
2. Change debug_print_rel() family to use StringInfo instead of printf().

I would also suggest we change DEBUG_OPTIMIZER into a GUC, because we
need this ability in release build as well. There is almost no
overhead when GUC is off.

Before I get into more details, I'd like to see if we see this is a
good way to go.

Thoughts?

Regards,
Qingqing


-- query to test
postgres=# explain select a.i, count(a.i) from a join b on a.i=b.i
group by a.i order by a.i limit 1;                                     QUERY PLAN

--------------------------------------------------------------------------------------Limit  (cost=14.47..15.64 rows=1
width=4) ->  GroupAggregate  (cost=14.47..120.67 rows=91 width=4)        Group Key: a.i        ->  Merge Join
(cost=14.47..97.51rows=4450 width=4)              Merge Cond: (a.i = b.i)              ->  Index Only Scan using ai on
a (cost=0.28..45.07
 
rows=600 width=4)              ->  Sort  (cost=14.20..14.72 rows=210 width=4)                    Sort Key: b.i
         ->  Seq Scan on b  (cost=0.00..6.10 rows=210 width=4)
 
(9 rows)

-- after query is done we can see rels
postgres=# select * from pg_planner_rels;  rel   |               content
---------+-------------------------------------2f49b70 | RELOPTINFO (1): rows=600 width=42f49d08 | RELOPTINFO (2):
rows=210width=42f4a590 | RELOPTINFO (1 2): rows=4450 width=41543340 | RELOPTINFO (1): rows=1 width=64
 
(4 rows)

-- and paths
postgres=# select rel, path, replacedby, totalcost, substr(content, 1,
30) from pg_planner_paths ;  rel   |  path   | replacedby | totalcost |             substr
---------+---------+------------+-----------+--------------------------------2f49b70 | 2f4abb8 |            |         1
|IdxScan(1) rows=600 cost=0.28.2f49b70 | 2f4a1f0 |            |        10 | SeqScan(1) rows=600 cost=0.00.2f49b70 |
2f49e10|            |        45 | IdxScan(1) rows=600 cost=0.28.2f49b70 | 2f4d6b8 | 2f4d6b8    |         5 |
BitmapHeapScan(1)rows=600 cos2f49d08 | 2f4e3e0 |            |         6 | SeqScan(2) rows=210 cost=0.00.2f4a590 |
2f4f938|            |        77 | HashJoin(1 2) rows=4450 cost=12f4a590 | 2f4f678 |            |        88 | HashJoin(1
2)rows=4450 cost=82f4a590 | 2f4f330 |            |        98 | MergeJoin(1 2) rows=4450 cost=2f4a590 | 2f4f850 |
   |       140 | NestLoop(1 2) rows=4450 cost=02f4a590 | 2f4f188 |            |      1907 | NestLoop(1 2) rows=4450
cost=02f4a590| 2f4f278 |            |      1942 | NestLoop(1 2) rows=4450 cost=02f4a590 | 2f4f8a8 | 2f4f8a8    |
1908| NestLoop(1 2) rows=4450 cost=02f4a590 | 2f4f700 | 2f4f700    |       105 | MergeJoin(1 2) rows=4450 cost=2f4a590
|2f4eea0 | 2f4f330    |       120 | MergeJoin(1 2) rows=4450 cost=2f4a590 | 2f4f220 | 2f4f278    |      5280 |
NestLoop(12) rows=4450 cost=02f4a590 | 2f4efb8 | 2f4f188    |      5245 | NestLoop(1 2) rows=4450 cost=01543340 |
1543d08|            |         1 | ForeignScan(1) rows=1 cost=0.01543cf8 | 2f49a20 |            |         3 |
ForeignScan(1)rows=17 cost=0.
 
(18 rows)