Re: Planner debug views

On Thu, Jul 23, 2015 at 4:11 PM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
> Sounds like a great feature!
>

Thanks!

Attached is a draft patch implementing the idea. To play with it, you
shall create the follow two foreign tables:
CREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
create foreign table pg_planner_rels(rel text, content text)server
pglog options(filename '<your_install>/data/debug_planner_relopt.csv',
format 'csv');
create foreign table pg_planner_paths(rel text, path text, replacedby
text, reason int, startupcost float, totalcost float, cheapest text,
innerp text, outerp text, content text) server pglog options(filename
'<your_install>/data/debug_planner_paths.csv', format 'csv');
Example output attached.

Questions:
1. Which document shall we update? This is more than existing
debug_print_ knobs.
2. GEQO is not supported yet. I would suggest we do that with a
separate check in.
3. Where do we want to put the csv files? Currently I just put them under /data.
4. Do we want to push these two foreign tables into system_view.sql?
One problem is that foreign table needs a absolute path. Any way to
handle this?
5. As the output is csv file: I wrap strings with '"' but not sure
within the string itself if there any. Do we have any guarantee here?

Thanks,
Qingqing

---

postgres=# select p.rel, p.path, p.replacedby, p.reason,
p.startupcost, p.totalcost, p.cheapest, p.innerp, p.outerp,
substr(p.content, 1,30),r.content from pg_planner_paths p join
pg_planner_rels r on p.rel=r.rel;
rel | path | replacedby | reason | startupcost | totalcost
| cheapest | innerp | outerp | substr
| content
-----------+-----------+------------+--------+-------------+-----------+----------------------+-----------+-----------+--------------------------------+------------------------------------------------
0x2791a10 | 0x279d4b0 | | | 0 | 40.1
| +total+startup+param | | | ForeignScan(1)
rows=301 cost=0 | RELOPTINFO (1): rows=301 width=244
0x279f998 | 0x27a2238 | | | 0 | 1.1
| +total+startup+param | | | ForeignScan(1) rows=1
cost=0.0 | RELOPTINFO (1): rows=1 width=244
0x279fbd0 | 0x27a28b8 | | | 0 | 1.1
| +total+startup+param | | | ForeignScan(2) rows=1
cost=0.0 | RELOPTINFO (2): rows=1 width=64
0x27a2ab0 | 0x27a3c68 | | | 0 | 2.21
| +total+startup+param | 0x27a28b8 | 0x27a2238 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4608 | 0x27a4608 | 2 | 1.11 | 2.23
| | 0x27a2238 | 0x27a28b8 | HashJoin(1 2) rows=1
cost=1.11 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4498 | 0x27a4498 | 0 | 0 | 2.22
| | 0x27a4330 | 0x27a28b8 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4388 | 0x27a4388 | 0 | 0 | 2.21
| | 0x27a2238 | 0x27a28b8 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4220 | 0x27a4220 | 2 | 2.22 | 2.25
| | 0x27a2238 | 0x27a28b8 | MergeJoin(1 2) rows=1
cost=2.2 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a3f90 | 0x27a3f90 | 2 | 1.11 | 2.23
| | 0x27a28b8 | 0x27a2238 | HashJoin(1 2) rows=1
cost=1.11 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a3e20 | 0x27a3e20 | 0 | 0 | 2.22
| | 0x27a3c10 | 0x27a2238 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a3b18 | 0x27a3c68 | 1 | 2.22 | 2.25
| | 0x27a28b8 | 0x27a2238 | MergeJoin(1 2) rows=1
cost=2.2 | RELOPTINFO (1 2): rows=1 width=308