Performance Issue N1QL Query on 200M major bucket volume

Hi ,
I am having performance challenge on below query for 3 months data fetch . I have multiple buckets with Joins in between : The Major bucket is ALL_RATES with 200 M documents and H_LOCAL_COMP_SET with 2 M docs. Created required Covered Index , Primary Key indexes on all buckets . I have 4 Nodes couchbase cluster running in pretty decent high performing PureStorage with K8s. Each node having 10 GB Index Service memory quota, 20 GB Data service memory quota and 20 CPU limits to 75 . All 4 nodes having all services …
Admin setting for servicers and max-parallelism :

{"completed-limit":4000,"completed-threshold":1000,"controls":false,"cpuprofile":"","debug":false,"keep-alive-length":16384,"loglevel":"INFO","max-index-api":3,"max-parallelism":1,"memprofile":"","n1ql-feat-ctrl":0,"pipeline-batch":16,"pipeline-cap":512,"prepared-limit":16384,"pretty":false,"profile":"off","request-size-cap":67108864,"scan-cap":512,"servicers":224,"timeout":0}

Below is index plan : and query is below : taking 2.7 seconds …goal is in 100 concurrent multi-threaded session it should return in Ms … Currently Oracle is getting the same in same volume of data in .10 Secs which is decent . Any improvement suggestions please ?

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "9.149µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "2.591µs",
        "servTime": "1.44666ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:D_DATE",
            "Priv": 7
          },
          {
            "Target": "default:H_LOCAL_COMP_SET",
            "Priv": 7
          },
          {
            "Target": "default:ALL_RATES",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "3.269µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.488µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "17.747µs",
                  "kernTime": "1.544µs",
                  "servTime": "682.497µs"
                },
                "covers": [
                  "cover ((`D_DATE`.`CALENDAR_DATE`))",
                  "cover ((meta(`D_DATE`).`id`))"
                ],
                "index": "D_DATE_CALENDAR_DATE_INDEX",
                "index_id": "2a7dfe118d78e826",
                "index_projection": {
                  "entry_keys": [
                    0
                  ]
                },
                "keyspace": "D_DATE",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"2019-08-22\"",
                        "inclusion": 3,
                        "low": "\"2019-08-22\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.0007",
                "#time_absolute": 0.000700244
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "816ns"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 7,
                      "execTime": "22.145µs",
                      "kernTime": "708.788µs"
                    },
                    "condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-08-22\" and \"2019-08-22\")",
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000022145
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 8,
                      "execTime": "15.626µs",
                      "kernTime": "733.324µs"
                    },
                    "result_terms": [
                      {
                        "expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
                      }
                    ],
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000015626
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 4,
                      "execTime": "1.353µs",
                      "kernTime": "539ns"
                    },
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000001353
                  }
                ],
                "#time_normal": "00:00",
                "#time_absolute": 0
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000001488
          },
          {
            "#operator": "Alias",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "8.38µs",
              "kernTime": "758.599µs"
            },
            "as": "DD",
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000008380000000000001
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "2.592µs"
            },
            "~children": [
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 17,
                  "#phaseSwitches": 77,
                  "execTime": "61.057µs",
                  "kernTime": "1.674831ms"
                },
                "alias": "CS",
                "on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 17,
                    "#phaseSwitches": 73,
                    "execTime": "46.935µs",
                    "kernTime": "3.606µs",
                    "servTime": "845.218µs"
                  },
                  "as": "CS",
                  "covers": [
                    "cover ((`CS`.`K_SUBSCRIBER_PROPERTY`))",
                    "cover ((`CS`.`ENTERPRISE_ID`))",
                    "cover ((`CS`.`SET_NAME`))",
                    "cover ((`CS`.`IS_SUBSCRIBER`))",
                    "cover ((`CS`.`K_COMP_PROPERTY`))",
                    "cover ((meta(`CS`).`id`))"
                  ],
                  "index": "H_LOCAL_CS_NKEY_INDEX",
                  "index_id": "e756a6788a7d9c1f",
                  "keyspace": "H_LOCAL_COMP_SET",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "421000987",
                          "inclusion": 3,
                          "low": "421000987"
                        },
                        {
                          "high": "\"607384\"",
                          "inclusion": 3,
                          "low": "\"607384\""
                        },
                        {
                          "high": "\"Primary\"",
                          "inclusion": 3,
                          "low": "\"Primary\""
                        },
                        {
                          "high": "\"N\"",
                          "inclusion": 3,
                          "low": "\"N\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.0000",
                "#time_absolute": 0.00006105700000000001
              },
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 17,
                  "#itemsOut": 18083,
                  "#phaseSwitches": 72437,
                  "execTime": "141.592582ms",
                  "kernTime": "2.489108519s"
                },
                "alias": "AR",
                "on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 18083,
                    "#phaseSwitches": 72417,
                    "execTime": "80.27145ms",
                    "kernTime": "4.312155ms",
                    "servTime": "2.53903168s"
                  },
                  "as": "AR",
                  "covers": [
                    "cover ((`AR`.`K_PROPERTY`))",
                    "cover ((`AR`.`ARRIVAL_DATE`))",
                    "cover ((`AR`.`VALID_FROM_DTTM`))",
                    "cover ((`AR`.`VALID_TO_DTTM`))",
                    "cover ((`AR`.`LOS`))",
                    "cover ((`AR`.`GUESTS`))",
                    "cover ((`AR`.`SOURCE_CODE`))",
                    "cover ((`AR`.`SOURCE_NAME`))",
                    "cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
                    "cover ((`AR`.`LAST_SHOP_DTTM`))",
                    "cover ((`AR`.`RATE`))",
                    "cover ((`AR`.`PROPERTY_NAME`))",
                    "cover ((meta(`AR`).`id`))"
                  ],
                  "index": "ALL_RATES_DXP_INDEX",
                  "index_id": "18ee534b9af582c3",
                  "keyspace": "ALL_RATES",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "cover ((`CS`.`K_COMP_PROPERTY`))",
                          "inclusion": 3,
                          "low": "cover ((`CS`.`K_COMP_PROPERTY`))"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.1415",
                "#time_absolute": 0.141592582
              },
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 18083,
                  "#itemsOut": 8175,
                  "#phaseSwitches": 52519,
                  "execTime": "226.182251ms",
                  "kernTime": "2.40507214s"
                },
                "condition": "(((((((((((cover ((`CS`.`ENTERPRISE_ID`)) = \"607384\") and (cover ((`CS`.`SET_NAME`)) = \"Primary\")) and (cover ((`CS`.`IS_SUBSCRIBER`)) = \"N\")) and (cover ((`CS`.`K_SUBSCRIBER_PROPERTY`)) = 421000987)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-09-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_NAME`)) = \"Brand.com\")) and (cover ((`AR`.`GUESTS`)) = 1)) and (cover ((`AR`.`LOS`)) = 1)) and (cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`)) in [\"Apartment/Multi BR\", \"Deluxe\", \"Standard\", \"Suite\"])) and (cover ((`AR`.`VALID_FROM_DTTM`)) < date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\"))) and ((`DD`.`CALENDAR_DATE`) <= cover ((`AR`.`VALID_TO_DTTM`))))",
                "#time_normal": "00:00.2261",
                "#time_absolute": 0.226182251
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 748,
                  "#phaseSwitches": 17849,
                  "execTime": "149.570325ms",
                  "kernTime": "2.486629697s"
                },
                "aggregates": [
                  "max(cover ((`AR`.`RATE`)))",
                  "min(cover ((`AR`.`RATE`)))"
                ],
                "group_keys": [
                  "cover ((`AR`.`K_PROPERTY`))",
                  "cover ((`AR`.`PROPERTY_NAME`))",
                  "cover ((`AR`.`SOURCE_CODE`))",
                  "cover ((`AR`.`SOURCE_NAME`))",
                  "cover ((`AR`.`GUESTS`))",
                  "cover ((`AR`.`LOS`))",
                  "cover ((`AR`.`ARRIVAL_DATE`))",
                  "cover ((`AR`.`LAST_SHOP_DTTM`))",
                  "(`DD`.`CALENDAR_DATE`)"
                ],
                "#time_normal": "00:00.1495",
                "#time_absolute": 0.149570325
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000002592
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "12.154303ms",
              "kernTime": "2.637093681s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "(`DD`.`CALENDAR_DATE`)"
            ],
            "#time_normal": "00:00.0121",
            "#time_absolute": 0.012154303
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "13.622269ms",
              "kernTime": "2.648042956s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "(`DD`.`CALENDAR_DATE`)"
            ],
            "#time_normal": "00:00.0136",
            "#time_absolute": 0.013622269
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.676µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2249,
                  "execTime": "5.894799ms",
                  "kernTime": "2.658315038s"
                },
                "result_terms": [
                  {
                    "as": "property_name",
                    "expr": "cover ((`AR`.`PROPERTY_NAME`))"
                  },
                  {
                    "as": "k_property",
                    "expr": "cover ((`AR`.`K_PROPERTY`))"
                  },
                  {
                    "as": "source_code",
                    "expr": "cover ((`AR`.`SOURCE_CODE`))"
                  },
                  {
                    "as": "source_name",
                    "expr": "cover ((`AR`.`SOURCE_NAME`))"
                  },
                  {
                    "as": "guests",
                    "expr": "cover ((`AR`.`GUESTS`))"
                  },
                  {
                    "as": "los",
                    "expr": "cover ((`AR`.`LOS`))"
                  },
                  {
                    "as": "arrival_date",
                    "expr": "cover ((`AR`.`ARRIVAL_DATE`))"
                  },
                  {
                    "as": "last_shop_dttm",
                    "expr": "cover ((`AR`.`LAST_SHOP_DTTM`))"
                  },
                  {
                    "as": "asof_date",
                    "expr": "(`DD`.`CALENDAR_DATE`)"
                  },
                  {
                    "as": "min_rate",
                    "expr": "min(cover ((`AR`.`RATE`)))"
                  },
                  {
                    "as": "max_rate",
                    "expr": "max(cover ((`AR`.`RATE`)))"
                  }
                ],
                "#time_normal": "00:00.0058",
                "#time_absolute": 0.005894799
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2245,
                  "execTime": "237.709µs",
                  "kernTime": "6.99724ms"
                },
                "#time_normal": "00:00.0002",
                "#time_absolute": 0.000237709
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000001676
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000003269
      },
      "#time_normal": "00:00.0014",
      "#time_absolute": 0.001449251
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 748,
        "#itemsOut": 748,
        "#phaseSwitches": 2995,
        "execTime": "136.224µs",
        "kernTime": "2.672850809s"
      },
      "#time_normal": "00:00.0001",
      "#time_absolute": 0.000136224
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.1-2037-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.000009148999999999998
}

