Re: [DISCUSS] Index selection syntax for CASSANDRA-18112

[guo Maxwell]

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

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  于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  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 
>> >
>> > 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 
>> >
>> > This could be helpful in scenarios where multiple indexes exist on a
>> > column and was the primary motivation for CASSANDRA-18112.
>> >
>> > Thoughts?
>>
>

Re: [DISCUSS] Index selection syntax for CASSANDRA-18112

[guo Maxwell]

Wrong typing, it is DML not DDL ~~~😅

guo Maxwell  于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
> 
> 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  于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  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 
>>> >
>>> > 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 
>>> >
>>> > This could be helpful in scenarios where multiple indexes exist on a
>>> > column and was the primary motivation for CASSANDRA-18112.
>>> >
>>> > Thoughts?
>>>
>>