A bug with covering indexes

SQL++
1

It seems like there’s a bug with covering indexes (Couchbase 4.1).

I can create a covering index using the following statement
CREATE INDEX phone_id ON users(phone,meta().id) USING GSI;

However, the following statement (just switching meta().id and phone) doesn’t work:
CREATE INDEX phone_id ON users(meta().id,phone) USING GSI;

Is this a known bug?
Thanks!

2

In a simple case, changing the order doesn’t make a different as long as you’re using both keys in your predicate.
What query are you issuing after you create the second index?

cbq> create index ipm on beer-sample(name,meta().id);

cbq> explain select name, meta().id from beer-sample where name = ‘a’ and meta().id = “a1”;
{
“requestID”: “dc528609-db9e-43be-8532-fbed77bdc8aa”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“covers”: [
“cover((meta(beer-sample).id))”,
“cover((beer-sample.name))”,
“cover((meta(beer-sample).id))”
],

drop index beer-sample.ipm;
create index ipm on beer-sample(meta().id,name);

cbq> explain select name, meta().id from beer-sample where name = ‘a’ and meta().id = “a1”;
{
“requestID”: “768817fd-8f80-43f9-bc6e-1650fd6906e4”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“covers”: [
“cover((meta(beer-sample).id))”,
“cover((meta(beer-sample).id))”,
“cover((beer-sample.name))”
],

1 Like
3

My predicate only contains a condition on phone and not on id. So this would require phone to appear first in the list?

Suppose I have
create index sample_id on bucket(expr1, expr2, expr3, ..., exprn)
then the roles of expr1, expr2 etc. are not equal? Namely, for a query to make use of this index, expr1 has to appear in the predicate?

4

Correct. For a given list of N keys (1, 2, 3, …N) in the index, the query has to use FIRST K (1 or 1 and 2 or 1 and 2 and 3, etc) in the predicate list for the optimizer to consider using it.

5

That’s great. Thank you!