Query as below :

SELECT AR.PROPERTY_NAME property_name,
AR.K_PROPERTY k_property,
AR.SOURCE_CODE source_code,
AR.SOURCE_NAME source_name,
AR.GUESTS guests,
AR.LOS los,
/*AR.SHOP_STATUS,*/
AR.ARRIVAL_DATE arrival_date,
AR.LAST_SHOP_DTTM last_shop_dttm,
DD.CALENDAR_DATE asof_date,
MIN(AR.RATE) min_rate,
MAX(AR.RATE) max_rate
  FROM  (SELECT CALENDAR_DATE FROM D_DATE WHERE CALENDAR_DATE BETWEEN '2019-08-22' AND '2019-08-22') DD
        INNER JOIN H_LOCAL_COMP_SET CS
                ON DD.CALENDAR_DATE = DD.CALENDAR_DATE
        INNER JOIN ALL_RATES AR
                ON AR.K_PROPERTY = CS.K_COMP_PROPERTY
WHERE CS.ENTERPRISE_ID = '607384'
   AND CS.SET_NAME = 'Primary'
   AND CS.IS_SUBSCRIBER = 'N'
   AND CS.K_SUBSCRIBER_PROPERTY = 421000987
   AND AR.ARRIVAL_DATE BETWEEN '2019-09-01'  and '2019-12-31'
   AND AR.SOURCE_NAME = 'Brand.com'
   AND AR.GUESTS = 1
   AND AR.LOS = 1
   AND AR.ROOM_PRODUCT_DESCRIPTION IN  ['Apartment/Multi BR', 'Deluxe', 'Standard', 'Suite']
   AND AR.VALID_FROM_DTTM < date_add_str(DD.CALENDAR_DATE, 1, 'day')
   AND AR.VALID_TO_DTTM >= DD.CALENDAR_DATE
GROUP BY AR.K_PROPERTY,
AR.PROPERTY_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.LAST_SHOP_DTTM,
/* AR.SHOP_STATUS,*/
DD.CALENDAR_DATE

Covered Index on Huge bucket sample :

CREATE INDEX ALL_RATES_DXP_INDEX ON ALL_RATES ( K_PROPERTY , ARRIVAL_DATE , VALID_FROM_DTTM , VALID_TO_DTTM , LOS , GUESTS , SOURCE_CODE , SOURCE_NAME , ROOM_PRODUCT_DESCRIPTION , LAST_SHOP_DTTM , RATE , PROPERTY_NAME )

Check this condition.
JOINs are LEFT TO RIGHT change the order and try it.
Also if you are using EE try with HASH join https://blog.couchbase.com/ansi-join-support-n1ql/

SELECT AR.PROPERTY_NAME property_name,
       AR.K_PROPERTY k_property,
       AR.SOURCE_CODE source_code,
       AR.SOURCE_NAME source_name,
       AR.GUESTS guests,
       AR.LOS los,
       AR.ARRIVAL_DATE arrival_date,
       AR.LAST_SHOP_DTTM last_shop_dttm,
       DD.CALENDAR_DATE asof_date,
       MIN(AR.RATE) min_rate,
       MAX(AR.RATE) max_rate
FROM CALENDAR_DATE DD
INNER JOIN ALL_RATES AR ON AR.VALID_FROM_DTTM < date_add_str(DD.CALENDAR_DATE, 1, 'day') AND AR.VALID_TO_DTTM >= DD.CALENDAR_DATE
INNER JOIN H_LOCAL_COMP_SET CS ON AR.K_PROPERTY = CS.K_COMP_PROPERTY
WHERE DD.CALENDAR_DATE BETWEEN '2019-08-22' AND '2019-08-22'
   CS.ENTERPRISE_ID = '607384'
   AND CS.SET_NAME = 'Primary'
   AND CS.IS_SUBSCRIBER = 'N'
   AND CS.K_SUBSCRIBER_PROPERTY = 421000987
   AND AR.ARRIVAL_DATE BETWEEN '2019-09-01'  and '2019-12-31'
   AND AR.SOURCE_NAME = 'Brand.com'
   AND AR.GUESTS = 1
   AND AR.LOS = 1
   AND AR.ROOM_PRODUCT_DESCRIPTION IN  ['Apartment/Multi BR', 'Deluxe', 'Standard', 'Suite']
GROUP BY AR.K_PROPERTY,
AR.PROPERTY_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.LAST_SHOP_DTTM,
DD.CALENDAR_DATE;

CREATE INDEX ALL_RATES_DXP_INDEX ON ALL_RATES (SOURCE_NAME, GUESTS, LOS, ARRIVAL_DATE, VALID_FROM_DTTM, VALID_TO_DTTM,ROOM_PRODUCT_DESCRIPTION, K_PROPERTY ,  SOURCE_CODE ,  LAST_SHOP_DTTM , RATE , PROPERTY_NAME )

@vsr1 - First I tried with your Index suggestion approach and modified query …
I have few syntax errors which corrected as below : Took 1 minute to run !
Will swap around the Join conditions :

SELECT AR.PROPERTY_NAME property_name,
       AR.K_PROPERTY k_property,
       AR.SOURCE_CODE source_code,
       AR.SOURCE_NAME source_name,
       AR.GUESTS guests,
       AR.LOS los,
       AR.ARRIVAL_DATE arrival_date,
       AR.LAST_SHOP_DTTM last_shop_dttm,
       DD.CALENDAR_DATE asof_date,
       MIN(AR.RATE) min_rate,
       MAX(AR.RATE) max_rate
FROM D_DATE DD
INNER JOIN ALL_RATES AR ON AR.VALID_FROM_DTTM < date_add_str(DD.CALENDAR_DATE, 1, 'day') AND AR.VALID_TO_DTTM >= DD.CALENDAR_DATE
INNER JOIN H_LOCAL_COMP_SET CS ON AR.K_PROPERTY = CS.K_COMP_PROPERTY
WHERE DD.CALENDAR_DATE BETWEEN '2019-08-22' AND '2019-08-22'
   AND CS.ENTERPRISE_ID = '607384'
   AND CS.SET_NAME = 'Primary'
   AND CS.IS_SUBSCRIBER = 'N'
   AND CS.K_SUBSCRIBER_PROPERTY = 421000987
   AND AR.ARRIVAL_DATE BETWEEN '2019-09-01'  and '2019-12-31'
   AND AR.SOURCE_NAME = 'Brand.com'
   AND AR.GUESTS = 1
   AND AR.LOS = 1
   AND AR.ROOM_PRODUCT_DESCRIPTION IN  ['Apartment/Multi BR', 'Deluxe', 'Standard', 'Suite']
