Couchbase n1ql pagination so slow

SQL++
, ,
1

Hi, I try to implement pagination in couchbase. But my query with indexes is slow. It takes almost 3 min to run query.
I want to get distinct threadId count which contains in displayTo. How can i create right index for that query ?

select distinct raw(threadId) from messages WHERE 10 IN displayTo
group by threadId, createdDate
order by createdDate desc
limit 20 OFFSET 0

Document

{

“threadId”: “1”,
“senderId”: 10,
“displayTo”: [
10,
20
],
“createdDate”: 1566589725671
}

{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“index”: “idx_displayTo_senderId”,
“index_id”: “944f7efe923412ee”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “messages”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “messages”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(1313 in (messages.displayTo))”
},
{
“#operator”: “InitialGroup”,
“aggregates”: ,
“group_keys”: [
“(messages.threadId)”,
“(messages.createdDate)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: ,
“group_keys”: [
“(messages.threadId)”,
“(messages.createdDate)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: ,
“group_keys”: [
“(messages.threadId)”,
“(messages.createdDate)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“distinct”: true,
“raw”: true,
“result_terms”: [
{
“expr”: “(messages.threadId)”
}
]
},
{
“#operator”: “Distinct”
}
]
}
},
{
“#operator”: “Distinct”
}
]
},
{
“#operator”: “Order”,
“limit”: “20”,
“sort_terms”: [
{
“desc”: true,
“expr”: “(messages.createdDate)”
}
]
},
{
“#operator”: “Limit”,
“expr”: “20”
},
{
“#operator”: “FinalProject”
}
]
},
“text”: “select distinct raw(threadId) from messages WHERE 10 IN displayTo \n group by threadId, createdDate\n order by createdDate desc\n limit 20 OFFSET 0”
}

Any help would be appreciated

2 
CREATE INDEX ix1 ON messages(DISTINCT displayTo, threadId, createdDate DESC);

SELECT RAW m.threadId
FROM messages AS m
WHERE ANY v IN m.displayTo SATISFIES v = 10 END
GROUP BY m.threadId
LETTING  od = MAX(m.createdDate)
ORDER BY od DESC
OFFSET 0 
LIMIT 20;
1 Like
3

Thank you so much. You saved my whole week

4

@mesut_ozen,

Could you post the EXPLAIN for the new query & index.

I’m trying to understand how the below query is using the index.

CREATE INDEX ix1 ON messages(DISTINCT displayTo, threadId, createdDate DESC);

emit([10,20],“1”, 1566589725671)

vs

CREATE INDEX ix1_dt_front ON messages(createdDate DESC , threadId, DISTINCT displayTo,);

emit([1566589725671,“1”,[10,20],)

I would think that since the ORDER by is createdDate you would want to scan those parts in order and then find threadID and displays.

@vsr1, Could you explain logic the query engine uses to process the index

…
WHERE ANY v IN m.displayTo SATISFIES v = 10 END
GROUP BY m.threadId
LETTING od = MAX(m.createdDate)
ORDER BY od DESC
OFFSET 0
LIMIT 20;

5

@househippo, Checkout https://blog.couchbase.com/create-right-index-get-right-performance/ for explanation

Index scan produces all the items that displayTo has 10 and does group and aggregation and pagination. In this case query can’t use index order. Due to GROUP BY not on leading index key.
Also ix1_dt_front will not qualify because index selection is based on query predicate and leading index key is not part of the query predicate.

6

@vsr1,

I copy pasted doc and index to my local machine the index I created was not helpful and its b/c whats in the WHERE in the query. In the query it only really looking for: "WHERE ANY v IN m.displayTo SATISFIES v = 10 END"

The index:

CREATE INDEX ix1 ON messages(DISTINCT displayTo, threadId, createdDate DESC);

has what the WHERE is looking for in the front , and threadID , createdDate come along for the ride to finish the query processing.

The index I created is a good one if you asked find all the docs that WHERE created today or some time range.