I am personally -1 against the approach taken by DS for optimizing SAI queries. I consider it as a quick fix rather than a proper long term solution. The issue with this approach is that it splits the optimisation logic of queries. With one part of the optimization happening on the coordinator while the other is performed on the replicas. By doing so it will make the introduction of features such as global indexes much harder as those features will need to be plugged to the optimization logic. I strongly believe that the proper solution for query optimization is what was proposed in CEP-39: a cost based query optimizer located on the coordinator. Unfortunately do not have the time to work on it.
Le lun. 23 déc. 2024 à 07:11, guo Maxwell <cclive1...@gmail.com> a écrit : > Wrong typing, it is DML not DDL ~~~😅 > > guo Maxwell <cclive1...@gmail.com> 于2024年12月23日周一 11:56写道： > >> Thanks Caleb for bringing it up. >> >> When I opened this issue, I originally wanted to propose the following >> syntax: >> 1、When we want to force the selection of certain indexes: >> SELECT ...USE INDEX (ks.idx1, ks.idx2....) ... FROM ...WHERE ... >> or >> SELECT .../*+ USE INDEX (ks.idx1, ks.idx2....)*/ ... FROM ...WHERE ... >> >> 2、When we want to ignore the selection of some indexes: >> SELECT ...IGNORE INDEX (ks.idx1, ks.idx2....) ... FROM ... WHERE ... >> or >> SELECT .../*+ IGNORE INDEX (ks.idx1, ks.idx2....) */ ... FROM ... WHERE >> ... >> >> And In fact, what I originally wanted to do was to provide a hint >> function >> <https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-overview> >> similar to MySQL, which can push down some rules dynamically. >> The work to provide a hint for DDL needs to be accomplished in many >> steps, CASSANDRA-18112 for index hint is part of it, maybe the first step. >> >> >> Ekaterina Dimitrova <e.dimitr...@gmail.com> 于2024年12月21日周六 22:27写道： >> >>> Naming is hard but to me providing what Caleb mentioned through >>> something like WITH OPTIONS sounds reasonable. Thanks for bringing it >>> up. >>> >>> On Sat, 21 Dec 2024 at 2:46, Joel Shepherd <sheph...@amazon.com> wrote: >>> >>>> WITH INDEX (or something equivalent) seems really useful. >>>> >>>> Less opinionated on the specific syntax, but I think there is a lot of >>>> value in the form of predictable, controllable performance, in giving >>>> developers more direct control over query execution, whether that's >>>> index selection or even lower-level decisions. If you've experienced >>>> the >>>> thrill of operating a database with a cost-based planner that abruptly >>>> selects a new, sub-optimal plan due to a change in statistics or >>>> configuration, you'll appreciate language features that yield some >>>> planning control back to you. It does increase the burden on the >>>> developer to understand how best to execute the query, but it makes >>>> their intent much more obvious, and easier to adjust as the system >>>> changes. >>>> >>>> -- Joel. >>>> >>>> On 12/20/2024 12:28 PM, Caleb Rackliffe wrote: >>>> > Some of your are probably familiar with work in the DS fork to >>>> improve >>>> > the selection of indexes for SAI queries in >>>> > >>>> https://github.com/datastax/cassandra/commit/eeb33dd62b9b74ecf818a263fd73dbe6714b0df0#diff-2830028723b7f4af5ec7450fae2c206aeefa5a2c3455eff6f4a0734a85cb5424. >>>> >>>> > >>>> > >>>> > While I'm eagerly anticipating working on that in the new year, I'm >>>> > also wondering whether we think some simple CQL extensions to >>>> manually >>>> > control index selection would be helpful. Maxwell proposed this a >>>> > while back in CASSANDRA-18112, and I'd like to propose a syntax: >>>> > >>>> > >>>> > ex. Do not use the specified index during the query. >>>> > >>>> > SELECT ... FROM ... WHERE ... WITHOUT INDEX <ks.idx> >>>> > >>>> > This could be helpful for intersection queries where one of the >>>> > provided clauses is not very selective and could simply be handled >>>> via >>>> > post-filtering. >>>> > >>>> > ex. Require the specified index to be used. >>>> > >>>> > SELECT ... FROM ... WHERE ... WITH INDEX <ks.idx> >>>> > >>>> > This could be helpful in scenarios where multiple indexes exist on a >>>> > column and was the primary motivation for CASSANDRA-18112. >>>> > >>>> > Thoughts? >>>> >>>
