Query analyzer doesn't use index specified by "use index" statement

[icy44]

does anyone experience that even with “use index” statement specified in the select query, couchbase query analyzer still doesn’t chose that index? i am using couchbase 5.5.6. thanks in advance.

[vsr1]

Hi @icy44 ,

USE INDEX is hint. If the specified index qualifies (Based on query predicate it able to get results with out false negatives) it uses. Other wise hint will be ignored and choose different index.

If you can provide query and index will able to tell why it is not honored.

[icy44]

@vsr1 Thanks for responding so quickly. here is the index and query that i tried to use.

CREATE INDEX `idxx_c_pts` ON `catalog`(
`form`,
`username`,
(meta().`id`),
(`pts`),
(distinct (array [(`pt`.`ptId`), (`pt`.`enabled`)] for `pt` in (`pts`) when ((`pts`).`enabled`) end)),
ifmissingornull((`deleted_at`), 0),
WHERE (`form` = "creative")


SELECT 
  meta().id
FROM 
  catalog  use index (idxx_c_pts) 
WHERE 
  form = 'creative' 
  AND meta().id IS NOT MISSING
  AND username IS NOT MISSING
  AND pts IS NOT MISSING
  AND ANY AND EVERY v in pts SATISFIES v.ptid = 4 AND v.enabled = true END 
  AND ifmissingornull(deleted_at, 0) = 0 
ORDER BY form, username, meta().id  
LIMIT 10

when i ran it, query analyzer used another index, which is similar to the one i wanted to use with the query. this is the other index:

CREATE INDEX `idxx_u_pts` ON `catalog`(
`username`,
(distinct (array [(`pt`.`ptId`), (`pt`.`enabled`)] for `pt` in (`pts`) when ((`pts`).`enabled`) end)),
(`pts`),
ifmissingornull((`deleted_at`), 0),
) WHERE (`form` = "creative") 

thank you for any insight.

[vsr1]

Hi @icy44 ,

idxx_c_pts leading index key is from is not part of query predicate. Couchbase indexes when leading index key is not part of query predicate. It will not qualify due to index doesn’t keep item when leading key is MISSING in the document.
By any change do u have typo form vs from

SELECT meta().id
FROM catalog
WHERE form = 'creative'
      AND username IS NOT MISSING
      AND ANY AND EVERY v IN pts SATISFIES [v.enabled, v.ptid] = [true, 4] END
      AND IFMISSINGORNULL(deleted_at, 0) = 0
ORDER BY form, username, meta().id
LIMIT 10

CREATE INDEX ix1 ON catalog(IFMISSINGORNULL(deleted_at, 0), DISTINCT ARRAY [v.enabled, v.ptid] FOR v IN pts END, username)
WHERE `form` = "creative";

Check out do u want ANY AND EVERY (i.e. every element of array has same value)

ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY

Range predicates (ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY) allow you to test a boolean condition over the elements or attributes of a collection or object(s). They each evaluate to a boolean value.

ANY or SOME is TRUE if the collection is non-empty and at least one element matches.

EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.

ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.

If you don’t need ANY AND EVERY, only ANY use following query which covers and uses index order too.

SELECT meta().id
FROM catalog
WHERE form = 'creative'
      AND username IS NOT MISSING
      AND ANY  v IN pts SATISFIES [v.enabled, v.ptid] = [true, 4] END
      AND IFMISSINGORNULL(deleted_at, 0) = 0
ORDER BY form, username, meta().id
LIMIT 10

CREATE INDEX ix1 ON catalog(IFMISSINGORNULL(deleted_at, 0), DISTINCT ARRAY [v.enabled, v.ptid] FOR v IN pts END, username, META().id)
WHERE `form` = "creative";

If you always do IFMISSINGORNULL(deleted_at, 0) = 0 remove from index keys and add to index WHERE as AND IFMISSINGORNULL(deleted_at, 0) = 0

https://index-advisor.couchbase.com/indexadvisor/#1

[icy44]

hi @vsr1 ,

thank you so much for the help and point out my typo! such silly mistake.

for the index you mentioned, how would the indexed data looked like? i always curious about that. will it be like as below? does the order of the index keys matter when i use them in order-by clause?

0, array_data, username_1, meta_id_1
0, array_data, username_2, meta_id_2
0, array_data, username_3, meta_id_3

CREATE INDEX ix1 ON catalog(IFMISSINGORNULL(deleted_at, 0), DISTINCT ARRAY [v.enabled, v.ptid] FOR v IN pts END, username, META().id)
WHERE `form` = "creative";

[vsr1]

Hi @icy44 ,

You are almost right. Index is uses skip list tree ( similar to b-tree ).

Also it is array Index. Each array element make separate entry in the index. (i.e. If u have one document and Array has 10 elements and 4 unique array keys, index will have 4 elements for this document)

Try see indexed data (run on indexer node)

/opt/couchbase/bin/cbindex -auth user:pass -type scanAll -bucket default -index ix1

Normally index keys must be based on predicates. If query has ORDER BY and query order by expressions matches with index keys order query can use index order and avoid sort. During that process planner will do some optimization. Example if query predicate has equality predicate and not present in query order by it can be assume as if present.

Create the Right Index, Get the Right Performance.

Database Pagination: Using OFFSET and Keyset in N1QL

Also your index has ARRAY index with 2 elements checkout explanation here

Creating a covered index with array, how to access array elements in select?

Hi, I’m trying to create a covered index using some data from an object, but with unknown key names. I’m not able to figure out how to get the query planner match and get the data back. This is example data (insert multiple times with different key names if you want): INSERT INTO `test-bucket` ( KEY, VALUE ) VALUES ( "k001", { "id": "1da0c06c-b9af-404c-ab51-86f1b00f7c0e", "stuff": { "25443790-6ab9-4374-90f2-aa927b066ae8": { "thing": "one", "nested": { "foo": "ba…

Composite index from an array of objects

Hello, I have a bucket with documents like this: { "fd": [{ "ac": "AF", "d": 30, "fn": 1096, "ym": 1911 }, { "ac": "AF", "d": 9, "fn": 1053, "ym": 2009 }, { "ac": "BA", "d": 29, "fn": 1002, "ym": 2006 }, { "ac": "QR", "d": 6, "fn": 1764, "ym": 2011 } ], "ts": 1572258925.187879, "yld": [ "PEKAMS", "CDGORY" ] } I am trying to index, as a composite index the following fields: fd.ac, fd.ym, fd.d, i.e. I need to be able to perform qu…

[icy44]

hi @vsr1 ,

those are very helpful information. thank you. i have one more question regarding array index. i thought i need to include the original array as one of the index keys, as below, is the pts really needed in there? is there any benefits to have it in there?

example:

CREATE INDEX ix1 ON catalog(
IFMISSINGORNULL(deleted_at, 0), 
DISTINCT ARRAY [v.enabled, v.ptid] FOR v IN pts END, 
pts,
username,
 META().id)
WHERE `form` = "creative";

[vsr1]

You can’t push predicates on whole array. Whole array needed if need to cover. That will be last resort. It also depends on your use case. If array included, it repeats each key. The index size is grows. If array has less elements and each element size is small it benefit. If not in can negative impact, in that fetch is better. If you need to include array for covering keep that right most index key. (keys that used for covering projection, arrays (i.e. non predicates, order by)) should move right most in index, Checkout Rule #11 in Create right index blog)

If you are using ANY and based on above case you no need whole array (Array Indexing | Couchbase Docs). If you need ANY AND EVERY and want to cover include at the end.

My recommendation is create dummy bucket with out data create index and do EXPLAIN and take look repeat different index and repeat see.

Also try use Index Advisor link it recommends covering, non covering index. Then adjust the index with Index Condition. Above link doesn’t have data it may not recommend index WHERE clause. If you run on real cluster in EE it recommends based on data.