Weak performance when selecting from complex views

[patricebender]

Thanks @BobdenOs for bringing this up, I tried to summarize the findings in this issue for future discussions.

Take this view from sflight:

  entity Bookings as projection on my.Booking {
    @UI.Hidden: false
    BookingUUID as ID,
    to_Travel.TravelID,
    BookingID,

    @title : 'Travel/Booking ID'
    to_Travel.TravelID || '/' || BookingID as CombinedID : String,


    ConnectionID,
    FlightDate,

    // pretend all bookings have the same currency so the FlightPrice can be aggregated
    @title: '{i18n>CurrencyCode}'
    'USD' as CurrencyCode_code : String(3),
    //CurrencyCode.code as CurrencyCode_code,
    @Measures.ISOCurrency: CurrencyCode_code
    FlightPrice,

    @title: '{i18n>BookingStatus}'
    @Common.Text: statusName @Common.TextArrangement: #TextOnly
    BookingStatus.code   as status,
    BookingStatus.name   as statusName,

    @Common.Text: airlineName
    to_Carrier.AirlineID as airline,
    to_Carrier.Name      as airlineName,

    BookingDate,

    to_Travel,
    to_Carrier,

    // Java has a problem with this association
    to_Flight,

    // Workaround:
    to_Flight.PlaneType,
    to_Flight.to_Connection.Distance,
    to_Flight.to_Connection.DistanceUnit,
    @Common.Label: '{i18n>DepartureAirport}'
    @Common.Text: DepCity
    to_Flight.to_Connection.DepartureAirport.AirportID as DepAirport,
    to_Flight.to_Connection.DepartureAirport.City      as DepCity,
    @Common.Label: '{i18n>ArrivalAirport}'
    @Common.Text: DestCity
    to_Flight.to_Connection.DestinationAirport.AirportID as DestAirport,
    to_Flight.to_Connection.DestinationAirport.City      as DestCity,
  };

which is transformed to the following sql, note that we have 8 query sources in the from clause:

CREATE VIEW AnalyticsService_Bookings AS
SELECT
  Booking_0.BookingUUID AS ID,
  to_Travel_1.TravelID,
  Booking_0.BookingID,
  to_Travel_1.TravelID || '/' || Booking_0.BookingID AS CombinedID,
  Booking_0.ConnectionID,
  Booking_0.FlightDate,
  'USD' AS CurrencyCode_code,
  Booking_0.FlightPrice,
  BookingStatus_2.code AS status,
  BookingStatus_2.name AS statusName,
  to_Carrier_3.AirlineID AS airline,
  to_Carrier_3.Name AS airlineName,
  Booking_0.BookingDate,
  Booking_0.to_Travel_TravelUUID,
  Booking_0.to_Carrier_AirlineID,
  to_Flight_4.PlaneType,
  to_Connection_5.Distance,
  to_Connection_5.DistanceUnit,
  DepartureAirport_6.AirportID AS DepAirport,
  DepartureAirport_6.City AS DepCity,
  DestinationAirport_7.AirportID AS DestAirport,
  DestinationAirport_7.City AS DestCity
FROM
  (
    (
      (
        (
          (
            (
              (
                sap_fe_cap_travel_Booking AS Booking_0
                LEFT JOIN sap_fe_cap_travel_Travel AS to_Travel_1 ON Booking_0.to_Travel_TravelUUID = to_Travel_1.TravelUUID
              )
              LEFT JOIN sap_fe_cap_travel_BookingStatus AS BookingStatus_2 ON Booking_0.BookingStatus_code = BookingStatus_2.code
            )
            LEFT JOIN sap_fe_cap_travel_Airline AS to_Carrier_3 ON Booking_0.to_Carrier_AirlineID = to_Carrier_3.AirlineID
          )
          LEFT JOIN sap_fe_cap_travel_Flight AS to_Flight_4 ON to_Flight_4.AirlineID = Booking_0.to_Carrier_AirlineID
          AND to_Flight_4.FlightDate = Booking_0.FlightDate
          AND to_Flight_4.ConnectionID = Booking_0.ConnectionID
        )
        LEFT JOIN sap_fe_cap_travel_FlightConnection AS to_Connection_5 ON to_Connection_5.AirlineID = to_Flight_4.AirlineID
        AND to_Connection_5.ConnectionID = to_Flight_4.ConnectionID
      )
      LEFT JOIN sap_fe_cap_travel_Airport AS DepartureAirport_6 ON to_Connection_5.DepartureAirport_AirportID = DepartureAirport_6.AirportID
    )
    LEFT JOIN sap_fe_cap_travel_Airport AS DestinationAirport_7 ON to_Connection_5.DestinationAirport_AirportID = DestinationAirport_7.AirportID
  );

if we now fire a common request as this one: Bookings[ID=7A757221A8E4645C17002DF03754AB66].to_Travel.to_Booking we create a runtime query like this:

    SELECT
    …
    FROM
      AnalyticsService_Bookings as to_Booking
    WHERE
      exists (
        SELECT
          1 as "1"
        FROM
          AnalyticsService_Travels as to_Travel
        WHERE
          to_Travel.TravelUUID = to_Booking.to_Travel_TravelUUID
          and exists (
            SELECT
              1 as "1"
            FROM
              AnalyticsService_Bookings as Bookings
            WHERE
              Bookings.to_Travel_TravelUUID = to_Travel.TravelUUID
              and Bookings.ID = 7A757221A8E4645C17002DF03754AB66
          )
      )