GROUP BY AR.K_PROPERTY,
AR.PROPERTY_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.LAST_SHOP_DTTM,
DD.CALENDAR_DATE 

Plan :

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "3.052µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "7.217µs",
        "servTime": "4.72635ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:D_DATE",
            "Priv": 7
          },
          {
            "Target": "default:ALL_RATES",
            "Priv": 7
          },
          {
            "Target": "default:H_LOCAL_COMP_SET",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "3.926µs"
        },
        "~children": [
          {
            "#operator": "IndexScan3",
            "#stats": {
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "21.813µs",
              "kernTime": "2.646µs",
              "servTime": "4.535774ms"
            },
            "as": "DD",
            "covers": [
              "cover ((`DD`.`CALENDAR_DATE`))",
              "cover ((meta(`DD`).`id`))"
            ],
            "index": "D_DATE_CALENDAR_DATE_INDEX",
            "index_id": "2a7dfe118d78e826",
            "keyspace": "D_DATE",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"2019-08-22\"",
                    "inclusion": 3,
                    "low": "\"2019-08-22\""
                  }
                ]
              }
            ],
            "using": "gsi",
            "#time_normal": "00:00.0045",
            "#time_absolute": 0.004557587
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "12.106µs"
            },
            "~children": [
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 254486,
                  "#phaseSwitches": 1017953,
                  "execTime": "7.145409126s",
                  "kernTime": "1m38.453995109s"
                },
                "alias": "AR",
                "on_clause": "((cover ((`AR`.`VALID_FROM_DTTM`)) < date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")) and (cover ((`DD`.`CALENDAR_DATE`)) <= cover ((`AR`.`VALID_TO_DTTM`))))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 254486,
                    "#phaseSwitches": 1017949,
                    "execTime": "1.709553257s",
                    "kernTime": "1m43.300348906s",
                    "servTime": "361.974767ms"
                  },
                  "as": "AR",
                  "covers": [
                    "cover ((`AR`.`SOURCE_NAME`))",
                    "cover ((`AR`.`GUESTS`))",
                    "cover ((`AR`.`LOS`))",
                    "cover ((`AR`.`ARRIVAL_DATE`))",
                    "cover ((`AR`.`VALID_FROM_DTTM`))",
                    "cover ((`AR`.`VALID_TO_DTTM`))",
                    "cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
                    "cover ((`AR`.`K_PROPERTY`))",
                    "cover ((`AR`.`SOURCE_CODE`))",
                    "cover ((`AR`.`LAST_SHOP_DTTM`))",
                    "cover ((`AR`.`RATE`))",
                    "cover ((`AR`.`PROPERTY_NAME`))",
                    "cover ((meta(`AR`).`id`))"
                  ],
                  "index": "ALL_RATES_DXP1_INDEX",
                  "index_id": "1c3e1624404c1040",
                  "keyspace": "ALL_RATES",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "cover ((`DD`.`CALENDAR_DATE`))"
                        },
                        {
                          "high": "\"Apartment/Multi BR\"",
                          "inclusion": 3,
                          "low": "\"Apartment/Multi BR\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "cover ((`DD`.`CALENDAR_DATE`))"
                        },
                        {
                          "high": "\"Deluxe\"",
                          "inclusion": 3,
                          "low": "\"Deluxe\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "cover ((`DD`.`CALENDAR_DATE`))"
                        },
                        {
                          "high": "\"Standard\"",
                          "inclusion": 3,
                          "low": "\"Standard\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "cover ((`DD`.`CALENDAR_DATE`))"
                        },
                        {
                          "high": "\"Suite\"",
                          "inclusion": 3,
                          "low": "\"Suite\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:07.1454",
                "#time_absolute": 7.145409126
              },
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 254486,
                  "#itemsOut": 8175,
                  "#phaseSwitches": 1559619,
                  "execTime": "598.390059ms",
                  "kernTime": "1m45.206477582s"
                },
                "alias": "CS",
                "on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 8175,
                    "#phaseSwitches": 1305133,
                    "execTime": "1.640707095s",
                    "kernTime": "263.642896ms",
                    "servTime": "1m42.479612976s"
                  },
                  "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\""
                        },
                        {
                          "high": "cover ((`AR`.`K_PROPERTY`))",
                          "inclusion": 3,
                          "low": "cover ((`AR`.`K_PROPERTY`))"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.5983",
                "#time_absolute": 0.598390059
              },
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 8175,
                  "#phaseSwitches": 32703,
                  "execTime": "116.546982ms",
                  "kernTime": "1m45.688328615s"
                },
                "condition": "((((((((((cover ((`DD`.`CALENDAR_DATE`)) between \"2019-08-22\" and \"2019-08-22\") and (cover ((`CS`.`ENTERPRISE_ID`)) = \"607384\")) and (cover ((`CS`.`SET_NAME`)) = \"Primary\")) and (cover ((`CS`.`IS_SUBSCRIBER`)) = \"N\")) and (cover ((`CS`.`K_SUBSCRIBER_PROPERTY`)) = 421000987)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-09-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_NAME`)) = \"Brand.com\")) and (cover ((`AR`.`GUESTS`)) = 1)) and (cover ((`AR`.`LOS`)) = 1)) and (cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`)) in [\"Apartment/Multi BR\", \"Deluxe\", \"Standard\", \"Suite\"]))",
                "#time_normal": "00:00.1165",
                "#time_absolute": 0.116546982
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 748,
                  "#phaseSwitches": 17849,
                  "execTime": "266.375674ms",
                  "kernTime": "1m45.543016156s"
                },
                "aggregates": [
                  "max(cover ((`AR`.`RATE`)))",
                  "min(cover ((`AR`.`RATE`)))"
                ],
                "group_keys": [
                  "cover ((`AR`.`K_PROPERTY`))",
                  "cover ((`AR`.`PROPERTY_NAME`))",
                  "cover ((`AR`.`SOURCE_CODE`))",
                  "cover ((`AR`.`SOURCE_NAME`))",
                  "cover ((`AR`.`GUESTS`))",
                  "cover ((`AR`.`LOS`))",
                  "cover ((`AR`.`ARRIVAL_DATE`))",
                  "cover ((`AR`.`LAST_SHOP_DTTM`))",
                  "cover ((`DD`.`CALENDAR_DATE`))"
                ],
                "#time_normal": "00:00.2663",
                "#time_absolute": 0.266375674
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000012106
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "12.338102ms",
              "kernTime": "1m45.812830141s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "cover ((`DD`.`CALENDAR_DATE`))"
            ],
            "#time_normal": "00:00.0123",
            "#time_absolute": 0.012338102
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "21.485703ms",
              "kernTime": "1m45.822579568s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "cover ((`DD`.`CALENDAR_DATE`))"
            ],
            "#time_normal": "00:00.0214",
            "#time_absolute": 0.021485703000000002
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.461µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2249,
                  "execTime": "8.63843ms",
                  "kernTime": "1m45.839627255s"
                },
                "result_terms": [
                  {
                    "as": "property_name",
                    "expr": "cover ((`AR`.`PROPERTY_NAME`))"
                  },
                  {
                    "as": "k_property",
                    "expr": "cover ((`AR`.`K_PROPERTY`))"
                  },
                  {
                    "as": "source_code",
                    "expr": "cover ((`AR`.`SOURCE_CODE`))"
                  },
                  {
                    "as": "source_name",
                    "expr": "cover ((`AR`.`SOURCE_NAME`))"
                  },
                  {
                    "as": "guests",
                    "expr": "cover ((`AR`.`GUESTS`))"
                  },
                  {
                    "as": "los",
                    "expr": "cover ((`AR`.`LOS`))"
                  },
                  {
                    "as": "arrival_date",
                    "expr": "cover ((`AR`.`ARRIVAL_DATE`))"
                  },
                  {
                    "as": "last_shop_dttm",
                    "expr": "cover ((`AR`.`LAST_SHOP_DTTM`))"
                  },
                  {
                    "as": "asof_date",
                    "expr": "cover ((`DD`.`CALENDAR_DATE`))"
                  },
                  {
                    "as": "min_rate",
                    "expr": "min(cover ((`AR`.`RATE`)))"
                  },
                  {
                    "as": "max_rate",
                    "expr": "max(cover ((`AR`.`RATE`)))"
                  }
                ],
                "#time_normal": "00:00.0086",
                "#time_absolute": 0.008638429999999999
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2245,
                  "execTime": "341.757µs",
                  "kernTime": "5.561787ms"
                },
                "#time_normal": "00:00.0003",
                "#time_absolute": 0.000341757
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000001461
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000003926
      },
      "#time_normal": "00:00.0047",
      "#time_absolute": 0.004733567
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 748,
        "#itemsOut": 748,
        "#phaseSwitches": 2995,
        "execTime": "161.214µs",
        "kernTime": "1m45.858868471s"
      },
      "#time_normal": "00:00.0001",
      "#time_absolute": 0.000161214
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.1-2037-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.000003052
}

