NEST clause N1QL is very slow with “order by” clause

Hi,

Sorry for asking the same questions but my head hurts from trying to find a solution. :slight_smile:

My request here take more than 5 seconds to retrive 10 results when I add Order By clause. I tried to create compound indexes as idx_origin_class_metadata_name (origin, _class, metadata.name) or class_origin_metadata_name (_class, orgin, metadata.name), but nothing change.

SELECT t.metadata.name, META(t).id, ARRAY_STAR((ARRAY_STAR(p).metadata)).name AS breadcrumb 
FROM `bucket-data` AS t 
  NEST `bucket-data` AS p
  ON KEYS t.ancestorIds
WHERE t._class = "mypackage.Category" 
AND ( t.origin = "GLOBAL" AND t.metadata.configurations.GLOBAL.enabled = TRUE ) 
AND ARRAY_COUNT(t.productIds.GLOBAL) > 100
ORDER BY t.metadata.name ASC
LIMIT 10 OFFSET 0

The ancestorIds hold other categories IDs. My goal is to generate data with my current category and the names of its ancestors.

I don’t what’s going wrong!
Please help and thank you

If you need ORDER it needs to produce all qualified items and sort (even though you have LIMIT, OFFSET). Query engine can make certain optimizations based on index (If Query ORDER BY matches index order, avoid sort).

CREATE INDEX ix1 ON `bucket-data`(origin, metadata.configurations.GLOBAL.enabled,
                                  metadata.name, ARRAY_COUNT(productIds.GLOBAL), ancestorIds)
              WHERE _class = "mypackage.Category";

SELECT t.metadata.name, META(t).id, ARRAY_STAR((ARRAY_STAR(p).metadata)).name AS breadcrumb
FROM `bucket-data` AS t
  NEST `bucket-data` AS p
  ON KEYS t.ancestorIds
WHERE t._class = "mypackage.Category"
AND ( t.origin = "GLOBAL" AND t.metadata.configurations.GLOBAL.enabled = TRUE )
AND ARRAY_COUNT(t.productIds.GLOBAL) > 100
AND t.metadata.name IS NOT NULL
ORDER BY t.metadata.name ASC
LIMIT 10 OFFSET 0;

OR

SELECT t.metadata.name, META(t).id,
   (SELECT RAW p.metadata.name FROM `bucket-data` AS p USE KEYS t.ancestorIds) AS  breadcrumb
FROM `bucket-data` AS t
WHERE t._class = "mypackage.Category"
AND t.origin = "GLOBAL"
AND t.metadata.configurations.GLOBAL.enabled = TRUE
AND ARRAY_COUNT(t.productIds.GLOBAL) > 100
AND t.metadata.name IS NOT NULL
ORDER BY t.metadata.name ASC
LIMIT 10 OFFSET 0;

Check the following articles

https://blog.couchbase.com/create-right-index-get-right-performance/
https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/
https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/
https://blog.couchbase.com/author/keshav-murthy/

Thanks for your answer.
Assuming metadata.configurations and productIds are Map, do I have to create the index for each key?
ARRAY_STAR function works well for Array, is there a function for Map?

Thank u !!

There is no map in JSON. You mean OBJECT. give whole object or sub-object or construct new object

Sorry, Indeed I meant Object.
Do you suggest I give the whole object in the Index ?

You can, Predicate pushdown to indexer on Object may not happen. In query you need to Object key exactly same.
If Object is too big you can reconstruct object ( you need to use reconstructed object in query exactly to cover)

This is my new index

CREATE INDEX ix1 ON `bucket-data`(origin, metadata.configurations,
                                  metadata.name,  productIds, ancestorIds)
              WHERE _class = "mypackage.Category";

The execution time passed from 8s to 3 seconds.

Knowing that configurations and productIds objects can both have more than 30 keys. Their values often change. I thought it is not a best practice to use whole objects and arrays to build indexes.

{
   ...
   configurations:  {
     'GLOBAL': { enabled: true, order: 1 },
     'LOCAL 1' :  : { enabled: true, order: 1 },
      ...
   },
   productIds: {
       'GLOBAL': [1, 2, 3, ...],
       'LOCAL 1': [1, 2, 3, 4 ...],
        ...
   }
}

So what did you mean by reconstruct object? Did you mean I have to refactor my model schema or I can reconstruct by an existing way. Any examples?

I am very sorry to disturb you but I need to understand more.

Thank you a lot

Example : You want project configurations order when enabled true

Add this in index key and exact same expression must be present in the projection to cover.

ARRAY {v.order, v.name} FOR v IN configurations WHEN v.enabled = true END

Thank you a lot!

I will try to use OBJECT functions to find the good way to create the INDEX above.