Re: Add ALL_CANDIDATES option to EXPLAIN

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.

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