Why query take ~20s from 330k data records with right index

SQL++
1

Hi,
I run a query like below with right index, but why it take ~20s from 330k data records to return?

SELECT *
FROM bucket as onerisk
WHERE _class=‘com.blablabla.AccountDeskPositionInfo’
AND type = ‘FIRM’
AND positionKey.id = ‘SH_DUM1’
AND positionKey.symbol = ‘301196961’

with right index as below shows"

Index Currently Used

CREATE INDEX idx_position ON bucket(type,positionKey.id,positionKey.symbol) WHERE (_class = ‘com.blablabla.AccountDeskPositionInfo’)

Existing indexes are sufficient.

the total data volume is ~330K, why it take2 20s to return ?

the part execute plan and doc structure are below:

“_class”: “com.blablabla.AccountDeskPositionInfo”,
“intraday”: true,
“intradayAdjust”: false,
“position”: 100000000,
“positionCorrect”: 0,
“positionKey”: {
“currency”: “USD”,
“id”: “01CBBR”,
“symbol”: “278744”
},
“region”: “NAM”,
“sodPositionAdjust”: 0,
“timestamp”: 1607667715545,
“type”: "FIRM

{
“#operator”: “Fetch”,
“#stats”: {
“#itemsIn”: 333137,
“#itemsOut”: 333137,
“#phaseSwitches”: 1374195,
“execTime”: “512.694467ms”,
“kernTime”: “449.620729ms”,
“servTime”: “13.932198398s”
},
“as”: “onerisk”,
“keyspace”: “bucket”,
“namespace”: “default”,
“#time_normal”: “00:14.444”,
“#time_absolute”: 14.444892865
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “1.942µs”
},
“~children”: [
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 333137,
“#phaseSwitches”: 666277,
“execTime”: “6.882374061s”,
“kernTime”: “8.012326966s”
},
“condition”: “(((((onerisk._class) = “com.blablabla.AccountDeskPositionInfo”) and ((onerisk.type) = “FIRM”)) and (((onerisk.positionKey).id) = “SH_DUM1”)) and (((onerisk.positionKey).symbol) = “301196961”))”,
“#time_normal”: “00:06.882”,
“#time_absolute”: 6.882374061
},

BTW the “#itemsIn”: 333137 comes from below:

SELECT count(1)
FROM bucket as onerisk
WHERE _class=‘com.blablabla.AccountDeskPositionInfo’
AND type = ‘FIRM’

2

Index definition seems to be right.

CE version limits CPU cores to 4 for query service. Fetch 333K document took 13s.

As you are looking whole document. Get the document keys from N1QL and use SDK to get the document directly from Data node (asynchronously and parallel)

SELECT RAW META().id
FROM bucket as onerisk
WHERE _class=‘com.blablabla.AccountDeskPositionInfo’
AND type = ‘FIRM’
AND positionKey.id = ‘SH_DUM1’
AND positionKey.symbol = ‘301196961’
4

even we just count it, it also take 20s, do you have any clue how we can speed up this query?
SELECT COUNT(1)
FROM bucket AS onerisk
WHERE _class=‘com.blablalba.AccountDeskPositionInfo’
AND type = ‘FIRM’
AND positionKey.id = ‘SH_DUM1’
AND positionKey.symbol = ‘301196961’