N1QL Query order by desc is slow

[Han_Chris1]

Hi,

I’m using couchbase 6.5.1 and having issue for query using ‘order by’
My bucket contains 60 mio data and I have created index:

CREATE INDEX idx-myBucket-01 ON myBucket(idnumber) WHERE (type_ = ‘xxx’)

And below is my query:

select * from myBucket where type_ = ‘xxx’ and idnumber <> ‘’ order by idnumber desc offset 10 limit 10;

The query is very slow and even timeout

Is there any way to enhance the query performance?

Thanks

[vsr1]

Try

CREATE INDEX idx-myBucket-01 ON `myBucket` ( `idnumber`  DESC ) 
WHERE ( `type_` = ‘xxx’)

Single Index to cover different sort fields (both asc & desc)

Hi @vsr1 I tried the below index, Query1 looks fine but Query 2,3,4 takes more time to get the results. Am I missing something? could you please give me an example of an index that suits my need, please? CREATE INDEX idx_ImageSearch ON QA-ImageManagement(OriginalWidth,OriginalHeight) WHERE (Type = “Image”)

[Han_Chris1]

Thanks @vsr1, it works!

[Han_Chris1]

@vsr1

idnumber DESC

Hi @vsr1,

Just want to make sure,
If I want to capture both query order by ASC and DESC ,
so I need to create both index like this :

CREATE INDEX idx-myBucket-01 ON myBucket ( type_,idnumber ASC,idnumber DESC )
WHERE ( type_ = ‘xxx’)

or

CREATE INDEX idx-myBucket-01 ON myBucket ( type_,idnumber ASC )
WHERE ( type_ = ‘xxx’)
CREATE INDEX idx-myBucket-02 ON myBucket ( type_,idnumber DESC )
WHERE ( type_ = ‘xxx’)

or only

CREATE INDEX idx-myBucket-01 ON myBucket ( type_,idnumber )
WHERE ( type_ = ‘xxx’)

?

[vsr1]

Index WHERE clause has type equality, So not required in index keys. Also you might need USE INDEX pick right index based on query order

   CREATE INDEX `idx-myBucket-01` ON `myBucket` ( `idnumber` ASC ) WHERE ( `type_` = ‘xxx’)
    CREATE INDEX `idx-myBucket-02` ON `myBucket` ( `idnumber` DESC ) WHERE ( `type_` = ‘xxx’)

[Han_Chris1]

Hi @vsr1,

Well noted, so I need to create many combitaions of indexes?

I’ve tried to create indexes, and see the plan text only have 10 items, but it takes more than 3 secs.
I saw kernTime is high, what is it? how to tune that?

This is my query:

SELECT *
FROM mybucket a
WHERE a.type_ = ‘xxx’
AND a.transaction_date is not MISSING
AND a.year = 2021
AND a.month = 1
AND a.number = ‘111’
ORDER BY a.transaction_date DESC
LIMIT 10 OFFSET 10

This is my index :

CREATE INDEX idx-mybucket-1 ON mybucket(transaction_date DESC,year,month,number) WHERE (type_ = “xxx”)

This is my plan text:

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “7.635µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “43.331µs”,
“servTime”: “5.571942ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:mybucket”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “10.432µs”
},
“~children”: [
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 2,
“execTime”: “830.815µs”,
“kernTime”: “210ns”,
“state”: “running”
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 10,
“#phaseSwitches”: 43,
“execTime”: “159.928µs”,
“kernTime”: “26.448µs”,
“servTime”: “2.408697999s”
},
“as”: “a”,
“index”: “idx-mybucket-test-03”,
“index_id”: “d0b3d6a43e5fc9e0”,
“index_order”: [
{
“keypos”: 0
},
{
“desc”: true,
“keypos”: 1
}
],
“index_projection”: {
“primary_key”: true
},
“keyspace”: “mybucket”,
“limit”: “10”,
“namespace”: “default”,
“offset”: “10”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"TEST"”,
“inclusion”: 3,
“low”: “"TEST"”
},
{
“inclusion”: 1,
“low”: “null”
},
{
“high”: “2021”,
“inclusion”: 3,
“low”: “2021”
},
{
“high”: “1”,
“inclusion”: 3,
“low”: “1”
},
{
“high”: “"1"”,
“inclusion”: 3,
“low”: “"1"”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:02.408”,
“#time_absolute”: 2.408857927
},
{
“#operator”: “Fetch”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 45,
“execTime”: “178.177µs”,
“kernTime”: “2.408931839s”,
“servTime”: “3.169494ms”
},
“as”: “a”,
“keyspace”: “mybucket”,
“namespace”: “default”,
“#time_normal”: “00:00.003”,
“#time_absolute”: 0.0033476709999999995
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 2,
“execTime”: “1.270778ms”,
“kernTime”: “445ns”,
“state”: “running”
},
“~children”: [
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 43,
“execTime”: “867.174µs”,
“kernTime”: “2.412304232s”
},
“condition”: “((((((a.type_) = "TEST") and ((a.transaction_date) is not missing)) and ((a.year) = 2021)) and ((a.month) = 1)) and ((a.number) = "1"))”,
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000867174
},
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 35,
“execTime”: “73.494µs”,
“kernTime”: “2.412735021s”
},
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000073494
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 21,
“execTime”: “19.68µs”
},
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.00001968
}
],
“#time_normal”: “00:00.001”,
“#time_absolute”: 0.001270778
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000830815
},
{
“#operator”: “Limit”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 31,
“execTime”: “120.185µs”,
“kernTime”: “2.725989ms”
},
“expr”: “10”,
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000120185
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000010432000000000001
},
“#time_normal”: “00:00.005”,
“#time_absolute”: 0.005615273
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 10,
“#itemsOut”: 10,
“#phaseSwitches”: 23,
“execTime”: “2.88116ms”,
“kernTime”: “2.418879617s”
},
“#time_normal”: “00:00.002”,
“#time_absolute”: 0.00288116
}
],
“~versions”: [
“6.5.0-N1QL”,
“6.5.1-6299-enterprise”
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000007635
}