Re: making EXPLAIN extensible

On 28/02/2025 20:26, Robert Haas wrote:
> Prior to PostgreSQL 10, EXPLAIN had just 2 options: VACUUM and
> ANALYZE. Now, we're up to 12 options, which is already quite a lot,
> and there's plenty more things that somebody might like to do.
> However, not all of those things necessarily need to be part of the
> core code. My original reason for wanting to extend EXPLAIN was that I
> was thinking about an extension that would want to do a bunch of
> things and one of those things would be to add some information to the
> 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.
>
> However, there are other use cases, too, basically any of the normal
> reasons why extensibility is useful and desirable. You might need to
> get some information out a query plan that 99% of people don't care
> about. You could come up with your own way of formatting a query plan,
> but that's a big pain. It's a lot nicer if you can just add the detail
> that you care about to the EXPLAIN output without needing to modify
> PostgreSQL itself. Even if you think of something that really ought to
> be included in the EXPLAIN output by PostgreSQL, you can roll an
> extension out much quicker than you can get a change upstreamed and
> released. So I think EXPLAIN extensibility is, as a general concept,
> useful.
>
> So here are some patches.
>
> 0001 allows a loadable module to register new EXPLAIN options.
> Currently, EXPLAIN (FUNGUS) will error out, but if you want to make it
> work, this patch is for you. This patch also allows you to stash some
> state related to your new option, or options, in the ExplainState.
> Core options have hard-coded structure members; e.g. EXPLAIN (BUFFERS)
> sets es->buffers. If you add EXPLAIN (FUNGUS), there won't be an
> es->fungus, but you can get about the same effect using the new
> facilities provided here.
>
> 0002 provides hooks that you can use to make your new EXPLAIN options
> actually do something. In particular, this adds a new hook that is
> called once per PlanState node, and a new nook that is called once per
> PlannedStmt. Each is called at an appropriate point for you to tack on
> more output after what EXPLAIN would already produce.
>
> 0003 adds a new contrib module called pg_overexplain, which adds
> EXPLAIN (DEBUG) and EXPLAIN (RANGE_TABLE). I actually think this is
> quite useful for planner hacking, and maybe a few more options would
> be, too. Right now, if you want to see stuff that EXPLAIN doesn't
> clearly show, you have to use SET debug_print_plan = true, and that
> output is so verbose that finding the parts you actually want to see
> is quite difficult. Assuming it gives you the details you need,
> EXPLAIN (RANGE_TABLE) looks way, way better to me, and if we end up
> committing these patches I anticipate using this semi-regularly.
>
> There are plenty of debatable things in this patch set, and I mention
> some of them in the commit messages. The hook design in 0002 is a bit
> simplistic and could be made more complex; there's lots of stuff that
> could be added to or removed from 0003, much of which comes down to
> what somebody hacking on the planner would actually want to see. I'm
> happy to bikeshed all of that stuff; this is all quite preliminary and
> I'm not committed to the details. The only thing that would disappoint
> me is if somebody said "this whole idea of making EXPLAIN extensible
> is stupid and pointless and we shouldn't ever do it." I will argue
> against that vociferously. I think even what I have here is enough to
> disprove that hypothesis, but I have a bunch of ideas about how to do
> more. Some of those require additional infrastructure and are best
> proposed with that other infrastructure; some can be done with just
> this, but I ran out of time to code up examples so here is what I have
> got so far.
>
> Hope you like it, sorry if you don't.
>

I definitely LOVE it. I tried your patches and it works great. No real
surprise here :) I tried to code my own library (entirely based on
yours), and it's quite nice. Patch attached, not intended to be applied
on the repo, but just a nice use case.

This library adds "Tip" line for each tip it can give on a specific
node. Right now, it only handles "Rows Removed by Filter" on a
sequential scan, but there's much more we could add to it.

Here is an example on how to use it:

postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
pg_explaintips
(1 row)

postgres=# create table t1 (id integer);

CREATE TABLE

postgres=# insert into t1 select generate_series(1, 1000);

INSERT 0 1000
postgres=# explain (analyze,costs off,tips) select * from t1 where id>2;

QUERY PLAN
---------------------------------------------------------------
Seq Scan on t1 (actual time=0.042..0.337 rows=998.00 loops=1)
Filter: (id > 2)
Rows Removed by Filter: 2
Buffers: shared hit=5
Planning:
Buffers: shared hit=4
Planning Time: 0.079 ms
Execution Time: 0.479 ms
(8 rows)

postgres=# explain (analyze,costs off,tips) select * from t1 where id<2;

QUERY PLAN
-------------------------------------------------------------
Seq Scan on t1 (actual time=0.014..0.113 rows=1.00 loops=1)
Filter: (id < 2)
Rows Removed by Filter: 999
Buffers: shared hit=5
Tips: You should probably add an index!
Planning Time: 0.035 ms
Execution Time: 0.127 ms
(7 rows)

postgres=# explain (analyze,costs off,tips off) select * from t1 where
id<2;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on t1 (actual time=0.009..0.067 rows=1.00 loops=1)
Filter: (id < 2)
Rows Removed by Filter: 999
Buffers: shared hit=5
Planning:
Buffers: shared hit=5
Planning Time: 0.070 ms
Execution Time: 0.076 ms
(8 rows)

Just great. Hope your patchs will find their way in the 18 release.
Thanks a lot.

--
Guillaume Lelarge
Consultant
https://dalibo.com