N1QL Index and Sorting

SQL++
1

I am running into some performance issues and was wondering if there is a fix for this. I have a bucket which stores Email messages which come from MS Graph Db via Push and all works fine. But lets say i want to get the last 10 or 25 messages, i run a simple query like 2.5 sec for this seems a bit excessive specially on a 5K doc count

select createdDateTime from mail_store 
order by createdDateTime DESC
Limit 10

Here is my Index

CREATE INDEX prim_IDX_MAIL_1 ON mail_store(createdDateTime DESC)

And this is what the Plan looks like

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “4.033µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “6.801µs”,
“servTime”: “1.64663ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:mail_store”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “6.048µs”
},
“~children”: [
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “24.937µs”
},
“~children”: [
{
“#operator”: “PrimaryScan3”,
“#stats”: {
“#itemsOut”: 4859,
“#phaseSwitches”: 19439,
“execTime”: “32.138413ms”,
“kernTime”: “2.216832002s”,
“servTime”: “16.170855ms”
},
“index”: “undefined”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “mail_store”,
“namespace”: “default”,
“using”: “gsi”,
“#time_normal”: “00:00.0483”,
“#time_absolute”: 0.048309267999999995
},
{
“#operator”: “Fetch”,
“#stats”: {
“#itemsIn”: 4859,
“#itemsOut”: 4859,
“#phaseSwitches”: 20047,
“execTime”: “50.745391ms”,
“kernTime”: “19.106054ms”,
“servTime”: “2.292591669s”
},
“keyspace”: “mail_store”,
“namespace”: “default”,
“#time_normal”: “00:02.3433”,
“#time_absolute”: 2.34333706
},
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 4859,
“#itemsOut”: 4859,
“#phaseSwitches”: 19439,
“execTime”: “295.712859ms”,
“kernTime”: “2.067140932s”
},
“result_terms”: [
{
“expr”: “(mail_store.createdDateTime)”
}
],
“#time_normal”: “00:00.2957”,
“#time_absolute”: 0.295712859
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000024937000000000002
},
{
“#operator”: “Order”,
“#stats”: {
“#itemsIn”: 4859,
“#itemsOut”: 10,
“#phaseSwitches”: 9733,
“execTime”: “33.268546ms”,
“kernTime”: “2.329747561s”
},
“limit”: “10”,
“sort_terms”: [
{
“desc”: true,
“expr”: “(mail_store.createdDateTime)”
}
],
“#time_normal”: “00:00.0332”,
“#time_absolute”: 0.033268546
},
{
“#operator”: “Limit”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 21,
“execTime”: “7.589µs”
},
“expr”: “10”,
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000075890000000000005
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 31,
“execTime”: “13.519µs”,
“kernTime”: “229.175µs”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000013519
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000006048
},
“#time_normal”: “00:00.0016”,
“#time_absolute”: 0.0016534310000000001
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 43,
“execTime”: “10.202µs”,
“kernTime”: “2.364948012s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000010202
}
],
“~versions”: [
“2.0.0-N1QL”,
“6.0.0-1693-enterprise”
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000004033
}

2

You need leading index key as predicate and results will be in sub milli seconds.

  select createdDateTime from mail_store 
    WHERE createdDateTime IS NOT NULL
    order by createdDateTime DESC
    Limit 10
3

Yes that did the trick, now its down to elapsed: 9.29ms.