Also with USE HASH it returned in 8.7 Secs … Still not great .

EXPLAIN PLAN:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "1.708µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "7.548µs",
        "servTime": "1.141135ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:D_DATE",
            "Priv": 7
          },
          {
            "Target": "default:ALL_RATES",
            "Priv": 7
          },
          {
            "Target": "default:H_LOCAL_COMP_SET",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "9.031µs"
        },
        "~children": [
          {
            "#operator": "IndexScan3",
            "#stats": {
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "16.618µs",
              "kernTime": "6.389µs",
              "servTime": "836.483µs"
            },
            "as": "DD",
            "covers": [
              "cover ((`DD`.`CALENDAR_DATE`))",
              "cover ((meta(`DD`).`id`))"
            ],
            "index": "D_DATE_CALENDAR_DATE_INDEX",
            "index_id": "2a7dfe118d78e826",
            "keyspace": "D_DATE",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"2019-08-22\"",
                    "inclusion": 3,
                    "low": "\"2019-08-22\""
                  }
                ]
              }
            ],
            "using": "gsi",
            "#time_normal": "00:00.0008",
            "#time_absolute": 0.000853101
          },
          {
            "#operator": "NestedLoopJoin",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 254486,
              "#phaseSwitches": 1017953,
              "execTime": "4.541433556s",
              "kernTime": "3.788760171s"
            },
            "alias": "AR",
            "on_clause": "((cover ((`AR`.`VALID_FROM_DTTM`)) < date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")) and (cover ((`DD`.`CALENDAR_DATE`)) <= cover ((`AR`.`VALID_TO_DTTM`))))",
            "~child": {
              "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 254486,
                "#phaseSwitches": 1017949,
                "execTime": "1.548742191s",
                "kernTime": "1.315370268s",
                "servTime": "5.465203858s"
              },
              "as": "AR",
              "covers": [
                "cover ((`AR`.`SOURCE_NAME`))",
                "cover ((`AR`.`GUESTS`))",
                "cover ((`AR`.`LOS`))",
                "cover ((`AR`.`ARRIVAL_DATE`))",
                "cover ((`AR`.`VALID_FROM_DTTM`))",
                "cover ((`AR`.`VALID_TO_DTTM`))",
                "cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
                "cover ((`AR`.`K_PROPERTY`))",
                "cover ((`AR`.`SOURCE_CODE`))",
                "cover ((`AR`.`LAST_SHOP_DTTM`))",
                "cover ((`AR`.`RATE`))",
                "cover ((`AR`.`PROPERTY_NAME`))",
                "cover ((meta(`AR`).`id`))"
              ],
              "index": "ALL_RATES_DXP1_INDEX",
              "index_id": "1c3e1624404c1040",
              "keyspace": "ALL_RATES",
              "namespace": "default",
              "nested_loop": true,
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"Brand.com\"",
                      "inclusion": 3,
                      "low": "\"Brand.com\""
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "\"2019-12-31\"",
                      "inclusion": 3,
                      "low": "\"2019-09-01\""
                    },
                    {
                      "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                      "inclusion": 0,
                      "low": "null"
                    },
                    {
                      "inclusion": 1,
                      "low": "cover ((`DD`.`CALENDAR_DATE`))"
                    },
                    {
                      "high": "\"Apartment/Multi BR\"",
                      "inclusion": 3,
                      "low": "\"Apartment/Multi BR\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"Brand.com\"",
                      "inclusion": 3,
                      "low": "\"Brand.com\""
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "\"2019-12-31\"",
                      "inclusion": 3,
                      "low": "\"2019-09-01\""
                    },
                    {
                      "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                      "inclusion": 0,
                      "low": "null"
                    },
                    {
                      "inclusion": 1,
                      "low": "cover ((`DD`.`CALENDAR_DATE`))"
                    },
                    {
                      "high": "\"Deluxe\"",
                      "inclusion": 3,
                      "low": "\"Deluxe\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"Brand.com\"",
                      "inclusion": 3,
                      "low": "\"Brand.com\""
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "\"2019-12-31\"",
                      "inclusion": 3,
                      "low": "\"2019-09-01\""
                    },
                    {
                      "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                      "inclusion": 0,
                      "low": "null"
                    },
                    {
                      "inclusion": 1,
                      "low": "cover ((`DD`.`CALENDAR_DATE`))"
                    },
                    {
                      "high": "\"Standard\"",
                      "inclusion": 3,
                      "low": "\"Standard\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"Brand.com\"",
                      "inclusion": 3,
                      "low": "\"Brand.com\""
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "\"2019-12-31\"",
                      "inclusion": 3,
                      "low": "\"2019-09-01\""
                    },
                    {
                      "high": "date_add_str(cover ((`DD`.`CALENDAR_DATE`)), 1, \"day\")",
                      "inclusion": 0,
                      "low": "null"
                    },
                    {
                      "inclusion": 1,
                      "low": "cover ((`DD`.`CALENDAR_DATE`))"
                    },
                    {
                      "high": "\"Suite\"",
                      "inclusion": 3,
                      "low": "\"Suite\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            },
            "#time_normal": "00:04.5414",
            "#time_absolute": 4.541433556
          },
          {
            "#operator": "HashJoin",
            "#stats": {
              "#itemsIn": 254486,
              "#itemsOut": 8175,
              "#phaseSwitches": 525363,
              "execTime": "348.965842ms",
              "kernTime": "7.982476423s"
            },
            "build_aliases": [
              "CS"
            ],
            "build_exprs": [
              "cover ((`CS`.`K_COMP_PROPERTY`))"
            ],
            "on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
            "probe_exprs": [
              "cover ((`AR`.`K_PROPERTY`))"
            ],
            "~child": {
              "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 17,
                "#phaseSwitches": 73,
                "execTime": "102.409µs",
                "kernTime": "3.532µs",
                "servTime": "1.082608ms"
              },
              "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",
              "spans": [
                {
                  "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\""
                    },
                    {
                      "inclusion": 0,
                      "low": "null"
                    }
                  ]
                }
              ],
              "using": "gsi"
            },
            "#time_normal": "00:00.3489",
            "#time_absolute": 0.348965842
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "10.436µs"
            },
            "~children": [
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 8175,
                  "#phaseSwitches": 32703,
                  "execTime": "61.679746ms",
                  "kernTime": "8.269771187s"
                },
                "condition": "((((((((((cover ((`DD`.`CALENDAR_DATE`)) between \"2019-08-22\" and \"2019-08-22\") and (cover ((`CS`.`ENTERPRISE_ID`)) = \"607384\")) and (cover ((`CS`.`SET_NAME`)) = \"Primary\")) and (cover ((`CS`.`IS_SUBSCRIBER`)) = \"N\")) and (cover ((`CS`.`K_SUBSCRIBER_PROPERTY`)) = 421000987)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-09-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_NAME`)) = \"Brand.com\")) and (cover ((`AR`.`GUESTS`)) = 1)) and (cover ((`AR`.`LOS`)) = 1)) and (cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`)) in [\"Apartment/Multi BR\", \"Deluxe\", \"Standard\", \"Suite\"]))",
                "#time_normal": "00:00.0616",
                "#time_absolute": 0.061679746
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 748,
                  "#phaseSwitches": 17849,
                  "execTime": "190.312676ms",
                  "kernTime": "8.146137606s"
                },
                "aggregates": [
                  "max(cover ((`AR`.`RATE`)))",
                  "min(cover ((`AR`.`RATE`)))"
                ],
                "group_keys": [
                  "cover ((`AR`.`K_PROPERTY`))",
                  "cover ((`AR`.`PROPERTY_NAME`))",
                  "cover ((`AR`.`SOURCE_CODE`))",
                  "cover ((`AR`.`SOURCE_NAME`))",
                  "cover ((`AR`.`GUESTS`))",
                  "cover ((`AR`.`LOS`))",
                  "cover ((`AR`.`ARRIVAL_DATE`))",
                  "cover ((`AR`.`LAST_SHOP_DTTM`))",
                  "cover ((`DD`.`CALENDAR_DATE`))"
                ],
                "#time_normal": "00:00.1903",
                "#time_absolute": 0.19031267600000001
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000010436
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "12.964584ms",
              "kernTime": "8.336312789s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "cover ((`DD`.`CALENDAR_DATE`))"
            ],
            "#time_normal": "00:00.0129",
            "#time_absolute": 0.012964584
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "13.839608ms",
              "kernTime": "8.348275418s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "cover ((`DD`.`CALENDAR_DATE`))"
            ],
            "#time_normal": "00:00.0138",
            "#time_absolute": 0.013839608
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.951µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2249,
                  "execTime": "5.788788ms",
                  "kernTime": "8.359014805s"
                },
                "result_terms": [
                  {
                    "as": "property_name",
                    "expr": "cover ((`AR`.`PROPERTY_NAME`))"
                  },
                  {
                    "as": "k_property",
                    "expr": "cover ((`AR`.`K_PROPERTY`))"
                  },
                  {
                    "as": "source_code",
                    "expr": "cover ((`AR`.`SOURCE_CODE`))"
                  },
                  {
                    "as": "source_name",
                    "expr": "cover ((`AR`.`SOURCE_NAME`))"
                  },
                  {
                    "as": "guests",
                    "expr": "cover ((`AR`.`GUESTS`))"
                  },
                  {
                    "as": "los",
                    "expr": "cover ((`AR`.`LOS`))"
                  },
                  {
                    "as": "arrival_date",
                    "expr": "cover ((`AR`.`ARRIVAL_DATE`))"
                  },
                  {
                    "as": "last_shop_dttm",
                    "expr": "cover ((`AR`.`LAST_SHOP_DTTM`))"
                  },
                  {
                    "as": "asof_date",
                    "expr": "cover ((`DD`.`CALENDAR_DATE`))"
                  },
                  {
                    "as": "min_rate",
                    "expr": "min(cover ((`AR`.`RATE`)))"
                  },
                  {
                    "as": "max_rate",
                    "expr": "max(cover ((`AR`.`RATE`)))"
                  }
                ],
                "#time_normal": "00:00.0057",
                "#time_absolute": 0.0057887880000000004
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2245,
                  "execTime": "271.138µs",
                  "kernTime": "5.254758ms"
                },
                "#time_normal": "00:00.0002",
                "#time_absolute": 0.000271138
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000001951
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000009031
      },
      "#time_normal": "00:00.0011",
      "#time_absolute": 0.001148683
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 748,
        "#itemsOut": 748,
        "#phaseSwitches": 2995,
        "execTime": "132.39µs",
        "kernTime": "8.371459741s"
      },
      "#time_normal": "00:00.0001",
      "#time_absolute": 0.00013239
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.1-2037-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.000001708
}

