[SQL] Optimizing a cumulative price calculation query

I have a "pricing engine" query that is getting the cost of a variant (per source country), and is calculating taxes & margins in a cumulative fashion (one on top of the other and so forth ex. (cost * tariff)*tariff etc...) according to the order in which they need to be applied, in order to calculate the final price per channel, wholesale, b2b etc.

The query does work using recision but it's rather heavy. I've tried to accomplish the same thing using window functions. But I just do not get the correct result at the end.

Any ideas/suggestions on how I can optimize and make it way more performant?

WITH RECURSIVE __static AS (
SELECT NOW() AS __t_now
),
raw AS (
SELECT
pp.p_var_id,

pp.r_ch_id,
_ch.channel,

pp.r_pl_c_id, -- source country
_c.source_r_pl_c_id,
_c.source_r_ccy_id,

_c.splr_acct_id,
_c.source_cost,

_br.bkng_rt_id,
_br.rate AS rate, -- default to 1 -- no rate defined

_c.source_cost * COALESCE(_br.rate, 1) AS target_cost,
_ch.r_ccy_id AS target_r_ccy_id,
_pt.position,

pp.p_pr_id,

COALESCE(pp.p_pr_ty_id, _pc.p_pr_ty_id) AS p_pr_ty_id,
COALESCE(pp.tariff, _pc.tariff, 0) AS tariff_normalized,
COALESCE(pp.fixed, _pc.fixed, 0) AS fixed_normalized,

COALESCE(pp.tariff, _pc.tariff) AS tariff,
COALESCE(pp.fixed, _pc.fixed) AS fixed,

ROW_NUMBER()
OVER (
PARTITION BY
pp.p_var_id,
pp.r_pl_c_id,
_c.source_cost,
_c.source_r_pl_c_id,
_c.source_r_ccy_id,
_c.splr_acct_id,
pp.r_ch_id,
_br.bkng_rt_id,
_br.rate
ORDER BY _pt.position DESC
) AS row_number


FROM prices pp
CROSS JOIN __static

LEFT JOIN price_components _pc on _pc.p_pr_cmp_id = pp.p_pr_cmp_id
LEFT JOIN price_types _pt on _pt.p_pr_ty_id = COALESCE(pp.p_pr_ty_id, _pc.p_pr_ty_id)
LEFT JOIN channels _ch ON pp.r_ch_id = _ch.r_ch_id AND _ch.active IS TRUE

LEFT JOIN LATERAL (
SELECT DISTINCT ON (c.p_var_id, c.splr_acct_id)
c.p_var_id,
c.splr_acct_id,
c.cost AS source_cost,
c.bkng_rt_src_id,
c.r_ccy_id AS source_r_ccy_id,
c.r_pl_c_id AS source_r_pl_c_id
FROM costs c
WHERE
c.r_pl_c_id = pp.r_pl_c_id -- match cost source country to price source country (new)
AND __static.__t_now BETWEEN c.t_from AND c.t_until
ORDER BY c.p_var_id, c.splr_acct_id, c.t DESC
) _c ON pp.p_var_id = _c.p_var_id

LEFT JOIN LATERAL (
SELECT DISTINCT ON (br.bkng_rt_src_id, br.source_r_ccy_id, br.target_r_ccy_id)
br.bkng_rt_id,
br.bkng_rt_src_id,
br.rate
FROM rates br
WHERE
_c.source_r_ccy_id <> _ch.r_ccy_id -- Only join if conversion is needed
AND br.source_r_ccy_id = _c.source_r_ccy_id --cost source ccy
AND br.target_r_ccy_id = _ch.r_ccy_id --channel target ccy
AND br.bkng_rt_src_id = _c.bkng_rt_src_id
AND __static.__t_now >= br.t_from
AND br.deleted IS FALSE

ORDER BY br.bkng_rt_src_id, br.source_r_ccy_id, br.target_r_ccy_id, br.t_from DESC
) _br ON _c.bkng_rt_src_id = _br.bkng_rt_src_id

WHERE __static.__t_now BETWEEN pp.t_from AND pp.t_until
GROUP BY
__static.__t_now,
_c.p_var_id, _c.source_cost,
pp.r_pl_c_id, _c.source_r_pl_c_id,
_c.source_r_ccy_id, _c.splr_acct_id, _ch.r_ccy_id,
pp.p_var_id, pp.r_ch_id,
_ch.r_ch_id, _ch.channel, _br.bkng_rt_id, _br.rate,
_pt.position,
pp.p_pr_ty_id, _pc.p_pr_ty_id,
pp.p_pr_id,
pp.tariff, _pc.tariff,
pp.fixed, _pc.fixed
),
calc AS (
SELECT *,

target_cost + (target_cost * tariff_normalized) + fixed_normalized AS cumulative, -- Apply first tariff

jsonb_build_array(
jsonb_build_object(
'p_pr_id', p_pr_id,
'p_pr_ty_id', p_pr_ty_id,
'tariff', trim_scale(tariff),
'fixed', trim_scale(fixed),
'subtotal', trim_scale((target_cost * tariff_normalized) + fixed_normalized)
)
) AS components

FROM raw
WHERE row_number = 1 -- Start with the highest position tariff

UNION ALL

SELECT raw.*,

cc.cumulative + (cc.cumulative * raw.tariff_normalized) + raw.fixed_normalized AS cumulative, -- Apply each subsequent tariff

cc.components || jsonb_build_object(
'p_pr_id', raw.p_pr_id,
'p_pr_ty_id', raw.p_pr_ty_id,
'tariff', trim_scale(raw.tariff),
'fixed', trim_scale(raw.fixed),
'subtotal', trim_scale((cc.cumulative * raw.tariff_normalized) + raw.fixed_normalized)
) AS components

FROM calc cc
JOIN raw ON
cc.p_var_id = raw.p_var_id
AND cc.r_pl_c_id = raw.r_pl_c_id
AND cc.source_cost = raw.source_cost
AND cc.source_r_pl_c_id = raw.source_r_pl_c_id
AND cc.source_r_ccy_id = raw.source_r_ccy_id
AND cc.splr_acct_id IS NOT DISTINCT FROM raw.splr_acct_id
AND cc.r_ch_id = raw.r_ch_id
AND cc.bkng_rt_id IS NOT DISTINCT FROM raw.bkng_rt_id
AND cc.rate IS NOT DISTINCT FROM raw.rate
AND cc.row_number + 1 = raw.row_number -- Join on the next lower tariff
)

