On Wed, May 21, 2025 at 12:03 PM Maciek Sakrejda <m.sakre...@gmail.com> wrote: > That may be due to your extensive experience with Postgres and EXPLAIN plans.
Yes, that is very possible. All things being equal, it helps to have done something a lot of times. > Fair enough, although the people trying to make sense of EXPLAIN plans > are sometimes not the same ones who are writing the queries. And > sometimes the queries are not written by people at all but by ORMs > (or—heaven help us—vibe coded). "Don't do X" is a reasonable response > to "It hurts when I do X," but it doesn't really solve the user's > problem. That said, it's hard to argue with "We don't have any good > ideas on how to improve this right now, and it's not a total dumpster > fire, so we'll focus on other work." +1 to all of that. > No great ideas here. I thought initially that a good solution would be > to have structured EXPLAIN output include something like "Query Text > Start Index" and "Query Text End Index" fields for each node, but I > realized that this doesn't really work for multiple joins (and > probably other cases). Maybe "Query Text Indices", as a list of pairs? > But from the little I know about the planner, that seems like any sort > of tracking back to the source would be hard to implement. And it only > really solves the problem for external EXPLAIN viewers, and only ones > that put in the work to support this. I'm not sure if the problem can > be meaningfully addressed for text format, but maybe that's another > reason not to spend time on it in core. I'm not gonna say you couldn't make something like that work, but it sounds like a lot of effort for a hypothetical piece of external visualization software that might or might not produce satisfying results. My advice to anyone wanting to pursue this idea would be: make a totally fake POC first. Get a sample query with at least a moderately complex plan, get the EXPLAIN output, manually generate whatever data you think PostgreSQL ought to be able to spit out, and do a mock-up of an external viewer. When you're happy with the results, show it to some other people and see if they also like it. We can have the discussion about whether to include anything in core and what it should be after that. I definitely would not rule out the possibility that something like this could turn out to be really cool -- maybe hovering over stuff and having the corresponding part of the plan get highlighted will turn out to be awesome. But I think it might also turn out that there are things where it's not quite clear what you can or should usefully highlight, like target-list items, or for example a case where the query says that a.x = b.x and b.x = c.x but in the actual plan we use evaluate a.x = c.x, an expression not appearing anywhere in the query text. The legwork of sorting some of that kind of stuff out should really happen before making a feature proposal. > I'm intrigued, and happy to stand by with an extinguisher. The road to > great ideas is paved with bad ideas. Thanks. That proposal is a task for another day, but I appreciate the sentiment. -- Robert Haas EDB: http://www.enterprisedb.com
