Slow Query response on 280K data

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
}

It looks like it used differnt index. Check other Indexes you have provide the hint.
Index that has this keys (ExternalLocationId , PatternDate, …)

@vsr1 - I have other 4 index as :
CREATE INDEX group360_ExternalBookingId ON group360(ExternalBookingId)
CREATE INDEX group360_PatternDate ON group360(PatternDate)
CREATE INDEX group360_SnapshotDate ON group360(SnapshotDate)
CREATE PRIMARY INDEX group360_primary ON group360

I have to create those otherwise getting ANSI Term Join error .
are you saying I need to define Index in different order ?
Trying to understand what Indexing rule it violates which makes query so slow.

CREATE INDEX ix1 ON group360(ExternalLocationId, PatternDate, SnapshotDate, BookingStatus, BlendedRoomnightsTotal);

https://index-advisor.couchbase.com/indexadvisor/#1

Thanks , tried that no good … but best I could get it is 5 seconds still not great . I was playing around different index strategy:

This is Index and Plan :

CREATE INDEX GROUP360_INDEX_OUTER ON group360(ExternalLocationId,PatternDate,SnapshotDate,ExternalBookingId)

CREATE INDEX GROUP360_INDEX_TEST ON group360(SnapshotDate,ExternalBookingId,PatternDate,BookingStatus,BlendedRoomnightsTotal)

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "2.939µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "6.929µs",
        "servTime": "1.215523ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:group360",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "2.959µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "4.678µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 12845,
                  "#phaseSwitches": 51383,
                  "execTime": "26.098867ms",
                  "kernTime": "2.300746ms",
                  "servTime": "185.976914ms"
                },
                "as": "A",
                "covers": [
                  "cover ((`A`.`ExternalLocationId`))",
                  "cover ((`A`.`PatternDate`))",
                  "cover ((`A`.`SnapshotDate`))",
                  "cover ((`A`.`ExternalBookingId`))",
                  "cover ((meta(`A`).`id`))",
                  "cover (max(cover ((`A`.`SnapshotDate`))))"
                ],
                "index": "GROUP360_INDEX_OUTER",
                "index_group_aggs": {
                  "aggregates": [
                    {
                      "aggregate": "MAX",
                      "depends": [
                        2
                      ],
                      "expr": "cover ((`A`.`SnapshotDate`))",
                      "id": 5,
                      "keypos": 2
                    }
                  ],
                  "depends": [
                    1,
                    2,
                    3
                  ],
                  "group": [
                    {
                      "depends": [
                        1
                      ],
                      "expr": "cover ((`A`.`PatternDate`))",
                      "id": 1,
                      "keypos": 1
                    },
                    {
                      "depends": [
                        3
                      ],
                      "expr": "cover ((`A`.`ExternalBookingId`))",
                      "id": 3,
                      "keypos": 3
                    }
                  ],
                  "partial": true
                },
                "index_id": "feb27e7f81970650",
                "index_projection": {
                  "entry_keys": [
                    1,
                    3,
                    5
                  ]
                },
                "keyspace": "group360",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"a0Y1N00000DKUnTUAX\"",
                        "inclusion": 3,
                        "low": "\"a0Y1N00000DKUnTUAX\""
                      },
                      {
                        "high": "\"2018-12-31\"",
                        "inclusion": 3,
                        "low": "\"2018-01-01\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.212",
                "#time_absolute": 0.212075781
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 12845,
                  "#itemsOut": 12653,
                  "#phaseSwitches": 50999,
                  "execTime": "79.971942ms",
                  "kernTime": "189.44918ms"
                },
                "aggregates": [
                  "max(cover (max(cover ((`A`.`SnapshotDate`)))))"
                ],
                "group_keys": [
                  "cover ((`A`.`PatternDate`))",
                  "cover ((`A`.`ExternalBookingId`))"
                ],
                "#time_normal": "00:00.079",
                "#time_absolute": 0.079971942
              },
              {
                "#operator": "IntermediateGroup",
                "#stats": {
                  "#itemsIn": 12653,
                  "#itemsOut": 12653,
                  "#phaseSwitches": 50615,
                  "execTime": "54.894615ms",
                  "kernTime": "294.302173ms"
                },
                "aggregates": [
                  "max(cover (max(cover ((`A`.`SnapshotDate`)))))"
                ],
                "group_keys": [
                  "cover ((`A`.`PatternDate`))",
                  "cover ((`A`.`ExternalBookingId`))"
                ],
                "#time_normal": "00:00.054",
                "#time_absolute": 0.054894615
              },
              {
                "#operator": "FinalGroup",
                "#stats": {
                  "#itemsIn": 12653,
                  "#itemsOut": 12653,
                  "#phaseSwitches": 50615,
                  "execTime": "81.394594ms",
                  "kernTime": "4.357055397s"
                },
                "aggregates": [
                  "max(cover (max(cover ((`A`.`SnapshotDate`)))))"
                ],
                "group_keys": [
                  "cover ((`A`.`PatternDate`))",
                  "cover ((`A`.`ExternalBookingId`))"
                ],
                "#time_normal": "00:00.081",
                "#time_absolute": 0.081394594
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "1.063µs"
                },
                "~children": [
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 12653,
                      "#itemsOut": 12653,
                      "#phaseSwitches": 37964,
                      "execTime": "83.921463ms",
                      "kernTime": "359.187756ms"
                    },
                    "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.083",
                    "#time_absolute": 0.083921463
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 12653,
                      "#itemsOut": 12653,
                      "#phaseSwitches": 37960,
                      "execTime": "11.363828ms",
                      "kernTime": "4.165753374s"
                    },
                    "#time_normal": "00:00.011",
                    "#time_absolute": 0.011363828
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000001063
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000004678
          },
          {
            "#operator": "Alias",
            "#stats": {
              "#itemsIn": 12653,
              "#itemsOut": 12653,
              "#phaseSwitches": 50615,
              "execTime": "32.524328ms",
              "kernTime": "4.777894117s"
            },
            "as": "T",
            "#time_normal": "00:00.032",
            "#time_absolute": 0.032524328
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "4.809µs"
            },
            "~children": [
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 12653,
                  "#itemsOut": 12653,
                  "#phaseSwitches": 126533,
                  "execTime": "114.797145ms",
                  "kernTime": "4.879654066s"
                },
                "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": 12653,
                    "#phaseSwitches": 113877,
                    "execTime": "207.534823ms",
                    "kernTime": "24.876648ms",
                    "servTime": "4.23983158s"
                  },
                  "as": "M",
                  "covers": [
                    "cover ((`M`.`SnapshotDate`))",
                    "cover ((`M`.`ExternalBookingId`))",
                    "cover ((`M`.`PatternDate`))",
                    "cover ((`M`.`BookingStatus`))",
                    "cover ((`M`.`BlendedRoomnightsTotal`))",
                    "cover ((meta(`M`).`id`))"
                  ],
                  "index": "GROUP360_INDEX_TEST",
                  "index_id": "b697ed4a7a5611d2",
                  "index_projection": {
                    "entry_keys": [
                      0,
                      1,
                      2,
                      4
                    ],
                    "primary_key": true
                  },
                  "keyspace": "group360",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "(`T`.`SnapshotDate`)",
                          "inclusion": 3,
                          "low": "(`T`.`SnapshotDate`)"
                        },
                        {
                          "high": "(`T`.`ExternalBookingId`)",
                          "inclusion": 3,
                          "low": "(`T`.`ExternalBookingId`)"
                        },
                        {
                          "high": "(`T`.`PatternDate`)",
                          "inclusion": 3,
                          "low": "(`T`.`PatternDate`)"
                        },
                        {
                          "inclusion": 0
                        },
                        {
                          "inclusion": 0,
                          "low": "null"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.114",
                "#time_absolute": 0.114797145
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 12653,
                  "#itemsOut": 1,
                  "#phaseSwitches": 25311,
                  "execTime": "65.794013ms",
                  "kernTime": "4.928674174s"
                },
                "aggregates": [
                  "sum(cover ((`M`.`BlendedRoomnightsTotal`)))"
                ],
                "group_keys": [],
                "#time_normal": "00:00.065",
                "#time_absolute": 0.06579401300000001
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000004809
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "7.647µs",
              "kernTime": "4.994481002s"
            },
            "aggregates": [
              "sum(cover ((`M`.`BlendedRoomnightsTotal`)))"
            ],
            "group_keys": [],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000007647
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "21.07µs",
              "kernTime": "4.994493048s"
            },
            "aggregates": [
              "sum(cover ((`M`.`BlendedRoomnightsTotal`)))"
            ],
            "group_keys": [],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.00002107
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "5.185µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 8,
                  "execTime": "32.546µs",
                  "kernTime": "4.994517312s"
                },
                "result_terms": [
                  {
                    "as": "grprnleadvolume",
                    "expr": "round(sum(cover ((`M`.`BlendedRoomnightsTotal`))))"
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000032545999999999996
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 4,
                  "execTime": "2.703µs",
                  "kernTime": "4.734µs"
                },
                "#time_normal": "00:00.000",
                "#time_absolute": 0.0000027029999999999998
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.0000051849999999999995
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000002959
      },
      "#time_normal": "00:00.001",
      "#time_absolute": 0.0012224519999999997
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1,
        "#itemsOut": 1,
        "#phaseSwitches": 5,
        "execTime": "27.366µs",
        "kernTime": "4.995796563s"
      },
      "#time_normal": "00:00.000",
      "#time_absolute": 0.000027366
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.5.0-4960-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000002939
}

