N1QL : use view index works?

SQL++
1

Hello,

I try to read all my documents with a kind of pagination.

To do this, i create a index backed by a view like this :

create index products_updated on products(_id, updated) USING VIEW;

My document content is something like this :

{
_id : 10001,
updated : 123456,
…
}

When i wan to query something like this :
select meta().id from products USE INDEX (products_updated USING VIEW) where updated = 123456 order by _id offset 0 limit 10;

I does not seem to use my index products_updated but use the primary index …
Did i do something wrong ? Should i delete the primary index ?

Couchbase version : 4.0.0 community edition

Here is the explain plan :
[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “products_index”,
“keyspace”: “products”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “products”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((products.updated) = 123456)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(meta(products).id)”
}
]
}
]
}
}
]
},
{
"#operator": “Order”,
“sort_terms”: [
{
“expr”: “(products._id)”
}
]
},
{
"#operator": “Offset”,
“expr”: “0”
},
{
"#operator": “Limit”,
“expr”: “10”
},
{
"#operator": “FinalProject”
}
]
}
]

And here are my indexes :
SELECT * FROM system:indexes;

[
{
“indexes”: {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “5b90695d3f4d63f2”,
“index_key”: [],
“is_primary”: true,
“keyspace_id”: “products”,
“name”: “products_index”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
},
{
“indexes”: {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “products_updated”,
“index_key”: [
"_id",
"updated"
],
“keyspace_id”: “products”,
“name”: “products_updated”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “view”
}
}
]

2

You must use all the prefixing index keys in the where-clause. In this example, the index-key _id occurs first, before the 2nd index-key updated. Try this query:

select meta().id from products USE INDEX (products_updated USING VIEW) where _id IS NOT MISSING AND updated = 123456 order by _id offset 0 limit 10;

hth,
-Prasad

3

Hi @prasad,

Thank you for your answer.
It works when i add the clause that you suggest ;D, i only add this field in my index for sorting.

In my data the _id is always provided and it’s probably why i make that mistake.
Maybe this clause should be add in the index documentation section.

4

sure… In fact, we are updating the N1QL docs…