jiaqizho commented on PR #1096:
URL: https://github.com/apache/cloudberry/pull/1096#issuecomment-2918192922
> We need to be careful about VOLATILE and STABLE functions, for example:
>
> ```
> create table t1(v1 int, v2 int, v3 int);
> insert into t1 values(generate_series(1, 10), generate_series(11, 20),
generate_series(21, 30));
> analyze t1;
>
> create table t2(v1 int, v2 int, v3 int);
> insert into t2 values(generate_series(0, 100), generate_series(100, 200),
generate_series(200, 300));
>
> -- should pruned both seq scan and shared scan
> postgres=# explain verbose with c1 as (select v1, v2, random() v3 from t1)
select c11.v1 from c1 as c11 left join c1 as c22 on c11.v1=c22.v1 where c11.v1
< 5;
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------
> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.00 rows=5
width=4)
> Output: share0_ref3.v1
> -> Sequence (cost=0.00..1293.00 rows=2 width=4)
> Output: share0_ref3.v1
> -> Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=2
width=1)
> Output: share0_ref1.v1
> -> Seq Scan on public.t1 (cost=0.00..431.00 rows=2
width=4)
> Output: t1.v1
> Filter: (t1.v1 < 5)
> -> Hash Left Join (cost=0.00..862.00 rows=2 width=4)
> Output: share0_ref3.v1
> Hash Cond: (share0_ref3.v1 = share0_ref2.v1)
> -> Result (cost=0.00..431.00 rows=2 width=4)
> Output: share0_ref3.v1
> Filter: (share0_ref3.v1 < 5)
> -> Shared Scan (share slice:id 1:0)
(cost=0.00..431.00 rows=2 width=4)
> Output: share0_ref3.v1
> -> Hash (cost=431.00..431.00 rows=2 width=4)
> Output: share0_ref2.v1
> -> Result (cost=0.00..431.00 rows=2 width=4)
> Output: share0_ref2.v1
> Filter: (share0_ref2.v1 < 5)
> -> Shared Scan (share slice:id 1:0)
(cost=0.00..431.00 rows=2 width=4)
> Output: share0_ref2.v1
> Optimizer: GPORCA
> (25 rows)
> ```
>
> It is obvious we shouldn't prune v3 out safely here.
In fact, this is what ORCA's prune logic (specifically the `used/unused`
status setting for CColRef) does.
running follow SQL in **MAIN branch** :
ex.
```
explain verbose select c11.v1 from (select v1, v2, random() v3 from t1) as
c11 left join (select v1, v2, v3 from t1) as c22 on c11.v1=c22.v1 where c11.v1
< 5;
QUERY PLAN
----------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=5 width=4)
Output: t1.v1
-> Hash Left Join (cost=0.00..862.00 rows=2 width=4)
Output: t1.v1
Hash Cond: (t1.v1 = t1_1.v1)
-> Seq Scan on public.t1 (cost=0.00..431.00 rows=2 width=4)
Output: t1.v1
Filter: (t1.v1 < 5)
-> Hash (cost=431.00..431.00 rows=2 width=4)
Output: t1_1.v1
-> Seq Scan on public.t1 t1_1 (cost=0.00..431.00 rows=2
width=4)
Output: t1_1.v1
Filter: (t1_1.v1 < 5)
Settings: optimizer = 'on'
Optimizer: GPORCA
(15 rows)
CREATE OR REPLACE FUNCTION emp_output()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
RETURN 'emp';
END;
$$;
explain verbose select c11.v1 from (select v1, v2, emp_output() v3 from t1)
as c11 left join (select v1, v2, v3 from t1) as c22 on c11.v1=c22.v1 where
c11.v1 < 5;
QUERY PLAN
----------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=5 width=4)
Output: t1.v1
-> Hash Left Join (cost=0.00..862.00 rows=2 width=4)
Output: t1.v1
Hash Cond: (t1.v1 = t1_1.v1)
-> Seq Scan on public.t1 (cost=0.00..431.00 rows=2 width=4)
Output: t1.v1
Filter: (t1.v1 < 5)
-> Hash (cost=431.00..431.00 rows=2 width=4)
Output: t1_1.v1
-> Seq Scan on public.t1 t1_1 (cost=0.00..431.00 rows=2
width=4)
Output: t1_1.v1
Filter: (t1_1.v1 < 5)
Settings: optimizer = 'on'
Optimizer: GPORCA
(15 rows)
```
The function always been pruned.
--
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: commits-unsubscr...@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org
For additional commands, e-mail: commits-h...@cloudberry.apache.org