Depends on data see if you can avoid JOIN and get same results.

   CREATE INDEX GROUP360_INDEX_OUTER ON group360(ExternalLocationId,PatternDate,ExternalBookingId, 
SnapshotDate, BookingStatus, BlendedRoomnightsTotal);


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, {A.BlendedRoomnightsTotal, A.BookingStatus}])[1].* ,
     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 )  AS M;
1 Like

Great ! This definitely works with 700 ms performance but this is a sample example query and we have 12 other queries and some of them more complicated so wonder if any other ways to achieve same performance with Joins rather changing all queries .

JOINS take time because it needs to use NL or HASH JOIN and expands rows.

okay thanks. N1QL is still inefficient in this regard I guess comparing to Relational DB.

hi , is there any significance you have added Index column “ExternalBookingId” twice ? or is this only typo

It is typo and fixed it

Many endpoints on reddit use the same protocol for controlling pagination and filtering. These endpoints are called Listings and share five common parameters: after / before , limit , count , and show .

Listings do not use page numbers because their content changes so frequently. Instead, they allow you to view slices of the underlying data. Listing JSON responses contain after and before fields which are equivalent to the “next” and “prev” buttons on the site and in combination with count can be used to page through the listing.

The common parameters are as follows:

  • after / before - only one should be specified. these indicate the fullname of an item in the listing to use as the anchor point of the slice.
  • limit - the maximum number of items to return in this slice of the listing.
  • count - the number of items already seen in this listing. on the html site, the builder uses this to determine when to give values for before and after in the response.
  • show - optional parameter; if all is passed, filters such as “hide links that I have voted on” will be disabled.

To page through a listing, start by fetching the first page without specifying values for after and count . The response will contain an after value which you can pass in the next request. It is a good idea, but not required, to send an updated value for count which should be the number of items already fetched.

How this is related with my threads and questions… ?