leborchuk commented on PR #1762: URL: https://github.com/apache/cloudberry/pull/1762#issuecomment-4876762835
### 3. Sublink-to-Join Conversion for Nested Arithmetic Expressions tl;dr - let's make an exception for functions - do not transform expressions contains user functions, maybe except IMMUTABLE or STABLE one - for discussion. #### The whole idea discussion The whole idea is simple, I agree with it, why not to unnest the whole expression, why we limit transformation for a simple sentences? The search through pg hackers shows that Heike already tried to do the same in PG: Heikki Linnakangas, May 2017 [Pulling up more complicated subqueries](https://www.postgresql.org/message-id/[email protected]) He uses TPC-DS Q6 as the motivating example: ``` SELECT * FROM foo WHERE foo.j >= 1.2 * (SELECT avg(bar.j) FROM bar WHERE foo.i = bar.i); ``` Key quote: > "The planner can pull up simpler subqueries, converting them to joins, but unfortunately this case is beyond its capabilities." He proposes the manual rewrite: ``` SELECT * FROM foo LEFT JOIN (SELECT avg(bar.j) AS avg, bar.i FROM bar GROUP BY bar.i) AS avg_bar ON foo.i = avg_bar.i WHERE foo.j >= 1.2 * avg_bar.avg; ``` And propose a multi-step roadmap. The issue is that never fully landed in core ) We could ask Heike why so )) , but he cited Tom Lane from the 2011's thread > "Thinking of it as a pull-up or push-down transformation is the wrong approach because those sorts of transformations are done too early to be able to use cost comparisons." But! We have https://github.com/apache/cloudberry/commit/dcdc6c0b34bd7184f780f6e2f79f337937435476 commited by Heike ) It's remind me Surajit Chaudhuri idea to move aggregation through the join tree https://vldb.org/conf/1994/P354.PDF There is one remaining part to move - perform unnesting. Let's do it ) #### Implementation discussion I've made a test: ``` create table test(id int, price float, category int); CREATE OR REPLACE FUNCTION increment(f float) RETURNS float AS $$ BEGIN RETURN f + 1; END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# explain verbose select * from test i where i.price > 12 + 1.2 * (select increment(avg(j.price)) from test j where j.category = i.category); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (cost=541.17..1269.50 rows=23700 width=16) Output: i.id, i.price, i.category -> Hash Join (cost=541.17..953.50 rows=7900 width=16) Output: i.id, i.price, i.category Inner Unique: true Hash Cond: (i.category = "Expr_SUBQUERY".csq_c0) Join Filter: (i.price > ('12'::double precision + ('1.2'::double precision * "Expr_SUBQUERY".csq_c1))) -> Seq Scan on public.test i (cost=0.00..271.00 rows=23700 width=16) Output: i.id, i.price, i.category -> Hash (cost=528.67..528.67 rows=1000 width=12) Output: "Expr_SUBQUERY".csq_c1, "Expr_SUBQUERY".csq_c0 -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=424.50..528.67 rows=1000 width=12) Output: "Expr_SUBQUERY".csq_c1, "Expr_SUBQUERY".csq_c0 -> Subquery Scan on "Expr_SUBQUERY" (cost=424.50..515.33 rows=333 width=12) Output: "Expr_SUBQUERY".csq_c1, "Expr_SUBQUERY".csq_c0 -> Finalize HashAggregate (cost=424.50..512.00 rows=333 width=12) Output: j.category, increment(avg(j.price)) Group Key: j.category -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=389.50..419.50 rows=1000 width=36) Output: j.category, (PARTIAL avg(j.price)) Hash Key: j.category -> Streaming Partial HashAggregate (cost=389.50..399.50 rows=1000 width=36) Output: j.category, PARTIAL avg(j.price) Group Key: j.category -> Seq Scan on public.test j (cost=0.00..271.00 rows=23700 width=12) Output: j.id, j.price, j.category Settings: optimizer = 'off' Optimizer: Postgres query optimizer (28 rows) ``` Here you could see that we perform `increment` for the whole dataset and only then join. But we do not know what actually `increment` does, it could contain a cumbersome users logic and . So we cannot move it somewhere and function should be left in place. Except maybe `IMMUTABLE` or `STABLE` one. My experience tells me they are safe and could be moved. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
