Thread: BUG #17110: [FEATURE REQUEST] Log all plans for a query instead of just showing the most optimal plan
BUG #17110: [FEATURE REQUEST] Log all plans for a query instead of just showing the most optimal plan
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 17110 Logged by: masterskipper Email address: abhiroj95@gmail.com PostgreSQL version: 11.10 Operating system: debian9 Description: Hi, Recently, we had to optimize a query that was behaving differently on different databases (because data distribution and selectivity on each DB were different). The problem is that it is very very difficult today to understand why is the DB selecting the query plan it shows (and not other plans). For e.g. why prefer sequential scan over index scan? what were the costs that it calculated which made it prefer sequential scan? Investigating such issues will become way more user friendly rather than doing a guess work in and out. I believe this is a valid request and would request you to please consider some time to fix it. Many Thanks, Abhiroj
Re: BUG #17110: [FEATURE REQUEST] Log all plans for a query instead of just showing the most optimal plan
From
"David G. Johnston"
Date:
On Thursday, July 15, 2021, PG Bug reporting form <noreply@postgresql.org> wrote:
Investigating such issues will become way more user friendly rather than
doing a guess work in and out. I believe this is a valid request and would
request you to please consider some time to fix it.
Feature requests are preferred on the -general list, leaving this one for true bug reports.
As for this specific request it doesn’t seem like a good trade-off for the planner to fully compute plans it knows aren’t going to ultimately be selected to actually run. So while putting an opt-in mechanism in place is doable (e.g., maybe an explain all command) the rework necessary to actually produce the relevant information is daunting; which seems to be why the periodic requests for a such a feature don’t really get serious consideration. That, and the general belief that the query writer isn’t expected (or mostly given the tools) to influence the chosen query plan directly so such information would be difficult to act on.
In short, I agree the need is real but value/cost proposition for working on such a feature seems very unfavorable.
David J.