sure . thanks … I will ask .
@vsr1 - Sorry I am opening this again because we talked about here in same context : the query didn’t change a much but performance is still ~8 Seconds and obviously not acceptable , this follows the standard cover index Rule #11 guidelines : anything I am missing here : plan looks below :
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.504µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.298µs",
"servTime": "1.754613ms"
},
"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": "6.742µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.252µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "21.916µs",
"kernTime": "4.272µs",
"servTime": "1.015291ms"
},
"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-12-01\"",
"inclusion": 3,
"low": "\"2019-12-01\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.0010",
"#time_absolute": 0.001037207
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "451ns"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "36.245µs",
"kernTime": "1.048708ms"
},
"condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-12-01\" and \"2019-12-01\")",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000036244999999999996
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 8,
"execTime": "19.683µs",
"kernTime": "1.087713ms"
},
"result_terms": [
{
"expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000019683
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 4,
"execTime": "1.726µs",
"kernTime": "4.469µs"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001726
}
],
"#time_normal": "00:00",
"#time_absolute": 0
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001252
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "15.949µs",
"kernTime": "1.123681ms"
},
"as": "DD",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000015949
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "13.865µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 13,
"#phaseSwitches": 61,
"execTime": "45.578µs",
"kernTime": "3.099131ms"
},
"alias": "CS",
"on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 2,
"execTime": "8.541730128s",
"kernTime": "238ns",
"state": "running"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 13,
"#phaseSwitches": 55,
"execTime": "32.428µs",
"kernTime": "2.552µs",
"servTime": "955.542µs"
},
"as": "CS",
"index": "H_LOCAL_CS_NKEY_INDEX",
"index_id": "e756a6788a7d9c1f",
"index_projection": {
"primary_key": true
},
"keyspace": "H_LOCAL_COMP_SET",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "3257687873",
"inclusion": 3,
"low": "3257687873"
},
{
"high": "\"535878\"",
"inclusion": 3,
"low": "\"535878\""
},
{
"high": "\"Primary\"",
"inclusion": 3,
"low": "\"Primary\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 13,
"#itemsOut": 13,
"#phaseSwitches": 57,
"execTime": "85.469µs",
"kernTime": "999.322µs",
"servTime": "859.933µs"
},
"as": "CS",
"keyspace": "H_LOCAL_COMP_SET",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000045578
},
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 13,
"#itemsOut": 40836,
"#phaseSwitches": 163425,
"execTime": "598.341133ms",
"kernTime": "7.861594542s"
},
"alias": "AR",
"on_clause": "((`AR`.`K_PROPERTY`) = (`CS`.`K_COMP_PROPERTY`))",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 26,
"execTime": "127.612624ms",
"kernTime": "5.505µs",
"state": "running"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 40836,
"#phaseSwitches": 163383,
"execTime": "74.183643ms",
"kernTime": "6.044112743s",
"servTime": "128.595753ms"
},
"as": "AR",
"index": "ALL_RATES_PART_INDEX",
"index_id": "85d3167f00c2768b",
"index_projection": {
"primary_key": true
},
"keyspace": "ALL_RATES",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Apartment/Multi BR\"",
"inclusion": 3,
"low": "\"Apartment/Multi BR\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Deluxe\"",
"inclusion": 3,
"low": "\"Deluxe\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Standard\"",
"inclusion": 3,
"low": "\"Standard\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Suite\"",
"inclusion": 3,
"low": "\"Suite\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 40836,
"#itemsOut": 40836,
"#phaseSwitches": 168499,
"execTime": "112.558143ms",
"kernTime": "329.876656ms",
"servTime": "7.971773634s"
},
"as": "AR",
"keyspace": "ALL_RATES",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.5983",
"#time_absolute": 0.598341133
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 40836,
"#itemsOut": 40836,
"#phaseSwitches": 163347,
"execTime": "2.562816078s",
"kernTime": "5.897735972s"
},
"condition": "(((((((((((`CS`.`ENTERPRISE_ID`) = \"535878\") and ((`CS`.`SET_NAME`) = \"Primary\")) and ((`CS`.`K_SUBSCRIBER_PROPERTY`) = 3257687873)) and ((`AR`.`ARRIVAL_DATE`) between \"2019-12-01\" and \"2019-12-31\")) and ((`AR`.`SOURCE_CODE`) = \"WEB\")) 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 ((`DD`.`CALENDAR_DATE`) <= (`AR`.`VALID_TO_DTTM`)))",
"#time_normal": "00:02.5628",
"#time_absolute": 2.562816078
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 40836,
"#itemsOut": 401,
"#phaseSwitches": 82477,
"execTime": "3.51059413s",
"kernTime": "4.95041949s"
},
"aggregates": [
"max((`AR`.`RATE`))",
"min((`AR`.`RATE`))"
],
"group_keys": [
"(`AR`.`K_PROPERTY`)",
"(`AR`.`PROPERTY_NAME`)",
"(`CS`.`SUBSCRIBER_NAME`)",
"(`AR`.`SOURCE_CODE`)",
"(`AR`.`SOURCE_NAME`)",
"(`AR`.`GUESTS`)",
"(`AR`.`LOS`)",
"(`AR`.`ARRIVAL_DATE`)",
"(`AR`.`SHOP_STATUS`)",
"(`AR`.`LINE_STATUS`)",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:03.5105",
"#time_absolute": 3.51059413
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000013865
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 401,
"#itemsOut": 401,
"#phaseSwitches": 1607,
"execTime": "25.996137ms",
"kernTime": "8.461137836s"
},
"aggregates": [
"max((`AR`.`RATE`))",
"min((`AR`.`RATE`))"
],
"group_keys": [
"(`AR`.`K_PROPERTY`)",
"(`AR`.`PROPERTY_NAME`)",
"(`CS`.`SUBSCRIBER_NAME`)",
"(`AR`.`SOURCE_CODE`)",
"(`AR`.`SOURCE_NAME`)",
"(`AR`.`GUESTS`)",
"(`AR`.`LOS`)",
"(`AR`.`ARRIVAL_DATE`)",
"(`AR`.`SHOP_STATUS`)",
"(`AR`.`LINE_STATUS`)",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.0259",
"#time_absolute": 0.025996137000000002
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 401,
"#itemsOut": 401,
"#phaseSwitches": 1607,
"execTime": "28.926322ms",
"kernTime": "8.487251139s"
},
"aggregates": [
"max((`AR`.`RATE`))",
"min((`AR`.`RATE`))"
],
"group_keys": [
"(`AR`.`K_PROPERTY`)",
"(`AR`.`PROPERTY_NAME`)",
"(`CS`.`SUBSCRIBER_NAME`)",
"(`AR`.`SOURCE_CODE`)",
"(`AR`.`SOURCE_NAME`)",
"(`AR`.`GUESTS`)",
"(`AR`.`LOS`)",
"(`AR`.`ARRIVAL_DATE`)",
"(`AR`.`SHOP_STATUS`)",
"(`AR`.`LINE_STATUS`)",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.0289",
"#time_absolute": 0.028926321999999997
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.235µs"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 401,
"#itemsOut": 401,
"#phaseSwitches": 1208,
"execTime": "24.544292ms",
"kernTime": "8.51626129s"
},
"result_terms": [
{
"as": "property_name",
"expr": "(`AR`.`PROPERTY_NAME`)"
},
{
"as": "subscriber_name",
"expr": "(`CS`.`SUBSCRIBER_NAME`)"
},
{
"as": "k_property",
"expr": "(`AR`.`K_PROPERTY`)"
},
{
"as": "source_code",
"expr": "(`AR`.`SOURCE_CODE`)"
},
{
"as": "source_name",
"expr": "(`AR`.`SOURCE_NAME`)"
},
{
"as": "guests",
"expr": "(`AR`.`GUESTS`)"
},
{
"as": "los",
"expr": "(`AR`.`LOS`)"
},
{
"as": "arrival_date",
"expr": "(`AR`.`ARRIVAL_DATE`)"
},
{
"as": "min_rate",
"expr": "min((`AR`.`RATE`))"
},
{
"as": "max_rate",
"expr": "max((`AR`.`RATE`))"
},
{
"as": "shop_status",
"expr": "(`AR`.`SHOP_STATUS`)"
},
{
"as": "line_status",
"expr": "(`AR`.`LINE_STATUS`)"
},
{
"as": "asof_date",
"expr": "(`DD`.`CALENDAR_DATE`)"
}
],
"#time_normal": "00:00.0245",
"#time_absolute": 0.024544292
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 401,
"#itemsOut": 401,
"#phaseSwitches": 1204,
"execTime": "200.635µs",
"kernTime": "3.385367ms"
},
"#time_normal": "00:00.0002",
"#time_absolute": 0.00020063499999999998
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001235
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000006742
},
"#time_normal": "00:00.0017",
"#time_absolute": 0.0017569109999999999
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 401,
"#itemsOut": 401,
"#phaseSwitches": 1607,
"execTime": "110.101µs",
"kernTime": "8.54614825s"
},
"#time_normal": "00:00.0001",
"#time_absolute": 0.000110101
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.1-2037-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001504
}
Query as below :
SELECT AR.PROPERTY_NAME property_name,
CS.SUBSCRIBER_NAME subscriber_name,
AR.K_PROPERTY k_property,
AR.SOURCE_CODE source_code,
AR.SOURCE_NAME source_name,
AR.GUESTS guests,
AR.LOS los,
AR.ARRIVAL_DATE arrival_date,
MIN(AR.RATE) min_rate,
MAX(AR.RATE) max_rate,
AR.SHOP_STATUS shop_status,
AR.LINE_STATUS line_status,
DD.CALENDAR_DATE asof_date
FROM (SELECT CALENDAR_DATE FROM D_DATE WHERE CALENDAR_DATE BETWEEN '2019-12-01' AND '2019-12-01') 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 = '535878'
AND CS.SET_NAME = 'Primary'
AND CS.K_SUBSCRIBER_PROPERTY = 3257687873
AND AR.ARRIVAL_DATE BETWEEN '2019-12-01' AND '2019-12-31'
AND AR.SOURCE_CODE = 'WEB'
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,
CS.SUBSCRIBER_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.SHOP_STATUS,
AR.LINE_STATUS,
DD.CALENDAR_DATE
Index as below:
CREATE INDEX `ALL_RATES_PART_INDEX` ON `ALL_RATES`(`SOURCE_CODE`,`GUESTS`,`LOS`,`K_PROPERTY`,`ROOM_PRODUCT_DESCRIPTION`,`ARRIVAL_DATE`,`VALID_FROM_DTTM`,`VALID_TO_DTTM`,`LAST_SHOP_DTTM`,`RATE`,`PROPERTY_NAME`)Earlier it use to cover. Now it is not covered check what fields missing in the indexes
My bad ! Well in your original suggestion you have kept “VALID_FROM_DTTM” as Index key after “ARRIVAL_DATE” which gives better performance however as per Rule #11 says if I have less than (<) predicates that should come first as indexer key(in this case VALID_FROM_DTTM) and anything BETWEEN will come later … So I was playing around changing the order . Any reason why we kept that order ?
Also with the last accepted suggestion query sometimes going above 1 secs which is not acceptable specially when there will be concurrent request in threads and middleware processing plus network this can easily go beyond 1.5 seconds + . So how can we reduce this bottleneck from DB to bring it down more in .00x seconds ?
We have heard CB has reputation in < ms response time and this Bucket data is even nothing comparing the actual volume of data on top of which this query needs to perform well .
here is the 1s+ query plan :
I am expecting and hopeful to see a magic Tuned plan :
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.177µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.913µs",
"servTime": "1.281852ms"
},
"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": "4.014µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.734µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "28.244µs",
"kernTime": "2.745µs",
"servTime": "834.958µ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-12-01\"",
"inclusion": 3,
"low": "\"2019-12-01\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.0008",
"#time_absolute": 0.000863202
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "763ns"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "37.949µs",
"kernTime": "877.476µs"
},
"condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-12-01\" and \"2019-12-01\")",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000037949
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 8,
"execTime": "20.398µs",
"kernTime": "917.998µs"
},
"result_terms": [
{
"expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000020398
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 4,
"execTime": "1.681µs",
"kernTime": "3.38µs"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001681
}
],
"#time_normal": "00:00",
"#time_absolute": 0
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001734
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "33.271µs",
"kernTime": "954.484µs"
},
"as": "DD",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000033271
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.131µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 17,
"#phaseSwitches": 77,
"execTime": "79.206µs",
"kernTime": "2.212971ms"
},
"alias": "CS",
"on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
"~child": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 17,
"#phaseSwitches": 73,
"execTime": "58.725µs",
"kernTime": "5.769µs",
"servTime": "1.143215ms"
},
"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.000079206
},
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 17,
"#itemsOut": 50628,
"#phaseSwitches": 202617,
"execTime": "500.286068ms",
"kernTime": "493.059548ms"
},
"alias": "AR",
"on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
"~child": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 50628,
"#phaseSwitches": 202597,
"execTime": "285.344388ms",
"kernTime": "360.027922ms",
"servTime": "205.596334ms"
},
"as": "AR",
"covers": [
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"cover ((`AR`.`VALID_FROM_DTTM`))",
"cover ((`AR`.`VALID_TO_DTTM`))",
"cover ((`AR`.`LAST_SHOP_DTTM`))",
"cover ((`AR`.`RATE`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((meta(`AR`).`id`))"
],
"index": "ALL_RATES_PART_INDEX",
"index_id": "85d3167f00c2768b",
"keyspace": "ALL_RATES",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "cover ((`CS`.`K_COMP_PROPERTY`))",
"inclusion": 3,
"low": "cover ((`CS`.`K_COMP_PROPERTY`))"
},
{
"high": "\"Apartment/Multi BR\"",
"inclusion": 3,
"low": "\"Apartment/Multi BR\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "cover ((`CS`.`K_COMP_PROPERTY`))",
"inclusion": 3,
"low": "cover ((`CS`.`K_COMP_PROPERTY`))"
},
{
"high": "\"Deluxe\"",
"inclusion": 3,
"low": "\"Deluxe\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "cover ((`CS`.`K_COMP_PROPERTY`))",
"inclusion": 3,
"low": "cover ((`CS`.`K_COMP_PROPERTY`))"
},
{
"high": "\"Standard\"",
"inclusion": 3,
"low": "\"Standard\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"WEB\"",
"inclusion": 3,
"low": "\"WEB\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "cover ((`CS`.`K_COMP_PROPERTY`))",
"inclusion": 3,
"low": "cover ((`CS`.`K_COMP_PROPERTY`))"
},
{
"high": "\"Suite\"",
"inclusion": 3,
"low": "\"Suite\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
}
],
"using": "gsi"
},
"#time_normal": "00:00.5002",
"#time_absolute": 0.500286068
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 50628,
"#itemsOut": 50628,
"#phaseSwitches": 202515,
"execTime": "796.056389ms",
"kernTime": "205.408975ms"
},
"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-12-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_CODE`)) = \"WEB\")) 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.7960",
"#time_absolute": 0.796056389
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 50628,
"#itemsOut": 490,
"#phaseSwitches": 102239,
"execTime": "986.155591ms",
"kernTime": "23.224722ms"
},
"aggregates": [
"max(cover ((`AR`.`LAST_SHOP_DTTM`)))",
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.9861",
"#time_absolute": 0.9861555909999999
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000031309999999999998
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 490,
"#itemsOut": 490,
"#phaseSwitches": 1963,
"execTime": "6.439687ms",
"kernTime": "1.009498452s"
},
"aggregates": [
"max(cover ((`AR`.`LAST_SHOP_DTTM`)))",
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.0064",
"#time_absolute": 0.006439687
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 490,
"#itemsOut": 490,
"#phaseSwitches": 1963,
"execTime": "8.186067ms",
"kernTime": "1.016049149s"
},
"aggregates": [
"max(cover ((`AR`.`LAST_SHOP_DTTM`)))",
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"(`DD`.`CALENDAR_DATE`)"
],
"#time_normal": "00:00.0081",
"#time_absolute": 0.008186067
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.373µs"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 490,
"#itemsOut": 490,
"#phaseSwitches": 1475,
"execTime": "4.509884ms",
"kernTime": "1.024298276s"
},
"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": "guests",
"expr": "cover ((`AR`.`GUESTS`))"
},
{
"as": "los",
"expr": "cover ((`AR`.`LOS`))"
},
{
"as": "arrival_date",
"expr": "cover ((`AR`.`ARRIVAL_DATE`))"
},
{
"as": "last_shop_dttm",
"expr": "max(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.0045",
"#time_absolute": 0.004509884
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 490,
"#itemsOut": 490,
"#phaseSwitches": 1471,
"execTime": "139.009µs",
"kernTime": "7.816929ms"
},
"#time_normal": "00:00.0001",
"#time_absolute": 0.000139009
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001373
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000004014
},
"#time_normal": "00:00.0012",
"#time_absolute": 0.001284765
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 490,
"#itemsOut": 490,
"#phaseSwitches": 1963,
"execTime": "76.874µs",
"kernTime": "1.038038874s"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000076874
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.1-2037-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000011770000000000001
}You have 3 level JOINS, aggregation it takes time.
Optimize N1QL Performance Using Request Profiling
If needed set max_parallelism at request level and see how much you get
yes … we do have joins here : I have tested this same query in other platform with new cluster in MDS and see no difference . Also as soon as I add below in select (which is coming from different bucket) it increases time by .20-.30 seconds . I have COVERED index on CS as well following Rule #11 .
CS.SUBSCRIBER_NAME subscriber_name,
I am reading this document . I remember bumping up parallelism in non-MDS cluster with no difference and reverted back …Will try again .
As an idea do you know if I can make this CS bucket data in same ALL_RATES bucket with different type going to help in performance ? does cover index needs to be on MAX(RATE) rather simple RATE .
Because of the ALL_RATES doc already inflated and too much keys flatening out this more will inflate this too much so we want to go with JOIN approach . Do you also happen to know if servicers # is bottleneck ? or Index-scan is bottleneck here ? or multiple Index can help in this situation ? Does compact / fragmentation has any impact ?
I am just trying to seek all possible options .
Also does CB 6.5 can give better performance ?
thanks always !
Covering index only on fields. MAX is aggregate.
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 50628,
"#itemsOut": 490,
"#phaseSwitches": 102239,
"execTime": "986.155591ms",
"kernTime": "23.224722ms"
}
If you look above it has all details. Input to group is 50628 and there are 490 groups (output). It took 986ms took execute and 23ms waited for kernel time. If you want to improve. ItemsIn/ItemsOut must be close. some cases it is not possible.
In this case max_parallelism 2,4 may help.
Also try the following not sure if anything will help. If not ignore it.
SELECT AR.PROPERTY_NAME property_name,
CS.SUBSCRIBER_NAME subscriber_name,
AR.K_PROPERTY k_property,
AR.SOURCE_CODE source_code,
AR.SOURCE_NAME source_name,
AR.GUESTS guests,
AR.LOS los,
AR.ARRIVAL_DATE arrival_date,
MIN(AR.RATE) min_rate,
MAX(AR.RATE) max_rate,
AR.SHOP_STATUS shop_status,
AR.LINE_STATUS line_status,
CS.CALENDAR_DATE asof_date
FROM (
SELECT DD.CALENDAR_DATE, CS.SUBSCRIBER_NAME, CS.K_COMP_PROPERTY
FROM ( SELECT DISTINCT CS.SUBSCRIBER_NAME, CS.K_COMP_PROPERTY
FROM H_LOCAL_COMP_SET AS CS
WHERE CS.ENTERPRISE_ID = '535878'
AND CS.SET_NAME = 'Primary'
AND CS.K_SUBSCRIBER_PROPERTY = 3257687873) AS CS
UNNEST (SELECT CALENDAR_DATE FROM D_DATE WHERE CALENDAR_DATE BETWEEN '2019-12-01' AND '2019-12-01') DD
) AS CS
INNER JOIN ALL_RATES AR ON AR.K_PROPERTY = CS.K_COMP_PROPERTY
WHERE AR.ARRIVAL_DATE BETWEEN '2019-12-01' AND '2019-12-31'
AND AR.SOURCE_CODE = 'WEB'
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(CS.CALENDAR_DATE, 1, 'day')
AND AR.VALID_TO_DTTM >= CS.CALENDAR_DATE
GROUP BY AR.K_PROPERTY,
AR.PROPERTY_NAME,
CS.SUBSCRIBER_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.SHOP_STATUS,
AR.LINE_STATUS,
CS.CALENDAR_DATE;
I recommend this:
Take single query and optimize first with the right indexes (if needed max_parallelism).
Then See if you can create partition index and improve further.
Then create replicated indexes and run multiple queries same time and see how it scales & SLAs.
Understand …
Modified query is not good :
{
“#operator”: “InitialGroup”,
“#stats”: {
“#itemsIn”: 40836,
“#itemsOut”: 401,
“#phaseSwitches”: 82477,
“execTime”: “3.517520758s”,
“kernTime”: “37.751463ms”
},
bumped up max_parallelism as below . Do I need to bounce each node to it will take effect on the fly ?
No difference in perf either.
{
"completed-limit": 4000,
"completed-threshold": 1000,
"controls": false,
"cpuprofile": "",
"debug": false,
"keep-alive-length": 16384,
"loglevel": "INFO",
"max-index-api": 3,
"max-parallelism": 4,
"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": 700,
"timeout": 0
}
I am trying to understand what makes this below less ? Is this h/w resource contention ? Query issue or Indexing problem ?
“#itemsOut”: 490
I will try out replicated indexes as well . how to write the query multithreaded or it will run automatically with partition index ? Do you suggest anything for num of partitions as “num_partition”:8"
And do I need other buckets partitions as well … ?
The settings per node and not persistent. Best one is set as part of request vs service level.
Note you have 10 group keys and it takes time.
Ran with CURL command in query level with 4 parallel … No good either .
“status”: “success”,
“metrics”: {“elapsedTime”: “1.713657238s”,“executionTime”: “1.713382662s”,“resultCount”: 401,“resultSize”: 124087}
}
bumped up 16 and see variable response between <1 s to 1.3 secs for multiple iterations
How are u running query. With cbq shell or Query Web Console.
You mentioned latest query is not good. So go back to earlier query. Why are results 401 instead of 490
Inside POD not using cbq but regular shell …
curl http://10.xx.xxx.xxx:30854/query/service -u Administrator:password -d “statement=
SELECT AR.PROPERTY_NAME property_name, … &max_parallelism=16”
Sorry my Bad . was taking your query but response seems similar :
“status”: “success”,
“metrics”: {“elapsedTime”: “877.696871ms”,“executionTime”: “877.432958ms”,“resultCount”: 490,“resultSize”: 114864}
}
Tried cbq shell also : below response:
"status": "success",
"metrics": {
"elapsedTime": "883.553542ms",
"executionTime": "883.325237ms",
"resultCount": 490,
"resultSize": 187874
}
}
I think it may take that much time.
You can try ALL_RATES_PART_INDEX index with PARTITION BY (META().id); All others are not required.
Are you saying ?
CREATE INDEX ALL_RATES_INDEX_PART ON ALL_RATES(SOURCE_CODE,GUESTS,LOS,K_PROPERTY,ROOM_PRODUCT_DESCRIPTION,ARRIVAL_DATE,VALID_FROM_DTTM,VALID_TO_DTTM,LAST_SHOP_DTTM,RATE,PROPERTY_NAME) PARTITION BY HASH(META().id);
because when I try to do below it throws syntax error . or else I need to do PRIMARY index
CREATE INDEX ALL_RATES_INDEX_PART ON ALL_RATES PARTITION BY HASH(META().id);
with HASH(META().id) the explain plan looks like below … ~500ms + . there is gain but not considerable.
With max_parallelism 4 and 16 very similar response time. for non MDS this is index :
CREATE INDEX ALL_RATES_INDEX_PART ON ALL_RATES(SOURCE_CODE,GUESTS,LOS,K_PROPERTY,ROOM_PRODUCT_DESCRIPTION,ARRIVAL_DATE,VALID_FROM_DTTM,VALID_TO_DTTM,LAST_SHOP_DTTM,RATE,PROPERTY_NAME ) PARTITION BY HASH(META().id);
I have MDS node with 2 replica performance is like : 800 ms for same query same data volume 1 max parallelism with below settings :
{
"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": 192,
"timeout": 0
}
MDS has below index:
CREATE INDEX ALL_RATES_PART_INDEXONALL_RATES(SOURCE_CODE,GUESTS,LOS,K_PROPERTY,ROOM_PRODUCT_DESCRIPTION,ARRIVAL_DATE,VALID_FROM_DTTM,VALID_TO_DTTM,LAST_SHOP_DTTM,RATE,PROPERTY_NAME,SHOP_STATUS,LINE_STATUS) WITH { "num_replica":2 }
Explain plan for non-MDS as below : ~500 ms response
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.152µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.547µs",
"servTime": "1.719064ms"
},
"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.61µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.366µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "38.816µs",
"kernTime": "5.048µs",
"servTime": "711.8µ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-12-01\"",
"inclusion": 3,
"low": "\"2019-12-01\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.0007",
"#time_absolute": 0.000750616
},
{
"#operator": "Parallel",
"#stats": {
"#phaseSwitches": 1,
"execTime": "7.253µs"
},
"copies": 4,
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 4,
"execTime": "2.466µs"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 16,
"execTime": "47.133µs",
"kernTime": "3.157701ms"
},
"condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-12-01\" and \"2019-12-01\")",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000047133000000000004
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 23,
"execTime": "27.641µs",
"kernTime": "3.226318ms"
},
"result_terms": [
{
"expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000027641
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "2.414µs",
"kernTime": "946ns"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002414
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002466
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000007253
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000013660000000000001
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "17.245µs",
"kernTime": "848.382µs"
},
"as": "DD",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000017245000000000002
},
{
"#operator": "Parallel",
"#stats": {
"#phaseSwitches": 1,
"execTime": "19.487µs"
},
"copies": 4,
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 4,
"execTime": "7.708µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 13,
"#phaseSwitches": 70,
"execTime": "75.793µs",
"kernTime": "11.629441ms"
},
"alias": "CS",
"on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "CS",
"index": "H_LOCAL_CS_NKEY_INDEX",
"index_id": "e756a6788a7d9c1f",
"index_projection": {
"primary_key": true
},
"keyspace": "H_LOCAL_COMP_SET",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "3257687873",
"inclusion": 3,
"low": "3257687873"
},
{
"high": "\"535878\"",
"inclusion": 3,
"low": "\"535878\""
},
{
"high": "\"Primary\"",
"inclusion": 3,
"low": "\"Primary\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "CS",
"keyspace": "H_LOCAL_COMP_SET",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.00007579300000000001
},
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 13,
"#itemsOut": 20396,
"#phaseSwitches": 81674,
"execTime": "139.172715ms",
"kernTime": "318.363767ms"
},
"alias": "AR",
"on_clause": "(cover ((`AR`.`K_PROPERTY`)) = (`CS`.`K_COMP_PROPERTY`))",
"~child": {
"#operator": "IndexScan3",
"as": "AR",
"covers": [
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"cover ((`AR`.`VALID_FROM_DTTM`))",
"cover ((`AR`.`VALID_TO_DTTM`))",
"cover ((`AR`.`LAST_SHOP_DTTM`))",
"cover ((`AR`.`RATE`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((meta(`AR`).`id`))"
],
"index": "ALL_RATES_INDEX_PART",
"index_id": "902769e16c40cbe3",
"keyspace": "ALL_RATES",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "\"EXP\"",
"inclusion": 3,
"low": "\"EXP\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Apartment/Multi BR\"",
"inclusion": 3,
"low": "\"Apartment/Multi BR\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"EXP\"",
"inclusion": 3,
"low": "\"EXP\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Deluxe\"",
"inclusion": 3,
"low": "\"Deluxe\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"EXP\"",
"inclusion": 3,
"low": "\"EXP\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Standard\"",
"inclusion": 3,
"low": "\"Standard\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"EXP\"",
"inclusion": 3,
"low": "\"EXP\""
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "1",
"inclusion": 3,
"low": "1"
},
{
"high": "(`CS`.`K_COMP_PROPERTY`)",
"inclusion": 3,
"low": "(`CS`.`K_COMP_PROPERTY`)"
},
{
"high": "\"Suite\"",
"inclusion": 3,
"low": "\"Suite\""
},
{
"high": "\"2019-12-31\"",
"inclusion": 3,
"low": "\"2019-12-01\""
},
{
"high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 1,
"low": "(`DD`.`CALENDAR_DATE`)"
}
]
}
],
"using": "gsi"
},
"#time_normal": "00:00.1391",
"#time_absolute": 0.139172715
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 20396,
"#itemsOut": 20396,
"#phaseSwitches": 81596,
"execTime": "381.540983ms",
"kernTime": "83.687387ms"
},
"condition": "(((((((((((`CS`.`ENTERPRISE_ID`) = \"535878\") and ((`CS`.`SET_NAME`) = \"Primary\")) and ((`CS`.`K_SUBSCRIBER_PROPERTY`) = 3257687873)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-12-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_CODE`)) = \"EXP\")) 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.3815",
"#time_absolute": 0.381540983
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 20396,
"#itemsOut": 397,
"#phaseSwitches": 41598,
"execTime": "379.766ms",
"kernTime": "86.037483ms"
},
"aggregates": [
"max(cover ((`AR`.`LAST_SHOP_DTTM`)))",
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"(`DD`.`CALENDAR_DATE`)",
"(`CS`.`SUBSCRIBER_NAME`)"
],
"#time_normal": "00:00.3797",
"#time_absolute": 0.379766
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000007708
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000019486999999999998
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 397,
"#itemsOut": 397,
"#phaseSwitches": 1591,
"execTime": "6.087163ms",
"kernTime": "457.090394ms"
},
"aggregates": [
"max(cover ((`AR`.`LAST_SHOP_DTTM`)))",
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"(`DD`.`CALENDAR_DATE`)",
"(`CS`.`SUBSCRIBER_NAME`)"
],
"#time_normal": "00:00.0060",
"#time_absolute": 0.0060871630000000005
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 397,
"#itemsOut": 397,
"#phaseSwitches": 1591,
"execTime": "7.213323ms",
"kernTime": "463.292472ms"
},
"aggregates": [
"max(cover ((`AR`.`LAST_SHOP_DTTM`)))",
"max(cover ((`AR`.`RATE`)))",
"min(cover ((`AR`.`RATE`)))"
],
"group_keys": [
"cover ((`AR`.`K_PROPERTY`))",
"cover ((`AR`.`PROPERTY_NAME`))",
"cover ((`AR`.`SOURCE_CODE`))",
"cover ((`AR`.`GUESTS`))",
"cover ((`AR`.`LOS`))",
"cover ((`AR`.`ARRIVAL_DATE`))",
"(`DD`.`CALENDAR_DATE`)",
"(`CS`.`SUBSCRIBER_NAME`)"
],
"#time_normal": "00:00.0072",
"#time_absolute": 0.007213323
},
{
"#operator": "Parallel",
"#stats": {
"#phaseSwitches": 1,
"execTime": "12.388µs"
},
"copies": 4,
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 4,
"execTime": "3.298µs"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 397,
"#itemsOut": 397,
"#phaseSwitches": 1211,
"execTime": "4.849187ms",
"kernTime": "1.882403801s"
},
"result_terms": [
{
"as": "property_name",
"expr": "cover ((`AR`.`PROPERTY_NAME`))"
},
{
"as": "k_property",
"expr": "cover ((`AR`.`K_PROPERTY`))"
},
{
"expr": "(`CS`.`SUBSCRIBER_NAME`)"
},
{
"as": "source_code",
"expr": "cover ((`AR`.`SOURCE_CODE`))"
},
{
"as": "guests",
"expr": "cover ((`AR`.`GUESTS`))"
},
{
"as": "los",
"expr": "cover ((`AR`.`LOS`))"
},
{
"as": "arrival_date",
"expr": "cover ((`AR`.`ARRIVAL_DATE`))"
},
{
"as": "min_rate",
"expr": "min(cover ((`AR`.`RATE`)))"
},
{
"as": "max_rate",
"expr": "max(cover ((`AR`.`RATE`)))"
},
{
"as": "last_shop_dttm",
"expr": "max(cover ((`AR`.`LAST_SHOP_DTTM`)))"
},
{
"as": "asof_date",
"expr": "(`DD`.`CALENDAR_DATE`)"
}
],
"#time_normal": "00:00.0048",
"#time_absolute": 0.004849187
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 397,
"#itemsOut": 397,
"#phaseSwitches": 1195,
"execTime": "152.414µs",
"kernTime": "16.765263ms"
},
"#time_normal": "00:00.0001",
"#time_absolute": 0.000152414
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000003298
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000012388
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000036099999999999997
},
"#time_normal": "00:00.0017",
"#time_absolute": 0.001721611
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 397,
"#itemsOut": 397,
"#phaseSwitches": 1591,
"execTime": "70.115µs",
"kernTime": "477.847888ms"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.00007011499999999999
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.1-2037-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000003152
}If u expecting few ms it is not possible such complex query with many qualifying items.
@vsr1 - I see … so this is the max performance we can achieve ?
Also now the bucket size is 184 M and if this increases to 15 TB say , and if similar index is done should we see degraded performance in your experience ? or what measure we should take in that scenario , more replicas ? more partitions and more nodes ?
Also wonder what more parameters can be tweak-able in below ? to achieve greater query performance ?
Also in here . As I am certainly not feeling comfortable to change parameters here and there with my limited knowledge unless you guys advice .
thanks!
https://docs.couchbase.com/server/current/settings/query-settings.html
{
"completed-limit": 4000,
"completed-threshold": 1000,
"controls": false,
"cpuprofile": "",
"debug": false,
"keep-alive-length": 16384,
"loglevel": "INFO",
"max-index-api": 3,
"max-parallelism": 4,
"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": 192,
"timeout": 0
}
Since you are saying there are too many predicates in one indexes … Can we split the index into multiple and if that helps on performance ?