On 17 May 2018 at 10:37, Tom Lane <t...@sss.pgh.pa.us> wrote: > Thomas Munro <thomas.mu...@enterprisedb.com> writes: >> IIUC in DB2 (the clear winner at join elimination in the article you >> mentioned), you get these sorts of things by default (optimisation >> level 5 includes it), but not if you SET CURRENT QUERY OPTIMIZATION = >> 3 as many articles recommend for OLTP work. I think it's interesting >> that they provide that knob rather than something automatic, and >> interesting that there is one linear knob to classify your workload >> rather than N knobs for N optimisations. > > There's a lot to be said for that type of approach, as opposed to trying > to drive it off some necessarily-very-inexact preliminary estimate of > query cost. For example, the mere fact that you're joining giant tables > doesn't in itself suggest that extra efforts in query optimization will be > repaid. (If anything, it seems more likely that the user would've avoided > silliness like useless self-joins in such a case.) > > A different line of thought is that, to me, the most intellectually > defensible rationale for efforts like const-simplification and join > removal is that opportunities for those things can arise after view > expansion, even in queries where the original query text didn't seem > to contain anything extraneous. (Robert and Andres alluded to this > upthread, but not very clearly.) So maybe we could track how much > the query got changed during rewriting, and use that to drive the > planner's decisions about how hard to work later on. But I'm not > very sure that this'd be superior to having a user-visible knob.
This seems like a good line of thought. Perhaps a knob is a good first step, then maybe having the ability to set that knob to "automatic" is something to aspire for later. I don't think Alexander should work on this as part of this patch though. Perhaps we can re-evaluate when Alexander posts some planner benchmarks from the patch. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
