Hi ,
I have below query which took 49 seconds to return the data which is not obviously acceptable :
I have cover index created based on Rules of predicates order : Any idea if we can get improved performance in Mili seconds ?
SELECT ROUND(SUM(M.BlendedRoomnightsTotal)) grprnleadvolume,
100*ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM(
SELECT MAX(A.SnapshotDate) SnapshotDate,
A.ExternalBookingId ExternalBookingId,
A.PatternDate
FROM `group360` A
WHERE A.PatternDate BETWEEN '2018-01-01' AND '2018-12-31'
AND A.ExternalLocationId = 'a0Y1N00000DKUnTUAX'
GROUP BY A.PatternDate,
A.ExternalBookingId ) T
INNER JOIN `group360` M ON M.ExternalBookingId = T.ExternalBookingId
AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
This is Index definition : I have couple of 3 other simple index on the join keys :
CREATE INDEX GROUP360_COMP_INDEX ON group360
( ExternalLocationId , ExternalBookedById, ExternalBookingId , SnapshotDate , ExternalMarketSegmentId, BookingStatus , RevenueType , PatternDate ,
BlendedEventRevenueTotal , FBRevenueTotal , AVRevTotal , OtherRevTotal ,BlendedRoomnightsTotal , BlendedGuestroomRevenueTotal , AgreedRoomnightsTotal
)
And this is Explain PLAN:
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.247µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.445µs",
"servTime": "1.258512ms"
},
"privileges": {
"List": [
{
"Target": "default:group360",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.654µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "5.032µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 35707,
"#phaseSwitches": 142831,
"execTime": "61.10811ms",
"kernTime": "8.267968ms",
"servTime": "417.099854ms"
},
"as": "A",
"covers": [
"cover ((`A`.`ExternalLocationId`))",
"cover ((`A`.`ExternalBookedById`))",
"cover ((`A`.`ExternalBookingId`))",
"cover ((`A`.`SnapshotDate`))",
"cover ((`A`.`ExternalMarketSegmentId`))",
"cover ((`A`.`BookingStatus`))",
"cover ((`A`.`RevenueType`))",
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`BlendedEventRevenueTotal`))",
"cover ((`A`.`FBRevenueTotal`))",
"cover ((`A`.`AVRevTotal`))",
"cover ((`A`.`OtherRevTotal`))",
"cover ((`A`.`BlendedRoomnightsTotal`))",
"cover ((`A`.`BlendedGuestroomRevenueTotal`))",
"cover ((`A`.`AgreedRoomnightsTotal`))",
"cover ((meta(`A`).`id`))",
"cover (max(cover ((`A`.`SnapshotDate`))))"
],
"index": "GROUP360_COMP_INDEX",
"index_group_aggs": {
"aggregates": [
{
"aggregate": "MAX",
"depends": [
3
],
"expr": "cover ((`A`.`SnapshotDate`))",
"id": 16,
"keypos": 3
}
],
"depends": [
2,
3,
7
],
"group": [
{
"depends": [
2
],
"expr": "cover ((`A`.`ExternalBookingId`))",
"id": 2,
"keypos": 2
},
{
"depends": [
7
],
"expr": "cover ((`A`.`PatternDate`))",
"id": 7,
"keypos": 7
}
],
"partial": true
},
"index_id": "70c2c6a1f2ad431b",
"index_projection": {
"entry_keys": [
2,
7,
16
]
},
"keyspace": "group360",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"a0Y1N00000DKUnTUAX\"",
"inclusion": 3,
"low": "\"a0Y1N00000DKUnTUAX\""
},
{
"inclusion": 0
},
{
"inclusion": 0
},
{
"inclusion": 0
},
{
"inclusion": 0
},
{
"inclusion": 0
},
{
"inclusion": 0
},
{
"high": "\"2018-12-31\"",
"inclusion": 3,
"low": "\"2018-01-01\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.478",
"#time_absolute": 0.478207964
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 35707,
"#itemsOut": 12653,
"#phaseSwitches": 96723,
"execTime": "176.519943ms",
"kernTime": "363.354488ms"
},
"aggregates": [
"max(cover (max(cover ((`A`.`SnapshotDate`)))))"
],
"group_keys": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))"
],
"#time_normal": "00:00.176",
"#time_absolute": 0.176519943
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 12653,
"#phaseSwitches": 50615,
"execTime": "52.365664ms",
"kernTime": "558.907735ms"
},
"aggregates": [
"max(cover (max(cover ((`A`.`SnapshotDate`)))))"
],
"group_keys": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))"
],
"#time_normal": "00:00.052",
"#time_absolute": 0.052365664
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 12653,
"#phaseSwitches": 50615,
"execTime": "73.876754ms",
"kernTime": "44.578710367s"
},
"aggregates": [
"max(cover (max(cover ((`A`.`SnapshotDate`)))))"
],
"group_keys": [
"cover ((`A`.`PatternDate`))",
"cover ((`A`.`ExternalBookingId`))"
],
"#time_normal": "00:00.073",
"#time_absolute": 0.073876754
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "402ns"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 12653,
"#phaseSwitches": 37964,
"execTime": "106.90366ms",
"kernTime": "622.281269ms"
},
"result_terms": [
{
"as": "SnapshotDate",
"expr": "max(cover (max(cover ((`A`.`SnapshotDate`)))))"
},
{
"as": "ExternalBookingId",
"expr": "cover ((`A`.`ExternalBookingId`))"
},
{
"expr": "cover ((`A`.`PatternDate`))"
}
],
"#time_normal": "00:00.106",
"#time_absolute": 0.10690366
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 12653,
"#phaseSwitches": 37960,
"execTime": "13.813387ms",
"kernTime": "46.046677585s"
},
"#time_normal": "00:00.013",
"#time_absolute": 0.013813387
}
],
"#time_normal": "00:00",
"#time_absolute": 0
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000005032
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 12653,
"#phaseSwitches": 50615,
"execTime": "40.093506ms",
"kernTime": "49.045557288s"
},
"as": "T",
"#time_normal": "00:00.040",
"#time_absolute": 0.040093506
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.846µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 12653,
"#phaseSwitches": 1547555,
"execTime": "23.079085472s",
"kernTime": "27.913584293s"
},
"alias": "M",
"on_clause": "((((`M`.`ExternalBookingId`) = (`T`.`ExternalBookingId`)) and ((`M`.`SnapshotDate`) = (`T`.`SnapshotDate`))) and ((`T`.`PatternDate`) = (`M`.`PatternDate`)))",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 25306,
"execTime": "3.831688529s",
"kernTime": "5.820938ms",
"state": "running"
},
"~children": [
{
"#operator": "IntersectScan",
"#stats": {
"#itemsIn": 1283907,
"#itemsOut": 723164,
"#phaseSwitches": 4128019,
"execTime": "1.408641069s",
"kernTime": "7.503888059s"
},
"scans": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 316087,
"#phaseSwitches": 1338624,
"execTime": "930.768709ms",
"kernTime": "195.665953ms",
"servTime": "6.732265745s"
},
"as": "M",
"index": "group360_ExternalBookingId",
"index_id": "552c3546a2c42d60",
"index_projection": {
"primary_key": true
},
"keyspace": "group360",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`T`.`ExternalBookingId`)",
"inclusion": 3,
"low": "(`T`.`ExternalBookingId`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 572981,
"#phaseSwitches": 2362825,
"execTime": "981.298736ms",
"kernTime": "290.144215ms",
"servTime": "6.069757656s"
},
"as": "M",
"index": "group360_SnapshotDate",
"index_id": "8a95ba44889ef78d",
"index_projection": {
"primary_key": true
},
"keyspace": "group360",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`T`.`SnapshotDate`)",
"inclusion": 3,
"low": "(`T`.`SnapshotDate`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 394839,
"#phaseSwitches": 1656479,
"execTime": "727.567978ms",
"kernTime": "241.046375ms",
"servTime": "6.880432233s"
},
"as": "M",
"index": "group360_PatternDate",
"index_id": "55fc1279c3027856",
"index_projection": {
"primary_key": true
},
"keyspace": "group360",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`T`.`PatternDate`)",
"inclusion": 3,
"low": "(`T`.`PatternDate`)"
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 723164,
"#itemsOut": 723164,
"#phaseSwitches": 3036349,
"execTime": "1.444712935s",
"kernTime": "9.051984713s",
"servTime": "36.044748225s"
},
"as": "M",
"keyspace": "group360",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:23.079",
"#time_absolute": 23.079085472
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 12653,
"#itemsOut": 1,
"#phaseSwitches": 25311,
"execTime": "587.705919ms",
"kernTime": "50.405005181s"
},
"aggregates": [
"sum((`M`.`BlendedRoomnightsTotal`))",
"sum(case when ((`M`.`BookingStatus`) = \"Definite\") then (`M`.`BlendedRoomnightsTotal`) else 0 end)"
],
"group_keys": [],
"#time_normal": "00:00.587",
"#time_absolute": 0.587705919
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000001846
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "10.769µs",
"kernTime": "50.992719539s"
},
"aggregates": [
"sum((`M`.`BlendedRoomnightsTotal`))",
"sum(case when ((`M`.`BookingStatus`) = \"Definite\") then (`M`.`BlendedRoomnightsTotal`) else 0 end)"
],
"group_keys": [],
"#time_normal": "00:00.000",
"#time_absolute": 0.000010769
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 7,
"execTime": "25.531µs",
"kernTime": "50.99273837s"
},
"aggregates": [
"sum((`M`.`BlendedRoomnightsTotal`))",
"sum(case when ((`M`.`BookingStatus`) = \"Definite\") then (`M`.`BlendedRoomnightsTotal`) else 0 end)"
],
"group_keys": [],
"#time_normal": "00:00.000",
"#time_absolute": 0.000025531
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.735µs"
},
"~children": [
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 8,
"execTime": "52.3µs",
"kernTime": "50.992766737s"
},
"result_terms": [
{
"as": "grprnleadvolume",
"expr": "round(sum((`M`.`BlendedRoomnightsTotal`)))"
},
{
"as": "conversion",
"expr": "(100 * round((sum(case when ((`M`.`BookingStatus`) = \"Definite\") then (`M`.`BlendedRoomnightsTotal`) else 0 end) / sum((`M`.`BlendedRoomnightsTotal`))), 3))"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000523
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 4,
"execTime": "7.079µs",
"kernTime": "3.942µs"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.0000070789999999999995
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000017350000000000001
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000002654
},
"#time_normal": "00:00.001",
"#time_absolute": 0.001260957
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 5,
"execTime": "32.631µs",
"kernTime": "50.994105241s"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000032631
}
],
"~versions": [
"2.0.0-N1QL",
"6.5.0-4960-enterprise"
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000002247
}