N1ql indexing sequence

SQL++
1

Hi, I was searching about how the composite index sequence affect the performance on N1QL, but didn’t get an explanation on this.

I am using quite a lot of composite index (sometimes with array index).
What I observed was using the below 2 indexes:

  1. create index idx on bucket((key1),(key2),(key3),(distinct (arrayxforxin (array_key) end)))
  2. create index idx on bucket((distinct (arrayxforxin (array_key) end)),(key1),(key2),(key3))

(1) will take a few seconds to return the result while (2) will take <100ms to return the result.
I’ve tried altering the sequence of expressions in my query WHERE clause, but the result is the same.

May I know:

  1. Does this means: the sequence of keys in the query doesn’t affect the result, but the sequence of keys in index does
  2. Apart from array index, does the sequence of other keys affect the performance as well?
  3. How should we know the correct sequence when we construct an index/query, except by trying all the combinations each time we implement a new query?

FYI, I’m using 4.6.0-3573 Enterprise Edition.

Thanks a lot.

Index for the intersection of a sub array and a static array as a parameter
2

you can explain your N1QL to check the range in span is using the correct index with correct range.

3

Index key order makes difference, Recommended order will be equal, IN predicates first in leading keys, followed by less than/less than equal, followed by grater than/grater than equal fields.

1 Like
4

for example the following N1QL

SELECT resultKey
   FROM default
 WHERE equalKey == "value1"
       AND inKey IN ["value2","value3"]
       AND lessKey < "value4"
       AND graterKey > "value5"

the best index order should be

create index idx_4order on default(inKey, equalKey, lessKey, graterKey, resultKey)

how about the order of array index in index ?
and how should I choice index array key or create a array index for array key?

5

if key is array index key same rule applies based on SATISFIES condition.
FYI: Above rules makes query push as many predicates as possible to indexer (you can see in the spans). Also indexScans are range scans, will minimize the false positives.

1 Like
6

Thank you very much, it is very useful.
and if the N1QL include order key, should we put order key in leading keys?

7

It depends. Index Order used when there is single span and specific cases.
The following link gives some ideas.

8

ok,thank you very much.I will deep research.

9

@atom_yang, equalKeys before IN keys.

10

both will be coverScan,what is different?

11

the value of equalKeys that match predicate in the leading index will be the continuous in the same place ,that will speed up the indexScan.so equalKeys before IN keys is better.
Am I right?

12

@atom_yang, There are other cases that benefits for equalKeys before IN keys.
Example: predicate is equalKey = $1 AND inKey IN $2
This case equalKey can pushed to indexer and inKey will use whole range.
so equalKey before inKey more efficient.

Also IN clause can have many elements, In composite key If the number of spans exceed predefined limit to prune the spans, trailing index keys uses whole range. In those situations also equalKey before inKey more efficient.

13

Thank you for example.

I don’t understand this situation. can you give me a example more detailed?

14

This is extreme case.

CREATE INDEX ix1 on default(inKey,eualKey);
SELECT meta().id FROM default WHERE eualKey = "xyz"  AND inKey IN [1,2,3,....,8200];

In above case IN clause has more than 8192 entries, The spans will be more than 8192. In that case we prune the spans by reducing combinations backward from trailing end This makes complete index scan.

If the index is like below we can still push the equalKey to Indexer.

CREATE INDEX ix1 on default(eualKey,inKey);

15

I am still confused. and I just test above N1QL,
for

CREATE INDEX ix1 on default(inKey,eualKey);

the part of explain shows

              {
                "exact": true,
                "range": [
                  {
                    "high": "8198",
                    "inclusion": 3,
                    "low": "8198"
                  },
                  {
                    "high": "\"xyz\"",
                    "inclusion": 3,
                    "low": "\"xyz\""
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "8199",
                    "inclusion": 3,
                    "low": "8199"
                  },
                  {
                    "high": "\"xyz\"",
                    "inclusion": 3,
                    "low": "\"xyz\""
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "8200",
                    "inclusion": 3,
                    "low": "8200"
                  },
                  {
                    "high": "\"xyz\"",
                    "inclusion": 3,
                    "low": "\"xyz\""
                  }
                ]
              }
            ]

and for

CREATE INDEX ix1 on default(eualKey,inKey);

the part of explain shows

              {
                "exact": true,
                "range": [
                  {
                    "high": "\"xyz\"",
                    "inclusion": 3,
                    "low": "\"xyz\""
                  },
                  {
                    "high": "8199",
                    "inclusion": 3,
                    "low": "8199"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"xyz\"",
                    "inclusion": 3,
                    "low": "\"xyz\""
                  },
                  {
                    "high": "8200",
                    "inclusion": 3,
                    "low": "8200"
                  }
                ]
              }
            ]

nothing was pruned?

16

How many unique elements you have IN clause

17

8200 total.
notice: I am using CB 5.0.0-2194 Enterprise Edition (build-2194)
FYI

image.png1758×1192 142 KB

18

The intention is limit the spans to 8192. May be something slipped will take look and fix it. Thanks for pointing out. https://issues.couchbase.com/browse/MB-23564

19

btw, while run this N1QL, I will got the following warning

image.png1068×426 29.9 KB

other info

image.png1694×476 40.3 KB

image.png788×1322 81.4 KB

20

That is not related to query MOI index might used more RAM