Query below :

SELECT AR.PROPERTY_NAME property_name,
       AR.K_PROPERTY k_property,
       AR.SOURCE_CODE source_code,
       AR.SOURCE_NAME source_name,
       AR.GUESTS guests,
       AR.LOS los,
       AR.ARRIVAL_DATE arrival_date,
       AR.LAST_SHOP_DTTM last_shop_dttm,
       DD.CALENDAR_DATE asof_date,
       MIN(AR.RATE) min_rate,
       MAX(AR.RATE) max_rate
FROM D_DATE DD
INNER JOIN ALL_RATES AR ON AR.VALID_FROM_DTTM < date_add_str(DD.CALENDAR_DATE, 1, 'day') AND AR.VALID_TO_DTTM >= DD.CALENDAR_DATE
INNER JOIN H_LOCAL_COMP_SET CS USE HASH(build) ON  AR.K_PROPERTY  = CS.K_COMP_PROPERTY
WHERE DD.CALENDAR_DATE BETWEEN '2019-08-22' AND '2019-08-22'
   AND CS.ENTERPRISE_ID = '607384'
   AND CS.SET_NAME = 'Primary'
   AND CS.IS_SUBSCRIBER = 'N'
   AND CS.K_SUBSCRIBER_PROPERTY = 421000987
   AND AR.ARRIVAL_DATE BETWEEN '2019-09-01'  and '2019-12-31'
   AND AR.SOURCE_NAME = 'Brand.com'
   AND AR.GUESTS = 1
   AND AR.LOS = 1
   AND AR.ROOM_PRODUCT_DESCRIPTION IN  ['Apartment/Multi BR', 'Deluxe', 'Standard', 'Suite']
GROUP BY AR.K_PROPERTY,
AR.PROPERTY_NAME,
AR.SOURCE_CODE,
AR.SOURCE_NAME,
AR.GUESTS,
AR.LOS,
AR.ARRIVAL_DATE,
AR.LAST_SHOP_DTTM,
DD.CALENDAR_DATE

Try your original query with new index

@vsr1 - I did also … taking 2min +

