[
https://issues.apache.org/jira/browse/CASSANDRA-10214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14718615#comment-14718615
]
Sylvain Lebresne commented on CASSANDRA-10214:
----------------------------------------------
bq. When do we expect users to do a better job of picking an index than the
query planner?
Which query planner? :)
More seriously, our heuristic to choose between 2 indexes isn't terribly
advanced, I wouldn't be very surprised that user that knows their data a bit
could beat it on some query (in fact, it's kind of hard to even evaluate how
good our heuristic does since there is not way to override it). It's absolutely
an advanced feature for advanced users, but it's easy enough to provide and
intuitive enough for users that I don't see a huge drawback.
Further, to be honest, I'm personally almost mostly interested by the {{USING
NO INDEX}} form. Because as I said, if any expression of your query is indexed,
then there is no proper heuristic, the index is _always_ picked even in case
where filtering would be more efficient. As I said, for single partition query,
it's not hard to know when doing filtering would win (that is, assuming you
know a bit the data model that is). We could try to improve our default
heuristic there I guess, but well, heuristics are always just that, heuristics.
Anyway, I don't think it's urgent or absolutely fundamental, but I do think
it's a reasonable advanced tool to have (which can double as a pedagogical too
btw), a nice to have.
> Enable index selection to be overridden on a per query basis
> ------------------------------------------------------------
>
> Key: CASSANDRA-10214
> URL: https://issues.apache.org/jira/browse/CASSANDRA-10214
> Project: Cassandra
> Issue Type: New Feature
> Reporter: Sam Tunnicliffe
> Fix For: 3.x
>
>
> (Broken out of CASSANDRA-10124)
> We could add a {{USING INDEX <indexname>}} clause to {{SELECT}} syntax to
> force the choice of index and bypass the usual index selection mechanism.
> {code}
> CREATE TABLE ks.t1(k int, v1 int, v2 int, PRIMARY KEY (k));
> CREATE INDEX v1_idx ON ks.t1(v1);
> CREATE INDEX v2_idx ON ks.t1(v2);
> CREATE CUSTOM INDEX v1_v2_idx ON ks.t1(v1, v2) USING
> 'com.foo.bar.CustomMultiColumnIndex';
> # Override internal index selection mechanism
> SELECT * FROM ks.t1 WHERE v1=0 AND v2=0 USING INDEX v1_idx;
> SELECT * FROM ks.t1 WHERE v1=0 AND v2=0 USING INDEX v2_idx;
> SELECT * FROM ks.t1 WHERE v1=0 AND v2=0 USING INDEX v1_v2_idx;
> {code}
> This is in some ways similar to [index
> hinting|http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#CHDJDIAH]
> in Oracle.
> edit: fixed typo's (missing INDEX in the USING clauses)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
