I’m just saying the approach isn’t perfect. And it isn’t implemented. If you are able to change your application to use bind variables, that is superior in every way.
You mention “a quick recheck of cost function”. This is a very nice use case for multi-objective parametric query optimization[1], which is a very elegant extension to the query optimization and which I think will gradually become the standard approach. The idea would be to generate plans assuming that the selectivity of a condition is a parameter varies between 0 and 1, and figure out at planning time what is the selectivity value at which plan A ceases to be the optimal plan and plan B becomes the optimal plan. Julian [1] https://cacm.acm.org/magazines/2017/10/221322-multi-objective-parametric-query-optimization/abstract <https://cacm.acm.org/magazines/2017/10/221322-multi-objective-parametric-query-optimization/abstract> > On Sep 17, 2019, at 9:11 AM, Julian Feinauer <j.feina...@pragmaticminds.de> > wrote: > > Hi, > > this is a good point Julian. So an implementation should consider a > re-planning (possibly triggered by a quick recheck of cost function with the > given Literal values). But this should not be a general issue with the > approach, or? > > JulianF > > Am 16.09.19, 23:36 schrieb "Julian Hyde" <jh...@apache.org>: > > I found evidence that MSSQL[1] and Sybase ASE[2] do it. > > I agree, it's not a free lunch. For instance, if a column has a > non-uniform distribution, some values might be much more selective > than others, and it would be much better to know which value you are > dealing with at planning time, rather than execution time. > > Julian > > [1] > https://docs.microsoft.com/en-us/sql/relational-databases/performance/specify-query-parameterization-behavior-by-using-plan-guides?view=sql-server-2017 > > [2] > http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00743.1570/html/queryprocessing/BIIIBEJJ.htm > > On Mon, Sep 16, 2019 at 3:36 PM Stamatis Zampetakis <zabe...@gmail.com> > wrote: >> >> Out of curiosity does anybody know if popular DBMS (Postgres, Oracle, SQL >> Server, etc.) support "hoisting"? >> >> Performing it all the time does not seem a very good idea (constant >> reduction, histograms, and other optimization techniques would be >> impossible) >> while leaving its configuration to the end-user may not be a >> straightforward decision. >> >> On Sat, Sep 14, 2019 at 4:29 PM Julian Hyde <jhyde.apa...@gmail.com> wrote: >> >>> The idea of converting literals into bind variables is called “hoisting”. >>> We had the idea a while ago but have not implemented it. >>> >>> https://issues.apache.org/jira/browse/CALCITE-963 >>> >>> Until that feature is implemented, you will need to create bind variables >>> explicitly, and bind them before executing the query. >>> >>> Julian >>> >>>> On Sep 13, 2019, at 4:39 PM, Scott Reynolds <sdrreyno...@gmail.com> >>> wrote: >>>> >>>> Hi, >>>> >>>> Spent a bunch of time researching and staring at code today to understand >>>> the code compilation path within Calcite. I started down this path >>> because >>>> we noticed whenever we changed the `startDate` or `endDate` for the query >>>> it went through compilation process again. We expected it to use the >>>> previous classes `bind` it with the new RexLiterals. I was *hoping* the >>>> RexLiterals were passed into the `bind()` method but that does not appear >>>> to be the main goal of `DataContext` objects. >>>> >>>> We also found the trick Kylin did to improve their query compilation with >>>> prepared statements: >>>> https://issues.apache.org/jira/browse/KYLIN-3434 but PreparedStatement >>> is >>>> stateful and I don't believe a good way to solve this issue. >>>> >>>> I would like to propose a change to Calcite so that Filters are passed >>> into >>>> the `bind()` call alongside or within DataContext. This would allow the >>>> `EnumerableRel` implementations to reference the `Filters` as arguments. >>>> This -- I believe -- would cause any change to the filters to use >>>> the previously compiled class instead of generating a brand new one. >>>> >>>> I am emailing everyone on this list for two reasons: >>>> 1. Is this a bad idea ? >>>> 2. I don't have a design yet so would love any ideas. Should we stick >>> more >>>> stuff into `DataContext`? Should `EnumerableRel` have another method that >>>> is used to gather these RexLiterals? >>> > >
