GROUP By fields which partially match index run slowly

n1ql
query

#1

I have documents in a bucket as follows:-
{
“tradeData”: {
“strategyName”: “USD”,
“portfolioName”: “DAMFDA”,
“isDirectCitcoTrade”: false,
“relatedInfo”: {
“17”: “44421207326”,
“side”: “Buy”,
“isInitialValueInNativeCcy”: “False”,
“fxRate”: “1”
},
“rawCitcoData”: {},
“isTradeEligibleForValuation”: false,
“isTradeNotEligibleForValuation”: false,
“id”: 66533334,
“name”: “BOND_FUTURE_Buy_TH_WNH8_Comdty”,
“version”: 0,
“instrumentId”: 636526335416699136,
“instrumentVersion”: 0,
“notional”: 46.0,
“fund”: “DAMFDA”,
“portfolioManager”: “TH”,
“counterParty”: “UBSW”,
“executionBroker”: “UBSW”,
“side”: “Buy”,
“status”: “Active”,
“tags”: “USD”,
“sourceTradeId”: “SYSTEM_2018_01_27_07_05_41_669”,
“sourceTradeStatus”: “Active”,
“portfolioId”: 0,
“strategyId”: 0,
“inception”: “2018-01-27T00:00:00”,
“asOfDate”: “0001-01-01T00:00:00”,
“originalUpdate”: “2018-01-27T00:00:00”,
“created”: “2018-01-27T07:06:29.2631214+08:00”,
“lastUpdated”: “2018-01-27T07:06:29.2631214+08:00”,
“initialValue”: 7513572.916666666,
“sourceSystem”: “Trade Processor”,
“executionUser”: “Futures-AllocationManager”,
“commission”: 0.0,
“fee”: 0.0,
“tradeDate”: “2018-01-26T00:00:00”,
“custodian”: “UBS-FO”,
“valueDate”: “2018-01-26T00:00:00”,
“settlementDate”: “2018-03-20T00:00:00”,
“price”: 163.33854166666666,
“orderQuantity”: 34.0,
“orderCreationTime”: “1970-01-01T00:00:00”,
“instrument”: {
“contractSize”: 100000.0,
“frequency”: “Semi-annual”,
“dayCountConvention”: “DC_ACT_365”,
“bloombergCode”: “WNH8 Comdty”,
“reutersCode”: “”,
“expiry”: “2018-03-20T00:00:00”,
“fixing”: 0.0,
“fixingType”: “NotFixed”,
“metaInfo”: {
“bbgid”: “WNH8 Comdty”,
“contracT_SIZE”: “100000.000000”,
“country”: “US”,
“currency”: “USD”,
“exchangE_CODE”: “CBT”,
“id”: “WNH8 Comdty”,
“lasT_REQUEST_DATE”: “2018-01-04 03-22-21”,
“name”: “”,
“underlying”: “”
},
“id”: 9425710,
“name”: “WNH8 Comdty”,
“denominationCurrency”: “USD”,
“type”: “BOND_FUTURE”,
“assetClass”: “FIXED_INCOME”,
“assetCurrency1”: “USD”,
“assetCurrency2”: “”,
“category”: “FUT”,
“maturity”: “2018-03-20T00:00:00”,
“settlementDate”: “2018-01-03T00:00:00”,
“version”: 1,
“source”: “BBGTradeBook”,
“sourceId”: “9425710”,
“inception”: “2018-01-03T00:00:00”,
“originalUpdate”: “2018-01-04T03:22:11.905+08:00”,
“created”: “2018-01-04T03:22:11.905+08:00”,
“lastUpdated”: “2018-01-04T03:22:16.7922237+08:00”
}
},
“ddasSnapshotVersion”: {
“snapshotId”: “000000000000000000000000”,
“fund”: “DAMFDA”,
“version”: 0,
“asOfDate”: “2018-02-08T00:00:00”,
“saveTime”: “2018-04-27T19:01:25.4187803+08:00”
},
“id”: “c05cc2ccab1247e7bbcb3297”,
“riskData”: {
“bondFwdYield”: {
“portfolioManager”: “TH”,
“tradeId”: “DAMFDA:66533334:9425710”,
“underlier”: “USD”,
“baseValue”: 0.00120085467387554,
“shift”: 0.0,
“firstOrderExposure”: -165.02256278833235,
“secondOrderExposure”: 0.0,
“instrumentExposure”: -35874.4701713766,
“ccy1Amount”: 0.0,
“ccy2Amount”: 0.0,
“tenor”: “29Y6M”,
“skewType”: null,
“expiry”: “”,
“exposure”: “BOND_FWD_YIELD”
},
“bondYield”: {
“portfolioManager”: “TH”,
“tradeId”: “DAMFDA:66533334:9425710”,
“underlier”: “USD”,
“baseValue”: 0.0280354,
“shift”: 0.0,
“firstOrderExposure”: -11583.038626704392,
“secondOrderExposure”: 0.0,
“instrumentExposure”: -2518051.87537052,
“ccy1Amount”: 0.0,
“ccy2Amount”: 0.0,
“tenor”: “30Y”,
“skewType”: null,
“expiry”: “”,
“exposure”: “BOND_YIELD”
}
},

“pnlData”: {
“fund”: null,
“portfolioManager”: “TH”,
“tradeId”: “66533334:9425710”,
“pnLItemType”: null,
“value”: 0.0,
“positionChange”: 0.0,
“startNPV”: 0.0,
“currentNPV”: -20124.9999999991,
“startImntNPV”: 157031.25,
“currentImntNPV”: 156593.75,
“startFX”: 0.0,
“currentFX”: 0.0,
“actual”: -20124.9999999991,
“expected”: -20859.7528169414,
“unexplained”: 734.752816942375,
“theta”: 0.0,
“translation”: 0.0,
“equityDeltaSpecific”: 0.0,
“yieldDeltaSpecific”: -20642.4002171707,
“volSpecific”: 0.0,
“equityDelta”: 0.0,
“equityGamma”: 0.0,
“equityVega”: 0.0,
“fxDelta”: 0.0,
“fxGamma”: 0.0,
“fxFwdDelta”: 0.0,
“fxFwdGamma”: 0.0,
“fxAtmVega”: 0.0,
“fxSkewVega”: 0.0,
“depositDelta”: 0.0,
“depositGamma”: 0.0,
“futureDelta”: 0.0,
“futureGamma”: 0.0,
“swapDelta”: 0.0,
“swapGamma”: 0.0,
“singleCurrencyBasisDelta”: 0.0,
“crossCurrencyBasisDelta”: 0.0,
“bondYieldDelta”: -217.352599770743,
“bondYieldGamma”: 0.0,
“inflationYieldDelta”: 0.0,
“inflationYieldGamma”: 0.0,
“swaptionVega”: 0.0,
“capVega”: 0.0,
“commodityDelta”: 0.0,
“commodityGamma”: 0.0
}
}
I also have an index defined as:-
CREATE INDEX ix11 ON DartAggregatedComplete((tradeData.portfolioManager),(ddasSnapshotVersion.asOfDate))

