On Fri, Jul 26, 2024 at 10:47 PM Robert Haas <robertmh...@gmail.com> wrote: > > On Fri, Jul 26, 2024 at 12:59 PM Anthonin Bonnefoy > <anthonin.bonne...@datadoghq.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
