From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add ALL_CANDIDATES option to EXPLAIN
In-reply-to: <CAO6_Xqoke6qntHS8odYXT_P2Lrj7dNXgsCds+C8zLOXBbNefvg@mail.gmail.com>
References: <CAO6_Xqoke6qntHS8odYXT_P2Lrj7dNXgsCds+C8zLOXBbNefvg@mail.gmail.com>
Comments: In-reply-to Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
	message dated "Fri, 26 Jul 2024 18:59:07 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1743893.1722014034.1@sss.pgh.pa.us>
Date: Fri, 26 Jul 2024 13:13:54 -0400
Message-ID: <1743894.1722014034@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1743894.1722014034%40sss.pgh.pa.us>
Precedence: bulk

Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> writes:
> 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.

This doesn't seem feasible at all to me.  If we don't prune plans
fairly aggressively at lower plan levels, we'll have a combinatorial
explosion in the amount of work the planner has to do.  Have you
tried this on even slightly complex plans --- say, a dozen-way join?
I do not think you'll like the runtime, the amount of memory consumed,
or the verboseness of the output.  (I wonder how it interacts with
GEQO, too.)

			regards, tom lane