Request for improvement: Allow to push (t.o).id via GROUP BY ocd.o

From: Eugen Konkov <kes-kes(at)yandex(dot)ru>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Request for improvement: Allow to push (t.o).id via GROUP BY ocd.o
Date: 2019-07-12 10:04:27
Message-ID: 189018755.20190712130427@yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Eugen Konkov 2019-07-12 10:32:49 Re: Request for improvement: Allow to push (t.o).id via GROUP BY ocd.o
Previous Message Julien Rouhaud 2019-07-12 09:47:52 Re: Add parallelism and glibc dependent only options to reindexdb