Re: Add ALL_CANDIDATES option to EXPLAIN

On Fri, Jul 26, 2024 at 10:47 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Fri, Jul 26, 2024 at 12:59 PM Anthonin Bonnefoy
> <anthonin(dot)bonnefoy(at)datadoghq(dot)com> wrote:
> > I have a prototype for an ALL_CANDIDATES option for EXPLAIN. The goal
> > of this option is to print all plan candidates instead of only the
> > cheapest plan. It will output the plans from the most expensive at the
> > top to the cheapest. Here's an example:
>
> It's difficult for me to understand how this can work. Either it's not
> really going to print out all candidates, or it's going to print out
> gigabytes of output for moderately complex queries.
>
> I've thought about trying to figure out some way of identifying and
> printing out plans that are "interestingly different" from the chosen
> plan, with the costs they would have had, but I haven't been able to
> come up with a good algorithm. Printing out absolutely everything
> doesn't seem viable, because planning would be slow and use amazing
> amounts of memory and the output would be so large as to be useless.

If we print the path forest as a forest as against individual path
trees, we will be able to cut down on the size but it will still be
huge. Irrespective of that even with slightly non-trivial queries it's
going to be difficult to analyze these paths. The way I think of it is
dumping this information in the form of tables. Roughly something like
a table containing RelOptInfo id and RelOptInfo itself and another
containing all the paths identified by id and RelOptInfo id. The path
linkages are stored as path ids. That's a minimum. We will need more
tables to store query, and other metadata. If we do so we can use SQL
to carry out investigations.

--
Best Wishes,
Ashutosh Bapat