Non matching index in N1QL

SQL++
, ,
1

I have an index:

I-1:

CREATE INDEX idx_combined_publisherId
ON lV1k6vmwVk (publisherTenantId.value, (DISTINCT ARRAY p.value FOR p IN publisherIds END))
WHERE doc = “ScreenGroup”;

Q-1:

EXPLAIN SELECT *, meta().cas
FROM lV1k6vmwVk
USE INDEX (idx_player_token)
WHERE ( publisherTenantId.value = “publisherTenantId_1”
OR ANY p IN publisherIds SATISFIES p.value = “publisherTenantId_1” END )
AND doc = “ScreenGroup”

But despite manualy indicating the index in the query, It still uses the primary index. Am I missing something?

{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “lV1k6vmwVk”,
“namespace”: “default”,
“using”: “gsi”
}

2

The index specified is not qualify for query due to OR predicate. Both parts of OR clause need to contain leading index key.

CREATE INDEX idx1 ON lV1k6vmwVk (DISTINCT ARRAY p.`value` FOR p IN publisherIds END) WHERE doc = "ScreenGroup";
CREATE INDEX idx2 ON lV1k6vmwVk (publisherTenantId.`value`) WHERE doc = "ScreenGroup";

4.6.2+

EXPLAIN SELECT *, meta().cas FROM lV1k6vmwVk
WHERE ( publisherTenantId.value = "publisherTenantId_1" AND doc = "ScreenGroup")
OR (ANY p IN publisherIds SATISFIES p.value = "publisherTenantId_1" END  AND doc = "ScreenGroup");

Pre 4.6.2

EXPLAIN
SELECT *, meta().cas FROM lV1k6vmwVk WHERE publisherTenantId.value = "publisherTenantId_1" AND doc = "ScreenGroup"
UNION
SELECT *, meta().cas FROM lV1k6vmwVk WHERE ANY p IN publisherIds SATISFIES p.value = "publisherTenantId_1" END  AND doc = "ScreenGroup";
1 Like
3

Hi @Rabzu,

When you have an OR clause, we can’t use the index because each side of OR clause has to be evaluated independently.

For this index, you can create two distinct indexes
i1: CREATE INDEX idx_combined_publisherId_1
ON lV1k6vmwVk (publisherTenantId.value)
WHERE doc = “ScreenGroup”;

I2: CREATE INDEX idx_combined_publisherId
ON lV1k6vmwVk (DISTINCT ARRAY p.value FOR p IN publisherIds END))
WHERE doc = “ScreenGroup”;

See @VSR1’s articles on index selection, optimization.

Also, Couchbase N1QL: Index Advisor | PPT

1 Like