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