On 2018-05-16 18:37:11 -0400, Tom Lane 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.)
For prepared statements we could also start making more expensive optimizations after the first execution, when we know how long the query took / how expensive it was (also, if we had a plan cache...). Greetings, Andres Freund