N1QL Query Performance Issue - Declines on volume of data fetch

SQL++
16 
CREATE INDEX majorindex ON group360all(PatternDate,ExternalBookingId,BookingStatus, SnapshotDate,ExternalMarketSegmentId,ExternalBookedById,RevenueType,BlendedEventRevenueTotal,FBRevenueTotal,AVRevTotal,OtherRevTotal,BlendedRoomnightsTotal,BookedSqft,BlendedGuestroomRevenueTotal)

SELECT
   M.PatternDate patterndate,
   ROUND(SUM(M.BlendedEventRevenueTotal),2) funcroomrev,
   ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
   ROUND(SUM(M.AVRevTotal),2) avrev ,
   ROUND(SUM(M.OtherRevTotal),2) otherrev ,
   ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
   ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
   ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
   SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
   ROUND(SUM(CASE WHEN M.BookingStatus = "Definite" THEN M.BlendedRoomnightsTotal ELSE 0 END)/SUM(M.BlendedRoomnightsTotal),3) conversion,
   SUM(M.BlendedRoomnightsTotal) grprn,
   ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
   ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr
FROM ( SELECT mx.*
       FROM group360all  A
       WHERE A.PatternDate BETWEEN "2018-01-01" AND "2018-12-31"
       AND A.BookingStatus IN ["Definite"] AND A.ExternalBookingId IS NOT NULL
       GROUP BY A.PatternDate, A.ExternalBookingId
       LETTING mx = MAX([A.SnapshotDate, {A.PatternDate, A.BlendedEventRevenueTotal, A.FBRevenueTotal,
                                          A.AVRevTotal,A.OtherRevTotal,A.BlendedRoomnightsTotal, A.BlendedGuestroomRevenueTotal,
                                          A.BookedSqft, A.BookingStatus}])[1]
       ) M
GROUP BY M.PatternDate
ORDER BY M.PatternDate

I have adjusted index moved BookingStatus before SnapshotDate so that index can apply predicate during scan time. Also Added A.ExternalBookingId IS NOT NULL so that BookingStatus can be passed to indexer. You can see that in spans of the EXPLAIN.

How it works with MIN/MAX without JOIN you can read Select non-group by fields using sql query
MAX([arg1,arg2])[1]

arg2 is object and you must include all the fields you want in the parent query, otherwise those become MISSING and will not give right results.

1 Like