Here is Explain plan:

 {
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "2.119µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "6.459µs",
        "servTime": "2.370558ms"
      },
      "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.803µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.84µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "20.586µs",
                  "kernTime": "984ns",
                  "servTime": "1.137267ms"
                },
                "covers": [
                  "cover ((`D_DATE`.`CALENDAR_DATE`))",
                  "cover ((meta(`D_DATE`).`id`))"
                ],
                "index": "D_DATE_CALENDAR_DATE_INDEX",
                "index_id": "2a7dfe118d78e826",
                "index_projection": {
                  "entry_keys": [
                    0
                  ]
                },
                "keyspace": "D_DATE",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"2019-08-22\"",
                        "inclusion": 3,
                        "low": "\"2019-08-22\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.0011",
                "#time_absolute": 0.001157853
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "5.255µs"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 7,
                      "execTime": "6.245µs",
                      "kernTime": "1.168993ms"
                    },
                    "condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-08-22\" and \"2019-08-22\")",
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000006245
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 8,
                      "execTime": "7.062µs",
                      "kernTime": "1.184568ms"
                    },
                    "result_terms": [
                      {
                        "expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
                      }
                    ],
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000007062
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 4,
                      "execTime": "1.147µs",
                      "kernTime": "604ns"
                    },
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000001147
                  }
                ],
                "#time_normal": "00:00.0000",
                "#time_absolute": 0.000005255
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.0000018400000000000002
          },
          {
            "#operator": "Alias",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "2.62µs",
              "kernTime": "1.21382ms"
            },
            "as": "DD",
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.00000262
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "4.572µs"
            },
            "~children": [
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 17,
                  "#phaseSwitches": 77,
                  "execTime": "60.722µs",
                  "kernTime": "2.290423ms"
                },
                "alias": "CS",
                "on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 17,
                    "#phaseSwitches": 73,
                    "execTime": "63.546µs",
                    "kernTime": "4.277µs",
                    "servTime": "1.018779ms"
                  },
                  "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.000060722
              },
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 17,
                  "#itemsOut": 8175,
                  "#phaseSwitches": 32805,
                  "execTime": "62.746457ms",
                  "kernTime": "2m9.201755573s"
                },
                "alias": "AR",
                "on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 8175,
                    "#phaseSwitches": 32785,
                    "execTime": "37.950083ms",
                    "kernTime": "2.341664ms",
                    "servTime": "2m9.21811965s"
                  },
                  "as": "AR",
                  "covers": [
                    "cover ((`AR`.`SOURCE_NAME`))",
                    "cover ((`AR`.`GUESTS`))",
                    "cover ((`AR`.`LOS`))",
                    "cover ((`AR`.`ARRIVAL_DATE`))",
                    "cover ((`AR`.`VALID_FROM_DTTM`))",
                    "cover ((`AR`.`VALID_TO_DTTM`))",
                    "cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`))",
                    "cover ((`AR`.`K_PROPERTY`))",
                    "cover ((`AR`.`SOURCE_CODE`))",
                    "cover ((`AR`.`LAST_SHOP_DTTM`))",
                    "cover ((`AR`.`RATE`))",
                    "cover ((`AR`.`PROPERTY_NAME`))",
                    "cover ((meta(`AR`).`id`))"
                  ],
                  "index": "ALL_RATES_DXP1_INDEX",
                  "index_id": "1c3e1624404c1040",
                  "keyspace": "ALL_RATES",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        },
                        {
                          "high": "\"Apartment/Multi BR\"",
                          "inclusion": 3,
                          "low": "\"Apartment/Multi BR\""
                        },
                        {
                          "high": "cover ((`CS`.`K_COMP_PROPERTY`))",
                          "inclusion": 3,
                          "low": "cover ((`CS`.`K_COMP_PROPERTY`))"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        },
                        {
                          "high": "\"Deluxe\"",
                          "inclusion": 3,
                          "low": "\"Deluxe\""
                        },
                        {
                          "high": "cover ((`CS`.`K_COMP_PROPERTY`))",
                          "inclusion": 3,
                          "low": "cover ((`CS`.`K_COMP_PROPERTY`))"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        },
                        {
                          "high": "\"Standard\"",
                          "inclusion": 3,
                          "low": "\"Standard\""
                        },
                        {
                          "high": "cover ((`CS`.`K_COMP_PROPERTY`))",
                          "inclusion": 3,
                          "low": "cover ((`CS`.`K_COMP_PROPERTY`))"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"2019-12-31\"",
                          "inclusion": 3,
                          "low": "\"2019-09-01\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        },
                        {
                          "high": "\"Suite\"",
                          "inclusion": 3,
                          "low": "\"Suite\""
                        },
                        {
                          "high": "cover ((`CS`.`K_COMP_PROPERTY`))",
                          "inclusion": 3,
                          "low": "cover ((`CS`.`K_COMP_PROPERTY`))"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.0627",
                "#time_absolute": 0.062746457
              },
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 8175,
                  "#phaseSwitches": 32703,
                  "execTime": "108.693894ms",
                  "kernTime": "2m9.15656527s"
                },
                "condition": "(((((((((((cover ((`CS`.`ENTERPRISE_ID`)) = \"607384\") and (cover ((`CS`.`SET_NAME`)) = \"Primary\")) and (cover ((`CS`.`IS_SUBSCRIBER`)) = \"N\")) and (cover ((`CS`.`K_SUBSCRIBER_PROPERTY`)) = 421000987)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-09-01\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_NAME`)) = \"Brand.com\")) and (cover ((`AR`.`GUESTS`)) = 1)) and (cover ((`AR`.`LOS`)) = 1)) and (cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`)) in [\"Apartment/Multi BR\", \"Deluxe\", \"Standard\", \"Suite\"])) and (cover ((`AR`.`VALID_FROM_DTTM`)) < date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\"))) and ((`DD`.`CALENDAR_DATE`) <= cover ((`AR`.`VALID_TO_DTTM`))))",
                "#time_normal": "00:00.1086",
                "#time_absolute": 0.108693894
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 8175,
                  "#itemsOut": 748,
                  "#phaseSwitches": 17849,
                  "execTime": "152.195728ms",
                  "kernTime": "2m9.117706025s"
                },
                "aggregates": [
                  "max(cover ((`AR`.`RATE`)))",
                  "min(cover ((`AR`.`RATE`)))"
                ],
                "group_keys": [
                  "cover ((`AR`.`K_PROPERTY`))",
                  "cover ((`AR`.`PROPERTY_NAME`))",
                  "cover ((`AR`.`SOURCE_CODE`))",
                  "cover ((`AR`.`SOURCE_NAME`))",
                  "cover ((`AR`.`GUESTS`))",
                  "cover ((`AR`.`LOS`))",
                  "cover ((`AR`.`ARRIVAL_DATE`))",
                  "cover ((`AR`.`LAST_SHOP_DTTM`))",
                  "(`DD`.`CALENDAR_DATE`)"
                ],
                "#time_normal": "00:00.1521",
                "#time_absolute": 0.152195728
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.0000045720000000000004
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "12.076637ms",
              "kernTime": "2m9.27071143s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "(`DD`.`CALENDAR_DATE`)"
            ],
            "#time_normal": "00:00.0120",
            "#time_absolute": 0.012076637
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 748,
              "#itemsOut": 748,
              "#phaseSwitches": 2995,
              "execTime": "14.048965ms",
              "kernTime": "2m9.281430974s"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "(`DD`.`CALENDAR_DATE`)"
            ],
            "#time_normal": "00:00.0140",
            "#time_absolute": 0.014048965
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.554µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2249,
                  "execTime": "6.462504ms",
                  "kernTime": "2m9.292447244s"
                },
                "result_terms": [
                  {
                    "as": "property_name",
                    "expr": "cover ((`AR`.`PROPERTY_NAME`))"
                  },
                  {
                    "as": "k_property",
                    "expr": "cover ((`AR`.`K_PROPERTY`))"
                  },
                  {
                    "as": "source_code",
                    "expr": "cover ((`AR`.`SOURCE_CODE`))"
                  },
                  {
                    "as": "source_name",
                    "expr": "cover ((`AR`.`SOURCE_NAME`))"
                  },
                  {
                    "as": "guests",
                    "expr": "cover ((`AR`.`GUESTS`))"
                  },
                  {
                    "as": "los",
                    "expr": "cover ((`AR`.`LOS`))"
                  },
                  {
                    "as": "arrival_date",
                    "expr": "cover ((`AR`.`ARRIVAL_DATE`))"
                  },
                  {
                    "as": "last_shop_dttm",
                    "expr": "cover ((`AR`.`LAST_SHOP_DTTM`))"
                  },
                  {
                    "as": "asof_date",
                    "expr": "(`DD`.`CALENDAR_DATE`)"
                  },
                  {
                    "as": "min_rate",
                    "expr": "min(cover ((`AR`.`RATE`)))"
                  },
                  {
                    "as": "max_rate",
                    "expr": "max(cover ((`AR`.`RATE`)))"
                  }
                ],
                "#time_normal": "00:00.0064",
                "#time_absolute": 0.006462504
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 748,
                  "#itemsOut": 748,
                  "#phaseSwitches": 2245,
                  "execTime": "278.988µs",
                  "kernTime": "4.156431ms"
                },
                "#time_normal": "00:00.0002",
                "#time_absolute": 0.000278988
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000001554
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000006803
      },
      "#time_normal": "00:00.0023",
      "#time_absolute": 0.002377017
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 748,
        "#itemsOut": 748,
        "#phaseSwitches": 2995,
        "execTime": "120.66µs",
        "kernTime": "2m9.305721519s"
      },
      "#time_normal": "00:00.0001",
      "#time_absolute": 0.00012066
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.1-2037-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.0000021190000000000004
}
            "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 8175,
                "#phaseSwitches": 32785,
                "execTime": "37.950083ms",
                "kernTime": "2.341664ms",
                "servTime": "2m9.21811965s"
              },
              "as": "AR",

try this

CREATE INDEX ALL_RATES_DXP_INDEX ON ALL_RATES (SOURCE_NAME, GUESTS, LOS, 
K_PROPERTY, ROOM_PRODUCT_DESCRIPTION
ARRIVAL_DATE, VALID_FROM_DTTM, VALID_TO_DTTM,  SOURCE_CODE ,  LAST_SHOP_DTTM , 
RATE , PROPERTY_NAME );
1 Like

@vsr1

