Hi ,
I am having performance challenge on below query for 3 months data fetch . I have multiple buckets with Joins in between : The Major bucket is ALL_RATES with 200 M documents and H_LOCAL_COMP_SET with 2 M docs. Created required Covered Index , Primary Key indexes on all buckets . I have 4 Nodes couchbase cluster running in pretty decent high performing PureStorage with K8s. Each node having 10 GB Index Service memory quota, 20 GB Data service memory quota and 20 CPU limits to 75 . All 4 nodes having all services âŠ
Admin setting for servicers and max-parallelism :
{"completed-limit":4000,"completed-threshold":1000,"controls":false,"cpuprofile":"","debug":false,"keep-alive-length":16384,"loglevel":"INFO","max-index-api":3,"max-parallelism":1,"memprofile":"","n1ql-feat-ctrl":0,"pipeline-batch":16,"pipeline-cap":512,"prepared-limit":16384,"pretty":false,"profile":"off","request-size-cap":67108864,"scan-cap":512,"servicers":224,"timeout":0}
Below is index plan : and query is below : taking 2.7 seconds âŠgoal is in 100 concurrent multi-threaded session it should return in Ms ⊠Currently Oracle is getting the same in same volume of data in .10 Secs which is decent . Any improvement suggestions please ?
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "9.149”s"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.591”s",
"servTime": "1.44666ms"
},
"privileges": {
"List": [
{
"Target": "default:D_DATE",
"Priv": 7
},
{
"Target": "default:H_LOCAL_COMP_SET",
"Priv": 7
},
{
"Target": "default:ALL_RATES",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.269”s"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.488”s"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "17.747”s",
"kernTime": "1.544”s",
"servTime": "682.497”s"
},
"covers": [
"cover ((`D_DATE`.`CALENDAR_DATE`))",
"cover ((meta(`D_DATE`).`id`))"
],
"index": "D_DATE_CALENDAR_DATE_INDEX",
"index_id": "2a7dfe118d78e826",
"index_projection": {
"entry_keys": [
0
]
},
"keyspace": "D_DATE",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"2019-08-22\"",
"inclusion": 3,
"low": "\"2019-08-22\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.0007",
"#time_absolute": 0.000700244
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "816ns"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "22.145”s",
"kernTime": "708.788”s"
},
"condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-08-22\" and \"2019-08-22\")",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000022145
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 8,
"execTime": "15.626”s",
"kernTime": "733.324”s"
},
"result_terms": [
{
"expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000015626
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 4,
"execTime": "1.353”s",
"kernTime": "539ns"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001353
}
],
"#time_normal": "00:00",
"#time_absolute": 0
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001488
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "8.38”s",
"kernTime": "758.599”s"
},
"as": "DD",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000008380000000000001
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.592”s"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 17,
"#phaseSwitches": 77,
"execTime": "61.057”s",
"kernTime": "1.674831ms"
},
"alias": "CS",
"on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
"~child": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 17,
"#phaseSwitches": 73,
"execTime": "46.935”s",
"kernTime": "3.606”s",
"servTime": "845.218”s"
},
"as": "CS",
"covers": [
"cover ((`CS`.`K_SUBSCRIBER_PROPERTY`))",
"cover ((`CS`.`ENTERPRISE_ID`))",
"cover ((`CS`.`SET_NAME`))",
"cover ((`CS`.`IS_SUBSCRIBER`))",
"cover ((`CS`.`K_COMP_PROPERTY`))",
"cover ((meta(`CS`).`id`))"
],
"index": "H_LOCAL_CS_NKEY_INDEX",
"index_id": "e756a6788a7d9c1f",
"keyspace": "H_LOCAL_COMP_SET",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "421000987",
"inclusion": 3,
"low": "421000987"
},
{
"high": "\"607384\"",
"inclusion": 3,
"low": "\"607384\""
},
{
"high": "\"Primary\"",
"inclusion": 3,
"low": "\"Primary\""
},
{
"high": "\"N\"",
"inclusion": 3,
"low": "\"N\""
}
]
}
],
"using": "gsi"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.00006105700000000001
},
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 17,
"#itemsOut": 18083,
"#phaseSwitches": 72437,
"execTime": "141.592582ms",
"kernTime": "2.489108519s"
},
"alias": "AR",
"on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
"~child": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 18083,
"#phaseSwitches": 72417,
"execTime": "80.27145ms",
"kernTime": "4.312155ms",
"servTime": "2.53903168s"
},
"as": "AR",
"covers": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"cover ((`AR`.`VALID_FROM_DTTM`))",
"cover ((`AR`.`VALID_TO_DTTM`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`SOURCE_NAME`))",
"cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
"cover ((`AR`.`LAST_SHOP_DTTM`))",
"cover ((`AR`.`RATE`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((meta(`AR`).`id`))"
],
"index": "ALL_RATES_DXP_INDEX",
"index_id": "18ee534b9af582c3",
"keyspace": "ALL_RATES",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "cover ((`CS`.`K_COMP_PROPERTY`))",
"inclusion": 3,
"low": "cover ((`CS`.`K_COMP_PROPERTY`))"
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-09-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
}
]
}
],
"using": "gsi"
},
"#time_normal": "00:00.1415",
"#time_absolute": 0.141592582
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 18083,
"#itemsOut": 8175,
"#phaseSwitches": 52519,
"execTime": "226.182251ms",
"kernTime": "2.40507214s"
},
"condition": "(((((((((((cover ((`CS`.`ENTERPRISE_ID`)) = \"607384\") and (cover ((`CS`.`SET_NAME`)) = \"Primary\")) and (cover ((`CS`.`IS_SUBSCRIBER`)) = \"N\")) and (cover ((`CS`.`K_SUBSCRIBER_PROPERTY`)) = 421000987)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-09-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_NAME`)) = \"Brand.com\")) and (cover ((`AR`.`GUESTS`)) = 1)) and (cover ((`AR`.`LOS`)) = 1)) and (cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`)) in [\"Apartment/Multi BR\", \"Deluxe\", \"Standard\", \"Suite\"])) and (cover ((`AR`.`VALID_FROM_DTTM`)) < date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\"))) and ((`DD`.`CALENDAR_DATE`) <= cover ((`AR`.`VALID_TO_DTTM`))))",
"#time_normal": "00:00.2261",
"#time_absolute": 0.226182251
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 8175,
"#itemsOut": 748,
"#phaseSwitches": 17849,
"execTime": "149.570325ms",
"kernTime": "2.486629697s"
},
"aggregates": [
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`SOURCE_NAME`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"cover ((`AR`.`LAST_SHOP_DTTM`))",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.1495",
"#time_absolute": 0.149570325
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002592
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 748,
"#itemsOut": 748,
"#phaseSwitches": 2995,
"execTime": "12.154303ms",
"kernTime": "2.637093681s"
},
"aggregates": [
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`SOURCE_NAME`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"cover ((`AR`.`LAST_SHOP_DTTM`))",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.0121",
"#time_absolute": 0.012154303
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 748,
"#itemsOut": 748,
"#phaseSwitches": 2995,
"execTime": "13.622269ms",
"kernTime": "2.648042956s"
},
"aggregates": [
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`SOURCE_NAME`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"cover ((`AR`.`LAST_SHOP_DTTM`))",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.0136",
"#time_absolute": 0.013622269
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.676”s"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 748,
"#itemsOut": 748,
"#phaseSwitches": 2249,
"execTime": "5.894799ms",
"kernTime": "2.658315038s"
},
"result_terms": [
{
"as": "property_name",
"expr": "cover ((`AR`.`PROPERTY_NAME`))"
},
{
"as": "k_property",
"expr": "cover ((`AR`.`K_PROPERTY`))"
},
{
"as": "source_code",
"expr": "cover ((`AR`.`SOURCE_CODE`))"
},
{
"as": "source_name",
"expr": "cover ((`AR`.`SOURCE_NAME`))"
},
{
"as": "guests",
"expr": "cover ((`AR`.`GUESTS`))"
},
{
"as": "los",
"expr": "cover ((`AR`.`LOS`))"
},
{
"as": "arrival_date",
"expr": "cover ((`AR`.`ARRIVAL_DATE`))"
},
{
"as": "last_shop_dttm",
"expr": "cover ((`AR`.`LAST_SHOP_DTTM`))"
},
{
"as": "asof_date",
"expr": "(`DD`.`CALENDAR_DATE`)"
},
{
"as": "min_rate",
"expr": "min(cover ((`AR`.`RATE`)))"
},
{
"as": "max_rate",
"expr": "max(cover ((`AR`.`RATE`)))"
}
],
"#time_normal": "00:00.0058",
"#time_absolute": 0.005894799
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 748,
"#itemsOut": 748,
"#phaseSwitches": 2245,
"execTime": "237.709”s",
"kernTime": "6.99724ms"
},
"#time_normal": "00:00.0002",
"#time_absolute": 0.000237709
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001676
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000003269
},
"#time_normal": "00:00.0014",
"#time_absolute": 0.001449251
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 748,
"#itemsOut": 748,
"#phaseSwitches": 2995,
"execTime": "136.224”s",
"kernTime": "2.672850809s"
},
"#time_normal": "00:00.0001",
"#time_absolute": 0.000136224
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.1-2037-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000009148999999999998
}
Query as below :
SELECT AR.PROPERTY_NAME property_name,
AR.K_PROPERTY k_property,
AR.SOURCE_CODE source_code,
AR.SOURCE_NAME source_name,
AR.GUESTS guests,
AR.LOS los,
/*AR.SHOP_STATUS,*/
AR.ARRIVAL_DATE arrival_date,
AR.LAST_SHOP_DTTM last_shop_dttm,
DD.CALENDAR_DATE asof_date,
MIN(AR.RATE) min_rate,
MAX(AR.RATE) max_rate
FROM (SELECT CALENDAR_DATE FROM D_DATE WHERE CALENDAR_DATE BETWEEN '2019-08-22' AND '2019-08-22') DD
INNER JOIN H_LOCAL_COMP_SET CS
ON DD.CALENDAR_DATE = DD.CALENDAR_DATE
INNER JOIN ALL_RATES AR
ON AR.K_PROPERTY = CS.K_COMP_PROPERTY
WHERE CS.ENTERPRISE_ID = '607384'
AND CS.SET_NAME = 'Primary'
AND CS.IS_SUBSCRIBER = 'N'
AND CS.K_SUBSCRIBER_PROPERTY = 421000987
AND AR.ARRIVAL_DATE BETWEEN '2019-09-01' and '2019-12-31'
AND AR.SOURCE_NAME = 'Brand.com'
AND AR.GUESTS = 1
AND AR.LOS = 1
AND AR.ROOM_PRODUCT_DESCRIPTION IN ['Apartment/Multi BR', 'Deluxe', 'Standard', 'Suite']
AND AR.VALID_FROM_DTTM < date_add_str(DD.CALENDAR_DATE, 1, 'day')
AND AR.VALID_TO_DTTM >= DD.CALENDAR_DATE
GROUP BY AR.K_PROPERTY,
AR.PROPERTY_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.LAST_SHOP_DTTM,
/* AR.SHOP_STATUS,*/
DD.CALENDAR_DATE
Covered Index on Huge bucket sample :
CREATE INDEX ALL_RATES_DXP_INDEX ON ALL_RATES ( K_PROPERTY , ARRIVAL_DATE , VALID_FROM_DTTM , VALID_TO_DTTM , LOS , GUESTS , SOURCE_CODE , SOURCE_NAME , ROOM_PRODUCT_DESCRIPTION , LAST_SHOP_DTTM , RATE , PROPERTY_NAME )