The following query runs in 3 secs which is what I expect:-

SELECT t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, COUNT(*) as TradeCount
FROM DartAggregatedComplete t
WHERE t.tradeData.portfolioManager IS NOT NULL AND t.ddasSnapshotVersion.asOfDate IS NOT NULL
GROUP BY t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate
ORDER by t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate

I have another query as follows:-
SELECT t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, SUM(t.pnlData.currentNPV) as PnL
FROM DartAggregatedComplete t
WHERE t.tradeData.portfolioManager IS NOT NULL AND t.ddasSnapshotVersion.asOfDate IS NOT NULL
GROUP BY t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, t.pnlData.currentNPV
ORDER by t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, t.pnlData.currentNPV

Explain returned:-

{
“plan”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Sequence”,
“~children”: [
{
#operator”: “IndexScan3”,
“as”: “t”,
“index”: “ix11”,
“index_id”: “e541057a3f76ec79”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “DartAggregatedComplete”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“inclusion”: 0,
“low”: “null”
},
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
},
{
#operator”: “Fetch”,
“as”: “t”,
“keyspace”: “DartAggregatedComplete”,
“namespace”: “default”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Filter”,
“condition”: “((((t.tradeData).portfolioManager) is not null) and (((t.ddasSnapshotVersion).asOfDate) is not null))”
},
{
#operator”: “InitialGroup”,
“aggregates”: [
“sum(((t.pnlData).currentNPV))”
],
“group_keys”: [
“((t.tradeData).portfolioManager)”,
“((t.ddasSnapshotVersion).asOfDate)”,
“((t.pnlData).currentNPV)”
]
}
]
}
},
{
#operator”: “IntermediateGroup”,
“aggregates”: [
“sum(((t.pnlData).currentNPV))”
],
“group_keys”: [
“((t.tradeData).portfolioManager)”,
“((t.ddasSnapshotVersion).asOfDate)”,
“((t.pnlData).currentNPV)”
]
},
{
#operator”: “FinalGroup”,
“aggregates”: [
“sum(((t.pnlData).currentNPV))”
],
“group_keys”: [
“((t.tradeData).portfolioManager)”,
“((t.ddasSnapshotVersion).asOfDate)”,
“((t.pnlData).currentNPV)”
]
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “((t.tradeData).portfolioManager)”
},
{
“expr”: “((t.ddasSnapshotVersion).asOfDate)”
},
{
“as”: “PnL”,
“expr”: “sum(((t.pnlData).currentNPV))”
}
]
}
]
}
}
]
},
{
#operator”: “Order”,
“sort_terms”: [
{
“expr”: “((t.tradeData).portfolioManager)”
},
{
“expr”: “((t.ddasSnapshotVersion).asOfDate)”
},
{
“expr”: “((t.pnlData).currentNPV)”
}
]
},
{
#operator”: “FinalProject”
}
]
},
“text”: “SELECT t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, SUM(t.pnlData.currentNPV) as PnL\r\nFROM DartAggregatedComplete t\r\nWHERE t.tradeData.portfolioManager IS NOT NULL AND t.ddasSnapshotVersion.asOfDate IS NOT NULL\r\nGROUP BY t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, t.pnlData.currentNPV\r\nORDER by t.tradeData.portfolioManager, t.ddasSnapshotVersion.asOfDate, t.pnlData.currentNPV”
}

I cancelled the query as it did not complete within 3 minutes.

My question is :- if the group by fields exactly match an index, the query is super fast, if it is a partial match it is very slow?
Is there a solution to this?

John


#2

The second query is not covered by index because pnlData.currentNPV not part of index. So it needs fetch the document from the Data node.

Use following index so that both queries will be covered.

CREATE INDEX ix11 ON DartAggregatedComplete(tradeData.portfolioManager, ddasSnapshotVersion.asOfDate, pnlData.currentNPV)