I am using N1QL query on 60K records with separate indexes on individual predicates and combined index on all predicates plus the metrics as ‘majorindex’ plus I have Primary key index as well.
I am querying 2 years of data with Performance with ~5 seconds performance and it declines to 12 seconds if I do 8 years .
As my dataset is not huge number of records can I get milliseconds perf on 7 years data ? and what is advice to improve if we can… because the total dataset will be millions later at some point of time… Here is how the plan looks like :
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.928µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.423µs",
"servTime": "4.847605ms"
},
"privileges": {
"List": [
{
"Target": "default:group360all",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.215µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.063µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.443µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 49659,
"#phaseSwitches": 198639,
"execTime": "141.225012ms",
"kernTime": "70.753111ms",
"servTime": "233.212792ms"
},
"as": "A",
"covers": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))",
"cover ((`A`.`SnapshotDate`))",
"cover ((`A`.`BookingStatus`))",
"cover ((`A`.`ExternalMarketSegmentId`))",
"cover ((`A`.`ExternalBookedById`))",
"cover ((`A`.`RevenueType`))",
"cover ((`A`.`BlendedEventRevenueTotal`))",
"cover ((`A`.`FBRevenueTotal`))",
"cover ((`A`.`AVRevTotal`))",
"cover ((`A`.`OtherRevTotal`))",
"cover ((`A`.`BlendedRoomnightsTotal`))",
"cover ((`A`.`BookedSqft`))",
"cover ((`A`.`BlendedGuestroomRevenueTotal`))",
"cover ((meta(`A`).`id`))"
],
"index": "majorindex",
"index_id": "9e209fb4e5dc636",
"index_projection": {
"entry_keys": [
0,
1,
2,
3
]
},
"keyspace": "group360all",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"2020-12-31\"",
"inclusion": 3,
"low": "\"2013-01-01\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.3744",
"#time_absolute": 0.374437804
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.164µs"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 49659,
"#itemsOut": 23713,
"#phaseSwitches": 146747,
"execTime": "92.584995ms",
"kernTime": "352.618797ms"
},
"condition": "((cover ((`A`.`PatternDate`)) between \"2013-01-01\" and \"2020-12-31\") and (cover ((`A`.`BookingStatus`)) in [\"Definite\"]))",
"#time_normal": "00:00.0925",
"#time_absolute": 0.092584995
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 23713,
"#itemsOut": 14861,
"#phaseSwitches": 77151,
"execTime": "242.446105ms",
"kernTime": "288.832205ms"
},
"aggregates": [
"max(cover ((`A`.`SnapshotDate`)))"
],
"group_keys": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))"
],
"#time_normal": "00:00.2424",
"#time_absolute": 0.242446105
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001164
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 14861,
"#phaseSwitches": 59447,
"execTime": "86.933473ms",
"kernTime": "568.319044ms"
},
"aggregates": [
"max(cover ((`A`.`SnapshotDate`)))"
],
"group_keys": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))"
],
"#time_normal": "00:00.0869",
"#time_absolute": 0.08693347300000001
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 14861,
"#phaseSwitches": 59447,
"execTime": "126.191538ms",
"kernTime": "10.2519575s"
},
"aggregates": [
"max(cover ((`A`.`SnapshotDate`)))"
],
"group_keys": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))"
],
"#time_normal": "00:00.1261",
"#time_absolute": 0.126191538
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "591ns"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 14861,
"#phaseSwitches": 44588,
"execTime": "137.512707ms",
"kernTime": "669.095036ms"
},
"result_terms": [
{
"as": "SnapshotDate",
"expr": "max(cover ((`A`.`SnapshotDate`)))"
},
{
"as": "ExternalBookingId",
"expr": "cover ((`A`.`ExternalBookingId`))"
},
{
"expr": "cover ((`A`.`PatternDate`))"
}
],
"#time_normal": "00:00.1375",
"#time_absolute": 0.137512707
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 14861,
"#phaseSwitches": 44584,
"execTime": "12.843561ms",
"kernTime": "9.937198722s"
},
"#time_normal": "00:00.0128",
"#time_absolute": 0.012843561
}
],
"#time_normal": "00:00",
"#time_absolute": 0
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001443
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 14861,
"#phaseSwitches": 59447,
"execTime": "51.435915ms",
"kernTime": "11.086336098s"
},
"as": "T",
"#time_normal": "00:00.0514",
"#time_absolute": 0.051435915
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.819µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 14861,
"#phaseSwitches": 148613,
"execTime": "281.358725ms",
"kernTime": "11.146701654s"
},
"alias": "M",
"on_clause": "(((cover ((`M`.`ExternalBookingId`)) = (`T`.`ExternalBookingId`)) and (cover ((`M`.`SnapshotDate`)) = (`T`.`SnapshotDate`))) and ((`T`.`PatternDate`) = cover ((`M`.`PatternDate`))))",
"~child": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 14861,
"#phaseSwitches": 133749,
"execTime": "260.568638ms",
"kernTime": "30.735006ms",
"servTime": "10.117377641s"
},
"as": "M",
"covers": [
"cover ((`M`.`PatternDate`))",
"cover ((`M`.`ExternalBookingId`))",
"cover ((`M`.`SnapshotDate`))",
"cover ((`M`.`BookingStatus`))",
"cover ((`M`.`ExternalMarketSegmentId`))",
"cover ((`M`.`ExternalBookedById`))",
"cover ((`M`.`RevenueType`))",
"cover ((`M`.`BlendedEventRevenueTotal`))",
"cover ((`M`.`FBRevenueTotal`))",
"cover ((`M`.`AVRevTotal`))",
"cover ((`M`.`OtherRevTotal`))",
"cover ((`M`.`BlendedRoomnightsTotal`))",
"cover ((`M`.`BookedSqft`))",
"cover ((`M`.`BlendedGuestroomRevenueTotal`))",
"cover ((meta(`M`).`id`))"
],
"index": "majorindex",
"index_id": "9e209fb4e5dc636",
"index_projection": {
"entry_keys": [
0,
1,
2,
3,
7,
8,
9,
10,
11,
12,
13
],
"primary_key": true
},
"keyspace": "group360all",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`T`.`PatternDate`)",
"inclusion": 3,
"low": "(`T`.`PatternDate`)"
},
{
"high": "(`T`.`ExternalBookingId`)",
"inclusion": 3,
"low": "(`T`.`ExternalBookingId`)"
},
{
"high": "(`T`.`SnapshotDate`)",
"inclusion": 3,
"low": "(`T`.`SnapshotDate`)"
}
]
}
],
"using": "gsi"
},
"#time_normal": "00:00.2813",
"#time_absolute": 0.281358725
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 14861,
"#itemsOut": 2417,
"#phaseSwitches": 34559,
"execTime": "721.692756ms",
"kernTime": "10.715754821s"
},
"aggregates": [
"sum(case when (cover ((`M`.`BookingStatus`)) = \"Definite\") then cover ((`M`.`BlendedRoomnightsTotal`)) else 0 end)",
"sum(cover ((`M`.`AVRevTotal`)))",
"sum(cover ((`M`.`BlendedEventRevenueTotal`)))",
"sum(cover ((`M`.`BlendedGuestroomRevenueTotal`)))",
"sum(cover ((`M`.`BlendedRoomnightsTotal`)))",
"sum(cover ((`M`.`BookedSqft`)))",
"sum(cover ((`M`.`FBRevenueTotal`)))",
"sum(cover ((`M`.`OtherRevTotal`)))"
],
"group_keys": [
"cover ((`M`.`PatternDate`))"
],
"#time_normal": "00:00.7216",
"#time_absolute": 0.721692756
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000018189999999999999
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 2417,
"#itemsOut": 2417,
"#phaseSwitches": 9671,
"execTime": "11.308748ms",
"kernTime": "11.560802913s"
},
"aggregates": [
"sum(case when (cover ((`M`.`BookingStatus`)) = \"Definite\") then cover ((`M`.`BlendedRoomnightsTotal`)) else 0 end)",
"sum(cover ((`M`.`AVRevTotal`)))",
"sum(cover ((`M`.`BlendedEventRevenueTotal`)))",
"sum(cover ((`M`.`BlendedGuestroomRevenueTotal`)))",
"sum(cover ((`M`.`BlendedRoomnightsTotal`)))",
"sum(cover ((`M`.`BookedSqft`)))",
"sum(cover ((`M`.`FBRevenueTotal`)))",
"sum(cover ((`M`.`OtherRevTotal`)))"
],
"group_keys": [
"cover ((`M`.`PatternDate`))"
],
"#time_normal": "00:00.0113",
"#time_absolute": 0.011308748
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 2417,
"#itemsOut": 2417,
"#phaseSwitches": 9671,
"execTime": "141.56248ms",
"kernTime": "11.501624877s"
},
"aggregates": [
"sum(case when (cover ((`M`.`BookingStatus`)) = \"Definite\") then cover ((`M`.`BlendedRoomnightsTotal`)) else 0 end)",
"sum(cover ((`M`.`AVRevTotal`)))",
"sum(cover ((`M`.`BlendedEventRevenueTotal`)))",
"sum(cover ((`M`.`BlendedGuestroomRevenueTotal`)))",
"sum(cover ((`M`.`BlendedRoomnightsTotal`)))",
"sum(cover ((`M`.`BookedSqft`)))",
"sum(cover ((`M`.`FBRevenueTotal`)))",
"sum(cover ((`M`.`OtherRevTotal`)))"
],
"group_keys": [
"cover ((`M`.`PatternDate`))"
],
"#time_normal": "00:00.1415",
"#time_absolute": 0.14156248
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 2417,
"#itemsOut": 2417,
"#phaseSwitches": 9671,
"execTime": "76.224233ms",
"kernTime": "11.585662899s"
},
"result_terms": [
{
"as": "patterndate",
"expr": "cover ((`M`.`PatternDate`))"
},
{
"as": "funcroomrev",
"expr": "round(sum(cover ((`M`.`BlendedEventRevenueTotal`))), 2)"
},
{
"as": "fnbrev",
"expr": "round(sum(cover ((`M`.`FBRevenueTotal`))), 2)"
},
{
"as": "avrev",
"expr": "round(sum(cover ((`M`.`AVRevTotal`))), 2)"
},
{
"as": "otherrev",
"expr": "round(sum(cover ((`M`.`OtherRevTotal`))), 2)"
},
{
"as": "funcrevpergrprn",
"expr": "round((sum(cover ((`M`.`BlendedEventRevenueTotal`))) / sum(cover ((`M`.`BlendedRoomnightsTotal`)))), 2)"
},
{
"as": "funcrevperbkdsqft",
"expr": "round((sum(cover ((`M`.`BlendedEventRevenueTotal`))) / sum(cover ((`M`.`BookedSqft`)))), 2)"
},
{
"as": "funcrevpersqft",
"expr": "round((sum(cover ((`M`.`BlendedEventRevenueTotal`))) / 1000), 2)"
},
{
"as": "grprnleadvolume",
"expr": "sum(cover ((`M`.`BlendedRoomnightsTotal`)))"
},
{
"as": "conversion",
"expr": "round((sum(case when (cover ((`M`.`BookingStatus`)) = \"Definite\") then cover ((`M`.`BlendedRoomnightsTotal`)) else 0 end) / sum(cover ((`M`.`BlendedRoomnightsTotal`)))), 3)"
},
{
"as": "grprn",
"expr": "sum(cover ((`M`.`BlendedRoomnightsTotal`)))"
},
{
"as": "grproomrev",
"expr": "round(sum(cover ((`M`.`BlendedGuestroomRevenueTotal`))), 2)"
},
{
"as": "grpadr",
"expr": "round((sum(cover ((`M`.`BlendedGuestroomRevenueTotal`))) / sum(cover ((`M`.`BlendedRoomnightsTotal`)))), 0)"
}
],
"#time_normal": "00:00.0762",
"#time_absolute": 0.076224233
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000003063
},
{
"#operator": "Order",
"#stats": {
"#itemsIn": 2417,
"#itemsOut": 2417,
"#phaseSwitches": 7256,
"execTime": "7.502165ms",
"kernTime": "11.661522773s"
},
"sort_terms": [
{
"expr": "cover ((`M`.`PatternDate`))"
}
],
"#time_normal": "00:00.0075",
"#time_absolute": 0.007502165
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 2417,
"#itemsOut": 2417,
"#phaseSwitches": 7252,
"execTime": "1.846574ms",
"kernTime": "50.797425ms"
},
"#time_normal": "00:00.0018",
"#time_absolute": 0.001846574
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002215
},
"#time_normal": "00:00.0048",
"#time_absolute": 0.004850027999999999
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 2417,
"#itemsOut": 2417,
"#phaseSwitches": 9671,
"execTime": "478.605µs",
"kernTime": "11.726507402s"
},
"#time_normal": "00:00.0004",
"#time_absolute": 0.000478605
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.1-2037-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001928
}
And I have majorindex as :
CREATE INDEX majorindex ON group360all(PatternDate,ExternalBookingId,SnapshotDate,BookingStatus,ExternalMarketSegmentId,ExternalBookedById,RevenueType,BlendedEventRevenueTotal,FBRevenueTotal,AVRevTotal,OtherRevTotal,BlendedRoomnightsTotal,BookedSqft,BlendedGuestroomRevenueTotal)
thanks