CODEWITHSUNDEEP
postgresqlpostgresql-performancequery-planner
Eugen Konkov
Eugen Konkov

Reputation: 25113

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

Upvotes: 2

Views: 55

Answers (2)

Eugen Konkov
Eugen Konkov

Reputation: 25113

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

Upvotes: 2

eshirvana
eshirvana

Reputation: 24568

because the slow query , aggregation function runs for all the rows that meet condition sq.agreement_id = agreement_id which is 1346 rows and then you filter the result of subquery while the fast query you filter the result right off the bat and subquery only returns 4 rows thus only aggregation only runs for 4 rows.

also If you look at index condition for #4 Bitmap Index Scan , because of your condition sql optimizer has to look up these conditions :

((o_1.sys_period @> sys_time()) 
AND (o_1.app_period && '["2021-01-01 00:00:00+02","2021-02-01 00:00:00+02")'::tstzrange))

while in the fast query , index condition is

(o_1.agreement_id = 3385)

so those conditions in the slow query are postponed to #3 Bitmap Heap Scan which only applies to 4 rows for fast query.

Upvotes: 0

Related Questions