Re: pull-up subquery if JOIN-ON contains refs to upper-query

Hi, all! I updated the patch and it looks nice. All the problems have 
been solved.

On 03.04.2025 16:56, Ilia Evdokimov wrote:
>
> On 02.04.2025 19:39, Alena Rybakina wrote:
>>
>> I see that I need to add a walker that, when traversing the tree, 
>> determines whether there are conditions under which pull-up is 
>> impossible - the presence of
>> volatility of functions and other restrictions, and leave the 
>> transformation for the var objects that I added before, I described 
>> it here.
>>
>
> I have some concerns about pulling up every clause from the subquery 
> with one column. In particular, not every clause is safe or beneficial 
> to pull up: OR-clauses, CASE expressions, nested sublinks could 
> significantly change how the planner estimates the number of rows or 
> applies filters, especially when they are not true join predicates. 
> Pulling them up might lead to worse plans, or even change the 
> semantics in subtle ways. I think before applying such 
> transformations, we should make sure they are not only safe but 
> actually improve the resulting plan.

There may indeed be cases where a query plan without pull-up is worse 
than with pull-up.

For example, as shown below, with pull-up we don't need to scan two 
tables and perform a join, since the subquery returns 0 rows (no 
matching tuples in the inner sequential scan in a parameterized Nested 
Loop).
However, this cannot be detected at the current planning stage - we 
simply don't have that information yet.

Do you have any ideas on how to solve this problem? So far, the only 
approach I see is to try an alternative plan but I'm still learning this.

For example:


create table t(x int);
create table t1(x int);
create table t2(x int);

insert into t2 select id from generate_series(20001,30000) as id;
insert into t1 select id from generate_series(10001,20000) as id;
insert into t select id from generate_series(1,10000) as id;
vacuum analyze;
explain analyze select * from t where exists (select * from t1 join t2 
on t.x = t1.x);

with my patch:
                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=1500540.00..1500822.50 rows=10000 width=4) (actual 
time=70694.658..70694.662 rows=0.00 loops=1)
    Hash Cond: (t.x = t1.x)
    Buffers: shared hit=135
*->* *Seq Scan on t*  (cost=0.00..145.00 rows=10000 width=4) (actual 
time=0.009..1.545 *rows=10000.00* loops=1)
          Buffers: shared hit=45
    ->  Hash  (cost=1500415.00..1500415.00 rows=10000 width=4) (actual 
time=70690.524..70690.526 rows=10000.00 loops=1)
          Buckets: 16384  Batches: 1  Memory Usage: 480kB
          Buffers: shared hit=90
          ->  HashAggregate  (cost=1500315.00..1500415.00 rows=10000 
width=4) (actual time=70683.143..70686.590 rows=10000.00 loops=1)
                Group Key: t1.x
                Batches: 1  Memory Usage: 793kB
                Buffers: shared hit=90
*->* *Nested Loop* (cost=0.00..1250315.00 rows=100000000 width=4) 
(actual time=0.019..25650.447 *rows=100000000.00* loops=1)
                      Buffers: shared hit=90
*->  Seq Scan on t1* (cost=0.00..145.00 rows=10000 width=4) (actual 
time=0.006..4.931 *rows=10000.00* loops=1)
                            Buffers: shared hit=45
                      ->  Materialize  (cost=0.00..195.00 rows=10000 
width=0) (actual time=0.000..0.875 rows=10000.00 loops=10000)
                            Storage: Memory  Maximum Storage: 519kB
                            Buffers: shared hit=45
                            ->  Seq Scan on t2  (cost=0.00..145.00 
rows=10000 width=0) (actual time=0.007..1.246 rows=10000.00 loops=1)
                                  Buffers: shared hit=45
  Planning:
    Buffers: shared hit=36 read=3
  Planning Time: 0.375 ms
*Execution Time: 70695.154 ms*


without my patch:

                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
*Seq Scan on t*  (cost=0.00..309.30 rows=5738 width=4) (actual 
time=68268.562..68268.565 *rows=0.00* loops=1)
    Filter: EXISTS(SubPlan 1)
    Rows Removed by Filter: 10000
    Buffers: shared hit=900045
    SubPlan 1
      -> *Nested Loop*  (cost=0.00..8524.27 rows=654075 width=0) (actual 
time=6.823..6.823 *rows=0.00* loops=10000)
            Buffers: shared hit=900000
            ->  Seq Scan on t2  (cost=0.00..159.75 rows=11475 width=0) 
(actual time=0.011..1.660 rows=10000.00 loops=10000)
                  Buffers: shared hit=450000
            ->  Materialize  (cost=0.00..188.72 rows=57 width=0) (actual 
time=0.000..0.000 rows=0.00 loops=100000000)
                  Storage: Memory  Maximum Storage: 17kB
                  Buffers: shared hit=450000
                  ->  Seq Scan on t1  (cost=0.00..188.44 rows=57 
width=0) (actual time=2.403..2.403 *rows=0.00* loops=10000)
                        Filter: (t.x = x)
                        Rows Removed by Filter: 10000
                        Buffers: shared hit=450000
  Planning:
    Buffers: shared hit=40 read=16
  Planning Time: 0.487 ms
  Execution Time: *68268.600 ms*


-- 
Regards,
Alena Rybakina
Postgres Professional