Select Query Optimization

SQL++
1

Below select query is taking 37 sec to execute can we optimize the below query.

select numtype, META().id from config where (__t=“icb-coi” or __t=“icb-coi-range”) and __at > 0 and status != 2 and name =‘NOLLAMEPP’ and hpmn=5201 and (META().id like ‘icb-coi-details:22:5555492234550105%’ OR 5555492234550105 >= CASE WHEN SPLIT(META().id, ‘:’)[2] = ‘*’ THEN -1 ELSE TONUMBER(SPLIT(META().id, ‘:’)[2]) END AND 5555492234550105 <= TONUMBER(SPLIT(META().id, ‘:’)[3])) order by META().id asc limit 1000
Index :
CREATE INDEX icb_con_coi_covering_idx ON config(__t,__at,status,name,hpmn,enabled,numtype) ;

2

It looks like Your META().id condition, ORDER by might be contributing. Execute query and check Plan Text for the timings counts of each operators.

SELECT numtype, META().id
FROM config
WHERE __t IN ["icb-coi" , "icb-coi-range"]
      AND __at > 0 AND status != 2
      AND name = "NOLLAMEPP" AND hpmn = 5201
      AND (META().id LIKE "icb-coi-details:22:5555492234550105%"
           OR 5555492234550105 >= CASE WHEN SPLIT(META().id, ":")[2] = "*" THEN -1 ELSE TONUMBER(SPLIT(META().id, ":")[2]) END
           AND 5555492234550105 <= TONUMBER(SPLIT(META().id, ":")[3]))
ORDER BY META().id ASC
LIMIT 1000

CREATE INDEX icb_con_coi_covering_idx ON config(__t,__at,name,hpmn,status, enabled,numtype) ;
3

Thank you for reply.
Please find the plan text for the query.

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “4.261µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “4.401µs”,
“servTime”: “1.122815ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:config”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “2.339µs”
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 2645249,
“#phaseSwitches”: 10580999,
“execTime”: “6.471159094s”,
“kernTime”: “17.676079775s”,
“servTime”: “1.209184093s”
},
“covers”: [
“cover ((config.__t))”,
“cover ((config.__at))”,
“cover ((config.status))”,
“cover ((config.name))”,
“cover ((config.hpmn))”,
“cover ((config.enabled))”,
“cover ((config.numtype))”,
“cover ((meta(config).id))”
],
“index”: “icb_con_coi_covering_idx”,
“index_id”: “f5dd66f21cf3bc”,
“index_projection”: {
“entry_keys”: [
0,
1,
2
],
“primary_key”: true
},
“keyspace”: “config”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““icb-coi””,
“inclusion”: 3,
“low”: ““icb-coi””
},
{
“inclusion”: 0,
“low”: “0”
},
{
“high”: “2”,
“inclusion”: 0,
“low”: “null”
}
]
},
{
“exact”: true,
“range”: [
{
“high”: ““icb-coi””,
“inclusion”: 3,
“low”: ““icb-coi””
},
{
“inclusion”: 0,
“low”: “0”
},
{
“inclusion”: 0,
“low”: “2”
}
]
},
{
“exact”: true,
“range”: [
{
“high”: ““icb-coi-range””,
“inclusion”: 3,
“low”: ““icb-coi-range””
},
{
“inclusion”: 0,
“low”: “0”
},
{
“high”: “2”,
“inclusion”: 0,
“low”: “null”
}
]
},
{
“exact”: true,
“range”: [
{
“high”: ““icb-coi-range””,
“inclusion”: 3,
“low”: ““icb-coi-range””
},
{
“inclusion”: 0,
“low”: “0”
},
{
“inclusion”: 0,
“low”: “2”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:07.6803”,
“#time_absolute”: 7.680343187
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “2.822µs”
},
“~children”: [
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 2645249,
“#phaseSwitches”: 5290501,
“execTime”: “24.562778907s”,
“kernTime”: “793.661382ms”
},
“condition”: “(((((cover ((config.__t)) = “icb-coi”) or (cover ((config.__t)) = “icb-coi-range”)) and (0 < cover ((config.__at)))) and (not (cover ((config.status)) = 2))) and ((cover ((meta(config).id)) like “icb-coi-details:%:5555492364601887”) or ((case when ((split(cover ((meta(config).id)), “:”)[2]) = “*”) then (-1) else to_number((split(cover ((meta(config).id)), “:”)[2])) end <= 5555492364601887) and (5555492364601887 <= to_number((split(cover ((meta(config).id)), “:”)[3]))))))”,
“#time_normal”: “00:24.5627”,
“#time_absolute”: 24.562778907
},
{
“#operator”: “InitialProject”,
“#stats”: {
“#phaseSwitches”: 5,
“execTime”: “34.466µs”,
“kernTime”: “25.356444595s”
},
“result_terms”: [
{
“expr”: “cover ((meta(config).id))”
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000034466
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “472ns”
},
“#time_normal”: “00:00”,
“#time_absolute”: 0
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000028220000000000003
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000002339
},
“#time_normal”: “00:00.0011”,
“#time_absolute”: 0.001127216
},
{
“#operator”: “Stream”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “6.531µs”,
“kernTime”: “25.357623381s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000006531
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.5-4521-enterprise”
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000004261
}

4

Your IndexScan qualified 2.6 Million and filter discarded all of them. Please review the META().id filter condition.

5

Thank you for your response VRS1
Yes, there more than 2 millions documents in a bucket and query is producing single row. kindly let me know how to improve the query performance

6

Not sure what this does