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?
