Thread: 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