Re: Add ALL_CANDIDATES option to EXPLAIN

On Fri, Jul 26, 2024 at 1:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wonder how far you'd get by just printing the surviving paths
> (that is, something like Anthonin's patch, but without lobotomizing
> add_path). The survivors would have to dominate the cheapest-total
> path along one of the other metrics add_path considers, which
> seems like a rough approximation of "interestingly different".

My guess is it wouldn't be that great. It seems easy to imagine that
the key decision for a particular plan might be whether to use table A
or B as the driving table, or whether to sequential scan or index scan
some table involved in the query. It could well be that you end up
with the same output ordering either way (or no output ordering) for
one reason or another. I'm actually not sure "interestingly different"
can be defined in a useful, general way, because how is the computer
to know what the human being cares about in a particular case? In
practice, it feels like what you'd often like to do is say "show me
the plan if you { started with table | used scan method Y on table X |
did not use index X | whatever }". I haven't given up on the idea that
there could be some way of defining interesting-ness that avoids the
need for the user to say what they think is interesting, but it
certainly feels like one needs to be a lot more clever to make it work
without user input.

--
Robert Haas
EDB: http://www.enterprisedb.com