Обсуждение: Request for improvement: Allow to push (t.o).id via GROUP BY ocd.o

Hello PostgreSQL-development,

something's stopping the planner from being able to deduce that (t.o).id is safe to push through a GROUP BY ocd.o

        SELECT * FROM (
                        SELECT
                                sum( t.group_suma ) OVER( PARTITION BY t.id ) AS total_suma,
--                              sum( t.group_suma ) OVER( PARTITION BY (t.o).id ) AS total_suma,  -- For any WHERE this
takes2700ms
 
                                *
                        FROM (
                                SELECT
                                 sum( ocd.item_cost     ) AS group_cost,
                                 sum( ocd.item_suma     ) AS group_suma,
                                 max( (ocd.ic).consumed ) AS consumed,
                                 (ocd.ic).consumed_period,
                                 ocd.o,
                                 (ocd.o).id
                                FROM order_cost_details( tstzrange( '2019-04-01', '2019-05-01' ) ) ocd
                                GROUP BY ocd.o, (ocd.o).id, (ocd.ic).consumed_period
                        ) t
        ) t
        WHERE t.id = 6154 AND t.consumed_period @> '2019-04-01'::timestamptz       -- This takes 2ms
--      WHERE (t.o).id = 6154 AND t.consumed_period @> '2019-04-01'::timestamptz   -- This takes 2700ms


More info is here: https://stackoverflow.com/q/57003113/4632019

-- 
Best regards,
Eugen Konkov

Hello

to my mind I may be done, because `id` is primary key of `o` table

Friday, July 12, 2019, 1:04:27 PM, you wrote:

> Hello PostgreSQL-development,

> something's stopping the planner from being able to deduce that
> (t.o).id is safe to push through a GROUP BY ocd.o

>         SELECT * FROM (
>                         SELECT
>                                 sum( t.group_suma ) OVER( PARTITION BY t.id ) AS total_suma,
> --                              sum( t.group_suma ) OVER( PARTITION
> BY (t.o).id ) AS total_suma,  -- For any WHERE this takes 2700ms
>                                 *
>                         FROM (
>                                 SELECT
>                                  sum( ocd.item_cost     ) AS group_cost,
>                                  sum( ocd.item_suma     ) AS group_suma,
>                                  max( (ocd.ic).consumed ) AS consumed,
>                                  (ocd.ic).consumed_period,
>                                  ocd.o,
>                                  (ocd.o).id
>                                 FROM order_cost_details( tstzrange(
> '2019-04-01', '2019-05-01' ) ) ocd
>                                 GROUP BY ocd.o, (ocd.o).id, (ocd.ic).consumed_period
>                         ) t
>         ) t
>         WHERE t.id = 6154 AND t.consumed_period @>
> '2019-04-01'::timestamptz       -- This takes 2ms
> --      WHERE (t.o).id = 6154 AND t.consumed_period @>
> '2019-04-01'::timestamptz   -- This takes 2700ms


> More info is here: https://stackoverflow.com/q/57003113/4632019




-- 
Best regards,
Eugen Konkov

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