Performance of ROW_NUMBER() using Firebird 4.0.6

[Damon Schultz]

Hello Firebird Support,

I am having performance issues when using ROW_NUMBER() in a LEFT OUTER JOIN. Here is a minimal reproducible example, using CTEs to make the semantics clear (but can reproduce when including the CTEs inline in the LEFT OUTER JOIN relation):

WITH all_qualifiers AS (

     SELECT q.morb_no

            , q.measure_type_no

            , q.measure_ref_type_no

     FROM pat_measure q

     WHERE q.measure_type_no IN (1815, 1817, 1816)

            AND q.measure_ref_type_no NOT IN (2747, 2767, 2759)

),

current_codes AS (

    SELECT pat_id

        , MAX(measure_ref_type_no) FILTER (WHERE measure_type_no = 1815) q_dis2

        , MAX(measure_ref_type_no) FILTER (WHERE measure_type_no = 1817) q_hhc2

        , MAX(measure_ref_type_no) FILTER (WHERE measure_type_no = 1816) q_acc2

    FROM (

        SELECT aq.measure_type_no

            , aq.measure_ref_type_no

            , ci.pat_id

            , ROW_NUMBER() over (

                PARTITION BY ci.pat_id, aq.measure_type_no

                ORDER BY pat_morb_act_date DESC, ci.morb_no DESC

            ) rn

        FROM patient_morbidity ci

        INNER JOIN all_qualifiers aq ON ci.morb_no = aq.morb_no

        )

    WHERE rn = 1

    GROUP BY pat_id

)

SELECT p.pat_id

    , q_dis2

    , q_acc2

    , q_hhc2

FROM patient p

INNER JOIN pat_morb_view ci ON p.pat_id = ci.pat_id

LEFT OUTER JOIN current_codes q ON p.pat_id = q.pat_id

WHERE ci.morb_type_no IN (2136, 2134)

    AND ci.pat_morb_status = 'C'

    AND ci.pat_morb_act_date BETWEEN CAST(:first_date_to_report AS DATE)

                                 AND CAST(:last_date_to_report AS DATE)

    AND (p.current_status <> 'SF' OR p.current_status IS NULL)

I note Issue 8372 is not closed. To my eye untrained in query optimisation, it seems like this could explain it but I'm not sufficiently skilled in query optimisation to be confident.

If it does, is there a way to improve the performance (for example, by using a PLAN clause)?

Looking forward to assistance anyone can offer.

Best wishes,

Damon