Thread: Showing alternative query planner plans with explain ?
Hey all !
I'm currently optimizing queries and indices on a relatively large dataset;
one of the frequent questions I seem to ask myself is why the planner chooses plan A over B.
Reading the docs, blogs, stack exchange posts, wiki, ... helps in trying to tinker with the query or indices in a way that either A will be discouraged, or B will be favoured, so I'd be more informed on why one was chosen over the other and which is empirically better for a given dataset.
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.
Is there any way to achieve the above ?
Thanks a ton,
Danny
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