SELECT *
FROM calc c
WHERE row_number = (
SELECT MAX(raw.row_number)
FROM raw
WHERE
p_var_id = c.p_var_id
AND r_pl_c_id = c.r_pl_c_id
AND source_cost = c.source_cost
AND source_r_pl_c_id = c.source_r_pl_c_id
AND source_r_ccy_id = c.source_r_ccy_id
AND splr_acct_id IS NOT DISTINCT FROM c.splr_acct_id
AND r_ch_id = c.r_ch_id
AND bkng_rt_id IS NOT DISTINCT FROM c.bkng_rt_id
AND rate IS NOT DISTINCT FROM c.rate
)
;


Please find a live version here: https://www.db-fiddle.com/f/vnM3o5RZnhvyNgSqr57w66/0

PS. This is meant to go into a materialized view (thats why it's calculating everything). But I would still like to optimize the heck out of it, because I will need to readapt it in order to get the price for a single product.


Correct result:

+--------+-------+--------+---------+----------------+---------------+------------+-----------+----------+---------+-----------+---------------+--------+-------+----------+-----------------+----------------+------+-----+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|p_var_id|r_ch_id|r_pl_c_id|source_r_pl_c_id|source_r_ccy_id|splr_acct_id|source_cost|bkng_rt_id|rate |target_cost|target_r_ccy_id|position|p_pr_id|p_pr_ty_id|tariff_normalized|fixed_normalized|tariff|fixed|row_number|cumulative |components |
+--------+-------+--------+---------+----------------+---------------+------------+-----------+----------+---------+-----------+---------------+--------+-------+----------+-----------------+----------------+------+-----+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|1 |4 |807 |807 |978 |1 |100.000000 |null |null |100 |978 |70 |33 |4 |0.35 |0 |0.3500|null |2 |152.55 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 34, "subtotal": 13, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 33, "subtotal": 39.55, "p_pr_ty_id": 4}] |
|1 |4 |807 |807 |807 |null |2000.000000|6 |0.016129 |32.258 |978 |70 |33 |4 |0.35 |0 |0.3500|null |2 |49.209579 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 34, "subtotal": 4.19354, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 33, "subtotal": 12.758039, "p_pr_ty_id": 4}] |
|1 |1 |807 |807 |978 |1 |100.000000 |1 |61.696400|6169.64 |807 |1 |19 |1 |0.18 |0 |0.1800|null |4 |11110.0372676|[{"fixed": null, "tariff": 0.13, "p_pr_id": 28, "subtotal": 802.0532, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 26, "subtotal": 2440.09262, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 27, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 19, "subtotal": 1694.7514476, "p_pr_ty_id": 1}]|
|1 |2 |807 |807 |978 |1 |100.000000 |1 |61.696400|6169.64 |807 |1 |31 |1 |0.18 |0 |0.1800|null |4 |11932.6970652|[{"fixed": null, "tariff": 0.13, "p_pr_id": 32, "subtotal": 802.0532, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.45, "p_pr_id": 29, "subtotal": 3137.26194, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 30, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 31, "subtotal": 1820.2419252, "p_pr_ty_id": 1}]|
|1 |1 |807 |807 |807 |null |2000.000000|null |null |2000 |807 |1 |19 |1 |0.18 |0 |0.1800|null |4 |3604.31 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 28, "subtotal": 260, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 26, "subtotal": 791, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 27, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 19, "subtotal": 549.81, "p_pr_ty_id": 1}] |
|1 |2 |807 |807 |807 |null |2000.000000|null |null |2000 |807 |1 |31 |1 |0.18 |0 |0.1800|null |4 |3870.99 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 32, "subtotal": 260, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.45, "p_pr_id": 29, "subtotal": 1017, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 30, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 31, "subtotal": 590.49, "p_pr_ty_id": 1}] |
+--------+-------+--------+---------+----------------+---------------+------------+-----------+----------+---------+-----------+---------------+--------+-------+----------+-----------------+----------------+------+-----+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Continue reading...