[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Showing alternative query planner plans with explain ?
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2022-05-29 14:44:59
Message-ID: 3452884.1653835499 () sss ! pgh ! pa ! us
[Download RAW message or body]

Danny Shemesh <dany74q@gmail.com> writes:
> A tool I seem to be missing, and I wondered if such exists, is to have the
> planner output alternative plans for a given query, i.e. to say, give me
> the x top plans sorted by cost - I believe this would help shed some light
> on the internal state machine and subsequent tinkering less
> trial-and-error-ish.

This does not exist, because the planner only carries one plan to
completion, for reasons of speed and memory consumption.  I have seen
people hack things to print out info about paths (plan fragments)
as they are considered, but I consider that approach pretty useless:
the output is voluminous, not very readable, and mostly not interesting.

You can get some of the effect by successively disabling the
believed-cheapest plan choice with the "enable_xxx" parameters
and seeing what the next choice is.  Fooling around with the cost
parameters can also provide useful insight.

If the concern you have is about join order and not the details
of the individual join types, another trick is to set
join_collapse_limit to 1 and then write the FROM clause as a
manually-parenthesized JOIN nest.  That setting will force the
planner to join in exactly the syntactic join order.

			regards, tom lane


[prev in list] [next in list] [prev in thread] [next in thread]