wow ! Coming in Ms with this index : Help me understand this please ?

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "3.172µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "3.725µs",
        "servTime": "1.357799ms"
      },
      "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.551µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "2.25µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "19.911µs",
                  "kernTime": "2.195µs",
                  "servTime": "733.601µs"
                },
                "covers": [
                  "cover ((`D_DATE`.`CALENDAR_DATE`))",
                  "cover ((meta(`D_DATE`).`id`))"
                ],
                "index": "D_DATE_CALENDAR_DATE_INDEX",
                "index_id": "2a7dfe118d78e826",
                "index_projection": {
                  "entry_keys": [
                    0
                  ]
                },
                "keyspace": "D_DATE",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"2019-08-22\"",
                        "inclusion": 3,
                        "low": "\"2019-08-22\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.0007",
                "#time_absolute": 0.0007535119999999999
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "6.615µs"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 7,
                      "execTime": "30.148µs",
                      "kernTime": "766.159µs"
                    },
                    "condition": "(cover ((`D_DATE`.`CALENDAR_DATE`)) between \"2019-08-22\" and \"2019-08-22\")",
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000030148
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 8,
                      "execTime": "16.481µs",
                      "kernTime": "798.794µs"
                    },
                    "result_terms": [
                      {
                        "expr": "cover ((`D_DATE`.`CALENDAR_DATE`))"
                      }
                    ],
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000016481
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 4,
                      "execTime": "1.657µs",
                      "kernTime": "3.796µs"
                    },
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000001657
                  }
                ],
                "#time_normal": "00:00.0000",
                "#time_absolute": 0.000006615
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.00000225
          },
          {
            "#operator": "Alias",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "14.759µs",
              "kernTime": "837.936µs"
            },
            "as": "DD",
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000014759
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "14.271µs"
            },
            "~children": [
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 17,
                  "#phaseSwitches": 77,
                  "execTime": "66.433µs",
                  "kernTime": "1.856423ms"
                },
                "alias": "CS",
                "on_clause": "((`DD`.`CALENDAR_DATE`) = (`DD`.`CALENDAR_DATE`))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 17,
                    "#phaseSwitches": 73,
                    "execTime": "60.037µs",
                    "kernTime": "4.235µs",
                    "servTime": "919.045µs"
                  },
                  "as": "CS",
                  "covers": [
                    "cover ((`CS`.`K_SUBSCRIBER_PROPERTY`))",
                    "cover ((`CS`.`ENTERPRISE_ID`))",
                    "cover ((`CS`.`SET_NAME`))",
                    "cover ((`CS`.`IS_SUBSCRIBER`))",
                    "cover ((`CS`.`K_COMP_PROPERTY`))",
                    "cover ((meta(`CS`).`id`))"
                  ],
                  "index": "H_LOCAL_CS_NKEY_INDEX",
                  "index_id": "e756a6788a7d9c1f",
                  "keyspace": "H_LOCAL_COMP_SET",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "421000987",
                          "inclusion": 3,
                          "low": "421000987"
                        },
                        {
                          "high": "\"607384\"",
                          "inclusion": 3,
                          "low": "\"607384\""
                        },
                        {
                          "high": "\"Primary\"",
                          "inclusion": 3,
                          "low": "\"Primary\""
                        },
                        {
                          "high": "\"N\"",
                          "inclusion": 3,
                          "low": "\"N\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.0000",
                "#time_absolute": 0.000066433
              },
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 17,
                  "#itemsOut": 4844,
                  "#phaseSwitches": 19481,
                  "execTime": "38.629774ms",
                  "kernTime": "376.564813ms"
                },
                "alias": "AR",
                "on_clause": "(cover ((`AR`.`K_PROPERTY`)) = cover ((`CS`.`K_COMP_PROPERTY`)))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 4844,
                    "#phaseSwitches": 19461,
                    "execTime": "20.747794ms",
                    "kernTime": "1.108632ms",
                    "servTime": "387.1264ms"
                  },
                  "as": "AR",
                  "covers": [
                    "cover ((`AR`.`SOURCE_NAME`))",
                    "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`.`SOURCE_CODE`))",
                    "cover ((`AR`.`LAST_SHOP_DTTM`))",
                    "cover ((`AR`.`RATE`))",
                    "cover ((`AR`.`PROPERTY_NAME`))",
                    "cover ((meta(`AR`).`id`))"
                  ],
                  "index": "ALL_RATES_DXP_INDEX",
                  "index_id": "1fb59e03af9eb126",
                  "keyspace": "ALL_RATES",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "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-09-31\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "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-09-31\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "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-09-31\""
                        },
                        {
                          "high": "date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\")",
                          "inclusion": 0,
                          "low": "null"
                        },
                        {
                          "inclusion": 1,
                          "low": "(`DD`.`CALENDAR_DATE`)"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"Brand.com\"",
                          "inclusion": 3,
                          "low": "\"Brand.com\""
                        },
                        {
                          "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-09-31\""
                        },
                        {
                          "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.0386",
                "#time_absolute": 0.038629774
              },
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 4844,
                  "#itemsOut": 4844,
                  "#phaseSwitches": 19379,
                  "execTime": "56.175532ms",
                  "kernTime": "359.640229ms"
                },
                "condition": "(((((((((((cover ((`CS`.`ENTERPRISE_ID`)) = \"607384\") and (cover ((`CS`.`SET_NAME`)) = \"Primary\")) and (cover ((`CS`.`IS_SUBSCRIBER`)) = \"N\")) and (cover ((`CS`.`K_SUBSCRIBER_PROPERTY`)) = 421000987)) and (cover ((`AR`.`ARRIVAL_DATE`)) between \"2019-09-31\" and \"2019-12-31\")) and (cover ((`AR`.`SOURCE_NAME`)) = \"Brand.com\")) and (cover ((`AR`.`GUESTS`)) = 1)) and (cover ((`AR`.`LOS`)) = 1)) and (cover ((`AR`.`ROOM_PRODUCT_DESCRIPTION`)) in [\"Apartment/Multi BR\", \"Deluxe\", \"Standard\", \"Suite\"])) and (cover ((`AR`.`VALID_FROM_DTTM`)) < date_add_str((`DD`.`CALENDAR_DATE`), 1, \"day\"))) and ((`DD`.`CALENDAR_DATE`) <= cover ((`AR`.`VALID_TO_DTTM`))))",
                "#time_normal": "00:00.0561",
                "#time_absolute": 0.056175532
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 4844,
                  "#itemsOut": 545,
                  "#phaseSwitches": 10781,
                  "execTime": "82.796936ms",
                  "kernTime": "334.653893ms"
                },
                "aggregates": [
                  "max(cover ((`AR`.`RATE`)))",
                  "min(cover ((`AR`.`RATE`)))"
                ],
                "group_keys": [
                  "cover ((`AR`.`K_PROPERTY`))",
                  "cover ((`AR`.`PROPERTY_NAME`))",
                  "cover ((`AR`.`SOURCE_CODE`))",
                  "cover ((`AR`.`SOURCE_NAME`))",
                  "cover ((`AR`.`GUESTS`))",
                  "cover ((`AR`.`LOS`))",
                  "cover ((`AR`.`ARRIVAL_DATE`))",
                  "cover ((`AR`.`LAST_SHOP_DTTM`))",
                  "(`DD`.`CALENDAR_DATE`)"
                ],
                "#time_normal": "00:00.0827",
                "#time_absolute": 0.082796936
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000014271000000000001
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 545,
              "#itemsOut": 545,
              "#phaseSwitches": 2183,
              "execTime": "8.096944ms",
              "kernTime": "417.55372ms"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "(`DD`.`CALENDAR_DATE`)"
            ],
            "#time_normal": "00:00.0080",
            "#time_absolute": 0.008096944
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 545,
              "#itemsOut": 545,
              "#phaseSwitches": 2183,
              "execTime": "9.344715ms",
              "kernTime": "425.588164ms"
            },
            "aggregates": [
              "max(cover ((`AR`.`RATE`)))",
              "min(cover ((`AR`.`RATE`)))"
            ],
            "group_keys": [
              "cover ((`AR`.`K_PROPERTY`))",
              "cover ((`AR`.`PROPERTY_NAME`))",
              "cover ((`AR`.`SOURCE_CODE`))",
              "cover ((`AR`.`SOURCE_NAME`))",
              "cover ((`AR`.`GUESTS`))",
              "cover ((`AR`.`LOS`))",
              "cover ((`AR`.`ARRIVAL_DATE`))",
              "cover ((`AR`.`LAST_SHOP_DTTM`))",
              "(`DD`.`CALENDAR_DATE`)"
            ],
            "#time_normal": "00:00.0093",
            "#time_absolute": 0.009344715
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.645µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 545,
                  "#itemsOut": 545,
                  "#phaseSwitches": 1640,
                  "execTime": "4.319335ms",
                  "kernTime": "434.517812ms"
                },
                "result_terms": [
                  {
                    "as": "property_name",
                    "expr": "cover ((`AR`.`PROPERTY_NAME`))"
                  },
                  {
                    "as": "k_property",
                    "expr": "cover ((`AR`.`K_PROPERTY`))"
                  },
                  {
                    "as": "source_code",
                    "expr": "cover ((`AR`.`SOURCE_CODE`))"
                  },
                  {
                    "as": "source_name",
                    "expr": "cover ((`AR`.`SOURCE_NAME`))"
                  },
                  {
                    "as": "guests",
                    "expr": "cover ((`AR`.`GUESTS`))"
                  },
                  {
                    "as": "los",
                    "expr": "cover ((`AR`.`LOS`))"
                  },
                  {
                    "as": "arrival_date",
                    "expr": "cover ((`AR`.`ARRIVAL_DATE`))"
                  },
                  {
                    "as": "last_shop_dttm",
                    "expr": "cover ((`AR`.`LAST_SHOP_DTTM`))"
                  },
                  {
                    "as": "asof_date",
                    "expr": "(`DD`.`CALENDAR_DATE`)"
                  },
                  {
                    "as": "min_rate",
                    "expr": "min(cover ((`AR`.`RATE`)))"
                  },
                  {
                    "as": "max_rate",
                    "expr": "max(cover ((`AR`.`RATE`)))"
                  }
                ],
                "#time_normal": "00:00.0043",
                "#time_absolute": 0.004319335
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 545,
                  "#itemsOut": 545,
                  "#phaseSwitches": 1636,
                  "execTime": "195.458µs",
                  "kernTime": "3.134078ms"
                },
                "#time_normal": "00:00.0001",
                "#time_absolute": 0.000195458
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000001645
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000003551
      },
      "#time_normal": "00:00.0013",
      "#time_absolute": 0.001361524
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 545,
        "#itemsOut": 545,
        "#phaseSwitches": 2183,
        "execTime": "84.062µs",
        "kernTime": "443.568317ms"
      },
      "#time_normal": "00:00.0000",
      "#time_absolute": 0.000084062
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.1-2037-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.0000031720000000000003
}

