Re: Request for improvement: Allow to push (t.o).id via GROUP BY ocd.o - Mailing list pgsql-hackers

And, probably, next query belongs to same issue:

SELECT
--next_ots.group_cost AS next_cost,
(SELECT next_ots FROM order_total_suma( next_range ) next_ots 
WHERE next_ots.order_id = ots.order_id  AND next_ots.consumed_period @> (ots.o).billed_to
) AS next_suma,  -- << this takes 111ms only
ots.* FROM (
        SELECT  
            tstzrange(
              NULLIF( (ots.o).billed_to, 'infinity' ),
              NULLIF( (ots.o).billed_to +p.interval, 'infinity' )
            ) as next_range,
         ots.*
        FROM order_total_suma() ots
        LEFT JOIN period p ON p.id = (ots.o).period_id
) ots
--LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = 6154  --<< this is fine
--  AND next_ots.consumed_period @> (ots.o).billed_to 
--LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = ots.order_id  --<< this takes 11500ms
--  AND next_ots.consumed_period @> (ots.o).billed_to 
WHERE ots.order_id IN ( 6154, 10805 )


id is not pushed for LEFT JOIN


I have attached plans:



-- 
Best regards,
Eugen Konkov