Select query with sorting performance advice

SQL++
,
1

Hello, we are executing a select query via N1QL over a very large data set. We are looking to fetch the last 10 records.
The query is taking much too long, could you please advice on improving the query or the index?
(Assuming we have to select all fields and can’t use a covering index :slight_smile: )

Many thanks in advance!

SELECT * FROM conversationWHERE (type = 'leg' AND application_id = 'bbf7b41e-9a87-471a-8627-98c6cfeb5ae5' ) ORDER BYtimestamp.start DESC LIMIT 10 OFFSET 0

The used index (leg_application_idx3):
Definition: CREATE INDEX leg_application_idx3ONconversation(type,application_id) WHERE (type = "leg")

We are using couchbase 4.5.

Execution plan:

[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “leg_application_idx3”,
“index_id”: “79b31bc04629774f”,
“keyspace”: “conversation”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“"leg"”,
“successor("bbf7b41e-9a87-471a-8627-98c6cfeb5ae5")”
],
“Inclusion”: 1,
“Low”: [
“"leg"”,
“"bbf7b41e-9a87-471a-8627-98c6cfeb5ae5"”
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“keyspace”: “conversation”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “(((conversation.type) = "leg") and ((conversation.application_id) = "bbf7b41e-9a87-471a-8627-98c6cfeb5ae5"))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
}
]
}
}
]
},
{
“#operator”: “Order”,
“limit”: “10”,
“offset”: “0”,
“sort_terms”: [
{
“desc”: true,
“expr”: “((conversation.timestamp).start)”
}
]
},
{
“#operator”: “Offset”,
“expr”: “0”
},
{
“#operator”: “Limit”,
“expr”: “10”
},
{
“#operator”: “FinalProject”
}
]
},
“text”: “SELECT * FROM conversation WHERE (type = ‘leg’ AND application_id = ‘bbf7b41e-9a87-471a-8627-98c6cfeb5ae5’) ORDER BY timestamp.start DESC LIMIT 10 OFFSET 0”
}
]

2

Is it possible post the sample document that contain timestamp.start

Try use negative index based on the timestamp.start, If it is IS0-8601 convert it to MILLIS and use negative index like below.

CREATE INDEX  ix1 ON conversation(application_id, -MILLIS(timestamp.start)) WHERE (type= "leg");

SELECT * FROM conversation WHERE type = 'leg' AND application_id = 'bbf7b41e-9a87-471a-8627-98c6cfeb5ae5' ORDER BY -MILLIS(timestamp.start)  LIMIT 10 OFFSET 0;

Checkout the EXPLAIN and if you see order operator at the end try the following query

SELECT * FROM conversation WHERE type = 'leg' AND 
application_id = 'bbf7b41e-9a87-471a-8627-98c6cfeb5ae5' 
ORDER BY application_id, -MILLIS(timestamp.start)  LIMIT 10 OFFSET 0;
3

Hi @vsr1, thank you for your reply!
The document looks like this, so I am guessing the timestamp.start is sorted as a string.

{
“type”: “leg”,
“leg_id”: “zzzzzzzzz”,
“leg_type”: “phone”,
“application_id”: “xxxxxxxxx”,
“mixer_to_member”: {
“aaaaaaaaaa”: “sssssssssss”
},
“last_member”: “ssssssssssss”,
“last_mixer”: “0ca74d57-d50a-4d57”,
“last_conversation”: “CON-0fc967b4-56da”,
“status”: “completed”,
“from”: {
“type”: “phone”,
“number”: “15344234”
},
“to”: {
“type”: “phone”,
“number”: “111111111”
},
“direction”: “outbound”,
“rate”: “0.10000000”,
“price”: “0.05333333”,
“duration”: “32”,
“network”: “XXXXX”,
“timestamp”: {
“start”: “2017-08-23T14:36:06.000Z”,
“end”: “2017-08-23T14:36:38.000Z”
},
“_preventStatusUpdate”: true
}

4

The Index and query that previous post should work. If not post the EXPLAIN output of the query.