I am seeing you have changed the position of couple of keys here from your first suggestion… How that helps and how to determine to trace what is the right and effective way from Plan ?

Hi @vsr1 - I have read this document . But still couldn’t figure out how changing the index position improves query performance. May be you can give me a from 1000 feet up version of it what makes you change the position of index looking at plan .
thanks again …
Also do we think any perf improvement can be achieved by making this Index as Partitioned covered Index ?
I am running 3000 concurrent threads and also testing with 3000 in a multiprocessing manner with python thread executor . Multi processing is pretty fast with decent amount of CPU spike ~60% for 2-3 seconds then come down to 7-10% for all 4 nodes . However Multi thread stumbles in between 3000 parallel execution and stops for couple of seconds before proceed. As mentioned I have max-parallelism set with 16 . And max-concurrency from query log set as 56 .
I have logical CPU limit from 20 goes upto 75.
And I am using couchbase cluster connection like below using 2 nodes only :

COUCHBASE_CONNSTR = ‘couchbase://10.xx.xxx.xx:32332,10.xx.xxx.xx.:30525’

Anything you see as bottleneck here ?
thanks

Check out Rule#11 in above link.
High Level: Couchbase Index Scans are Range Scans. Think like index scan uses b-tree like structure (sort by first key, when duplicates sorts by second key like that). If first key is range a to c and second key = 5 then it needs read all the values > = a and >= 5 and all the values of b irrespective of second key , <= c and <= 5. This involves disk i/o and then apply individual keys and eliminate( The picture here https://docs.couchbase.com/server/current/learn/services-and-indexes/indexes/index_pushdowns.html#composite-predicate-pushdown gives easy understanding )

Simple way explain is assume word search in dictionary. each letter is key in the index. first letter is a to p second letter is say r. How many pages you need to read get those words.

Partition index can help.
If you are testing load test 3000 threads don’t recommend max_parallelism at all.
I am not sure about connstr. If you connect 8091 port it automatically do load balance on all query service nodes. or list all the 4 nodes. You can post on sdk section,
Each query service has limit on how many concurrent queries it can run https://docs.couchbase.com/server/current/settings/query-settings.html#servicers ( unbounded 4* numberofcpus, request_plus 16 * numberofcpus). If you need more you must increase number of query nodes .
max-concurrency =56 mean u have 56 cpus, i.e 224 concurrent queries it can run on each query node.

Other option is instead of firing all 3000 threads once you should start one wait sec first then fire others afterwords.
SDK may doing prepared statement. If you do one prepare is complete and other after can reuse prepare. If you start all will do prepare at same time.

1 Like

Perfect . I have read through Rules #11 and this perfectly makes sense and make it easier for index to get results faster .
For max parallelism it was set 1 before I made the change but I could revert back and see if this makes any difference on much better concurrent response. My understanding was “Max_parallelism controls how many threads can run in parallel concurrently for a SINGLE query” and to better use of all logical CPU this helps. Any reason you why are you not recommending this ? And in scenario is suitable use-case for this ?

I will certainly do this cover index partition and will post results .
Will do post on SDK section to understand the loadbalancing across connection specifiers . What I don’t know how the connection pooling occurs in CB and we don’t do open close connections and if all the connections are threadsafe and connection timeout scenario if exist .
In MDS configuration do you recommend to have multiple query nodes . what I am not sure is should we engage more CPU power to Query nodes or Index nodes in faster concurrent connections scenario .

Also in a multipass run sometime I am ending up with below couchbase errors and sometime its running fine …Not sure what is related to this throwing this exceptions:

couchbase.exceptions._ProtocolError_0x16 (generated, catch ProtocolError): <RC=0x16[Data received on socket was not in the expected format], HTTP Request failed. Examine 'objextra' for full result, Results=1, C Source=(src/http.c,144), OBJ=ViewResult<rc=0x16[Data received on socket was not in the expected format], value=b'},\n', http_status=200, tracing_context=0, tracing_output=None>, Tracing Output={":nokey:0": null}>

If you have few queries running and more cpus Max_parallelism is good so that cpus can be used efficiently. If you are running many queries this can result more memory and thread contentions. You can try which works for you.

In MDS configuration you can have multiple query or index nodes. so that it can scale well.
If you think cpu is not fully utilize and see if u can modify servicers and see if that helps more request per sec

Thanks and that is great simple usecase when should we choose Max Parallelism .

{“completed-limit”:4000,“completed-threshold”:1000,“controls”:false,“cpuprofile”:"",“debug”:false,“keep-alive-length”:16384,“loglevel”:“INFO”,“max-index-api”:3,“max-parallelism”:16,“memprofile”:"",“n1ql-feat-ctrl”:0,“pipeline-batch”:16,“pipeline-cap”:512,“prepared-limit”:16384,“pretty”:false,“profile”:“off”,“request-size-cap”:67108864,“scan-cap”:512,“servicers”:224,“timeout”:0}

This is my servicers settings in each node . CPU is definitely under utilize as 3000 concurrent session it is running in ~10-20% utilization in each node CPU . So bumping upto what limit you think is sustainable ?

i doubt it try double servicers and see

w/o servicers change now I am periodically beaten by this and couple of other weird errors and again this behavior of getting below is random and not happening at all the time …

couchbase.exceptions._TimeoutError_0x17 (generated, catch TimeoutError): <RC=0x17[Client-Side timeout exceeded for operation. Inspect network conditions or increase the timeout], HTTP Request failed. Examine 'objextra' for full result, Results=1, C Source=(src/http.c,144), OBJ=ViewResult<rc=0x17[Client-Side timeout exceeded for operation. Inspect network conditions or increase the timeout], value=None, http_status=0, tracing_context=0, tracing_output=None>, Tracing Output={":nokey:0": null}>

14%|█▍ | 423/2996 [00:20<07:31, 5.70it/s]couchbase.exceptions._ProtocolError_0x16 (generated, catch ProtocolError): <RC=0x16[Data received on socket was not in the expected format], HTTP Request failed. Examine ‘objextra’ for full result, Results=1, C Source=(src/http.c,144),

couchbase.exceptions._ProtocolError_0x16 (generated, catch ProtocolError): <RC=0x16[Data received on socket was not in the expected format], HTTP Request failed. Examine ‘objextra’ for full result, Results=1, C Source=(src/http.c,144), OBJ=ViewResult<rc=0x16[Data received on socket was not in the expected format], value=b’},\n{“arrival_date”:“2019-12-01”,“asof_date”:“2019-08-01”,“guests”:1,“k_property”:432107026,“last_shop_dttm”:“2019-11-28 01:37:20”,“line_status”:“S”,“los”:1,“max_rate”:315.84,“min_rate”:17HTTP/1.1 200 OK\r\nContent-Length: 498\r\nContent-Type: application/json; version=2.0.0-N1QL\r\nDate: Fri, 27 Dec 2019 04:52:26 GMT\r\n\r\n{\n"requestID": “51cdf1bb-45c7-4189-b148-1c211c79683c”,\n"signature":

Looking at this thread if I can get some help to increase from default 75 seconds timeout … Is that SDK group is right to address this concerns ?

https://docs.couchbase.com/python-sdk/current/n1ql-queries-with-sdk.html#options

https://docs.couchbase.com/python-sdk/current/client-settings.html
n1ql_timeout

If still question post in Python SDK topic

Thank you … Understand the additional fine tune parameter config on N1QL … w/o below :
not sure how this is helpful ;

No-result queries

As a convenience for queries which are not intended to yield multiple rows, you may use the returned N1QLRequest object’s execute() method. For queries which are intended to return only a single result, you can use the get_single_result() method. Both of the aforementioned methods are wrappers that iterate over the object internally and are intended to provide additional clarity inside your application’s code.

bkt.n1ql_query("CREATE PRIMARY INDEX ON default").execute()

I am simply using : row_iter = cb.n1ql_query(query) to return the N1QLRequest object , what is additional benefit I will get if I do execute() as well ?

thanks

You should ask on sdk section.