the issue is that all the 7 left joins of the original view definition are materialized before our where exists subquery is applied to narrow down the results to exactly one booking with the matching UUID. The query becomes slower with more data to be joined and the execution plan becomes unnecessary complex:

the blue box is the where exists subquery and the red box is the view's join

if we would push down the path expression to the join node where the Bookings.ID is matched, like in this query:

SELECT
  Booking_0.BookingUUID AS ID,
  to_Travel_1.TravelID,
  Booking_0.BookingID,
  to_Travel_1.TravelID || '/' || Booking_0.BookingID AS CombinedID,
  Booking_0.ConnectionID,
  Booking_0.FlightDate,
  'USD' AS CurrencyCode_code,
  Booking_0.FlightPrice,
  BookingStatus_2.code AS status,
  BookingStatus_2.name AS statusName,
  to_Carrier_3.AirlineID AS airline,
  to_Carrier_3.Name AS airlineName,
  Booking_0.BookingDate,
  Booking_0.to_Travel_TravelUUID,
  Booking_0.to_Carrier_AirlineID,
  to_Flight_4.PlaneType,
  to_Connection_5.Distance,
  to_Connection_5.DistanceUnit,
  DepartureAirport_6.AirportID AS DepAirport,
  DepartureAirport_6.City AS DepCity,
  DestinationAirport_7.AirportID AS DestAirport,
  DestinationAirport_7.City AS DestCityFROM (
    (
      (
        (
          (
            (
              (
                (
                  SELECT
                    *
                  FROM
                    sap_fe_cap_travel_Booking AS to_Booking
                  WHERE
                    exists (
                      SELECT
                        1
                      FROM
                        sap_fe_cap_travel_Travel as to_Travel
                      WHERE
                        to_Travel.TravelUUID = to_Booking.to_Travel_TravelUUID
                        and exists (
                          SELECT
                            1
                          FROM
                            sap_fe_cap_travel_Booking as Bookings
                          WHERE
                            Bookings.to_Travel_TravelUUID = to_Travel.TravelUUID
                            and Bookings.BookingUUID = '7A757221A8E4645C17002DF03754AB66'
                        )
                    )
                ) as Booking_0
                LEFT JOIN sap_fe_cap_travel_Travel AS to_Travel_1 ON Booking_0.to_Travel_TravelUUID = to_Travel_1.TravelUUID
              )
              LEFT JOIN sap_fe_cap_travel_BookingStatus AS BookingStatus_2 ON Booking_0.BookingStatus_code = BookingStatus_2.code
            )
            LEFT JOIN sap_fe_cap_travel_Airline AS to_Carrier_3 ON Booking_0.to_Carrier_AirlineID = to_Carrier_3.AirlineID
          )
          LEFT JOIN sap_fe_cap_travel_Flight AS to_Flight_4 ON to_Flight_4.AirlineID = Booking_0.to_Carrier_AirlineID
          AND to_Flight_4.FlightDate = Booking_0.FlightDate
          AND to_Flight_4.ConnectionID = Booking_0.ConnectionID
        )
        LEFT JOIN sap_fe_cap_travel_FlightConnection AS to_Connection_5 ON to_Connection_5.AirlineID = to_Flight_4.AirlineID
        AND to_Connection_5.ConnectionID = to_Flight_4.ConnectionID
      )
      LEFT JOIN sap_fe_cap_travel_Airport AS DepartureAirport_6 ON to_Connection_5.DepartureAirport_AirportID = DepartureAirport_6.AirportID
    )
    LEFT JOIN sap_fe_cap_travel_Airport AS DestinationAirport_7 ON to_Connection_5.DestinationAirport_AirportID = DestinationAirport_7.AirportID
  );

the query becomes (in the case of sflight with 9k records) more than 100x faster, the execution plan for this query looks different, too. As all the joins are only applied to the already-narrowed-down results which matched the Bookings.BookingUUID = '7A757221A8E4645C17002DF03754AB66'

[BobdenOs]

Here is some additional information for the same approach when using @cap-js/hana.

When the query is currently being fired to HANA the execution plan looks like the following and the highlighted node applies the filter. Which shows that HANA is also first materializing the whole view before filtering it.

When running the optimized query the execution plan looks much more similar to that of Postgres, but it is not yet exactly on par. As there is an hash join executed which skips an additional index scan optimization which could be applied.

There have been limitations in the past when using ? placeholders on HANA. So making sure to exclude this possibility I have flagged the key to be in lined with the SQL and this unlocked all the optimizations HANA provides. Directly applying the ID filter and only working on the single row it produces. Gaining an 100x performance improvement over the exact same SQL statement that leverages an ? placeholder for the ID compare. But this optimization is only possible if foreign keys are indexed as suggested by: #887