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 1000postgres=# 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
pg_explaintips.tgz
Description: application/compressed-tar
