INDEX does not target query

SQL++
1

Hello,

I’m trying to build an index to target the following query:

SELECT 
DISTINCT 'Mission::' || submission.missionId as missionIds
FROM brandbassador_dev submission 
WHERE submission.docType="Submission"
AND (status = "request" OR offer IS NOT MISSING)
LIMIT 10

I’ve built the following index:

CREATE INDEX `idx_submission` ON `brandbassador_dev`(status, offer, missionId)
WHERE docType="Submission" AND ((status ="request") OR (offer IS NOT MISSING))

but it does not appear to target my query. Even if I use USE INDEX to force the index to be used, it defaults to the primary index. Am I missing something here? I’m using Couchbase Community DP (4.6). Thanks!

2

How about built index

CREATE INDEX `idx_submission_status` ON `brandbassador_dev`(status, missionId)
WHERE docType="Submission" AND (status ="request");

and

CREATE INDEX `idx_submission_offer` ON `brandbassador_dev`(offer, missionId)
WHERE docType="Submission" AND (offer IS NOT MISSING);

and run the following N1QL

SELECT 
 submission.missionId as missionIds
FROM brandbassador_dev submission
WHERE submission.docType = "Submission"
  AND submission.status = "request"
UNION
SELECT 
 submission.missionId as missionIds
FROM brandbassador_dev submission
WHERE submission.docType = "Submission"
  AND submission.offer IS NOT MISSING
LIMIT 10
3

Thanks for the prompt reply! Maybe it’s best if I present the whole use case:

SELECT 
DISTINCT 'Mission::' || submission.missionId as missionIds,
SUM(CASE WHEN submission.status = 'request' OR submission.offer IS NOT MISSING THEN 1 ELSE 0 END) AS requests,
    SUM(CASE WHEN submission.status = 'pending' THEN 1 ELSE 0 END) AS pending,
FROM brandbassador_dev submission 
WHERE submission.docType="Submission"
AND (status IN ["request", "pending"] OR offer IS NOT MISSING) 
LIMIT 10

This is the query I wanted to run; am I still able to do it with UNION?

4

please post the whole N1QL.
and yes,may be you can query data with UNION the data and then aggregation it.

5

Only index possible this query is
CREATE INDEX idx_submission ON brandbassador_dev(docType, status, offer, missionId);

Note: predicate is converted to disjunctive normal form
(A OR B) AND C => (A AND C) OR (B AND C)

To pick the index leading key of the index needs to be present in each OR clause

1 Like
6

@vsr1, I see. Regarding your note, wouldn’t it be (A OR B OR C) ?

7

(A OR B) AND C => (A AND C) OR (B AND C) is right

8

I meant that isn’t this

AND (status IN ["request", "pending"] OR offer IS NOT MISSING)

the same as this?

AND (status ="request" OR status="pending" OR offer IS NOT MISSING)
9

This is correct. Then you have AND on top that goes by post 6

10

I see what you mean now, thank you for your help!!

11

Hello again,

I have a different query now, but the problem is the same. I have this query:

SELECT RAW submission.docId
FROM brandbassador_dev submission
WHERE submission.docType="Submission"
AND  submission.network IS NOT MISSING 
AND submission.status IN  ["approved", "evaluating", "pending"] 
AND  submission.response IS NOT MISSING

I’ve tried to create variations of an index that also covers docId, but the query is not targeted, even with USE INDEX:

CREATE INDEX idx_submission_network_dev ON brandbassador_dev(docId, status) WHERE docType="Submission" AND network IS NOT MISSING AND response IS NOT MISSING

CREATE INDEX idx_submission_network_dev ON brandbassador_dev(docId, status, network, response) WHERE docType="Submission" AND network IS NOT MISSING AND response IS NOT MISSING

Am I missing something here?

12

@manusyone, Index selection depends on predicate only (i.e leading index key needs to be present in predicate).

CREATE INDEX idx_submission_network_dev ON brandbassador_dev (status, netwrok, response, docId) WHERE docType="Submission";

13

The index you’ve provided did work, but I didn’t understand what you meant by “(i.e leading index key needs to be present in predicate)”. Could you give me an example?

Why did this index not work?

CREATE INDEX idx_submission_network_dev ON brandbassador_dev(status, network, response, docId) WHERE docType="Submission" AND network IS NOT MISSING AND response IS NOT MISSING
14

In post 11 the index is different. The leading key is docId and no predicate on the docId. That is why index is not selected.
FYI: Index uses b-tree, you can only scan b-tree when leading key is present.

The following index will select and works. I have removed the extra conditions in index where clause whcih unnessary and added more index keys make query covered.

15

I understand now, thank you for your help!