Re: making EXPLAIN extensible

Hi,

>>> EXPLAIN output. It wouldn't make sense for core to have an EXPLAIN
>>> option whose whole purpose is to cater to the needs of some extension,
>>> so that made me think of providing some extensibility infrastructure.

>> Making EXPLAIN extensible sounds like a good idea.. FWIW, There is a
>> discussion [0]
>> for showing FDW remote plans ( postgres_fdw specifically), and I think
>> we will need to
>> add some new options to EXPLAIN to make that possible.

> Have not looked at your patches, but I will do so now.

Over the past few days I have had a chance to experiment with these
patches and good news is that it has allowed me to extend EXPLAIN
for postrges_fdw to show remote plans. I will share the update and
patch for this in [0], but thought it will be good to share here as well.

postgres=# explain (remote_plans) select * from t_r1, t1_r1;
QUERY PLAN
----------------------------------------------------------------------------
Nested Loop (cost=200.00..83272.80 rows=6553600 width=16)
Plan Node ID: 0
-> Foreign Scan on t_r1 (cost=100.00..673.20 rows=2560 width=8)
Plan Node ID: 1
-> Materialize (cost=100.00..686.00 rows=2560 width=8)
Plan Node ID: 2
-> Foreign Scan on t1_r1 (cost=100.00..673.20 rows=2560 width=8)
Plan Node ID: 3
Remote Plans:
Seq Scan on t (cost=0.00..32.60 rows=2260 width=8)
Statement Name: Plan Node ID = 1

Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
Statement Name: Plan Node ID = 3
(14 rows)

I do have some findings/suggestions:

1/ As you can see form the output above, I used explain_per_node_hook
to append a "Plan Node ID" to the explain output. I really don't like having it
there, and prefer that it gets added to the top line of the node.

i.e.
-> Foreign Scan on t_r1 (cost=100.00..673.20 rows=2560 width=8) (node_id=1)
-> Materialize (cost=100.00..686.00 rows=2560 width=8) (node_id=2)
-> Foreign Scan on t1_r1 (cost=100.00..673.20 rows=2560
width=8) (node_id=3)

Can we add a hook at that point [1] which will allow an extension to modify
the first line of a node? I think this is not just useful for my case, but also
for other use-cases in which some high level node details could be placed.
what do you think?

2/ I registered an options handler, and I wanted this options handler to
validate that my new extension option is not used with the analyze
option.

So, the behavior is if the core explain option was first in the list, it worked,
but if it was first in the list it does not.

postgres=# explain (analyze, remote_plans) select from t_r1, t1_r1;
ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together

postgres=# explain (remote_plans, analyze) select from t_r1, t1_r1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=200.00..147337.37 rows=11648569 width=0) (actual
time=3.222..3.236 rows=0.00 loops=1)
...
....

This is because the ApplyExtensionExplainOption is called inside the main
loop that parses the options, so when ApplyExtensionExplainOption, the core
option may or may not have been set yet. I also think this will break if there
are multiple extension options that need to be validated together.

One way I thought to fix this is to allow the user to register another handler
for validation, which can then be called after the parse option loop, and after
all the in-core options have been validated against each other. Right after
this line.

+ /* if the summary was not set explicitly, set default value */
+ es->summary = (summary_set) ? es->summary : es->analyze;

What do you think?

[0] https://www.postgresql.org/message-id/flat/CAP%2BB4TD%3Diy-C2EnsrJgjpwSc7_4pd3Xh-gFzA0bwsw3q8u860g%40mail.gmail.com
[1] https://github.com/postgres/postgres/blob/master/src/backend/commands/explain.c#L2013

Thanks

--
Sami Imseih
Amazon Web Services (AWS)