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