postgresqlpostgresql-performancequery-planner

Why optimization is not applied/different index is used?

I have two pretty similar queries:

WITH USAGE AS NOT MATERIALIZED ( SELECT
  ocd.*,
  1 AS conf_suma
FROM(
        SELECT o, o.agreement_id AS agreement_id
        FROM "order_bt" o
        WHERE o.sys_period @> sys_time()
          AND  (o.app_period && tstzrange( '2021-01-01', '2021-02-01' ))
)ocd
)
SELECT 
  *,
  (conf_suma/6) ::numeric( 10, 2 ) as group_nds,
  (SELECT sum(conf_suma) from USAGE sq WHERE sq.agreement_id = agreement_id) as total_suma -- #1 SLOW
  -- #2 FAST: (SELECT sum(conf_suma) from USAGE sq WHERE sq.agreement_id = 3385) as total_suma
FROM USAGE
  WHERE agreement_id = 3385   -- LAST WHERE

They are differ only by this part sq.agreement_id = agreement_id VS sq.agreement_id = 3385

Plan for them are #1 slow and #2 fast

#1 slow enter image description here

#2 fast enter image description here

Why optimization is not applied at first case? To my mind optimizer could see from LAST WHERE that there is only one agreement_id. So agreement_id is like constant. If we fold this constant into slow subquery it becomes same as fast subquery. Is this place for planner improvement?

PS. At one my query on production this cause query to run 12sec instead of 20ms

Solution

  • Thanks to RhodiumToad at IRC. Mistake was at agreement_id name.
    It must be written as usage.agreement_id:

    (SELECT sum(conf_suma) from USAGE sq WHERE sq.agreement_id = usage.agreement_id) as total_suma -- #1 NOW IT IS FAST TOO

    Now plan fine. As expected by me: enter image description here