Re: A new strategy for pull-up correlated ANY_SUBLINK

Hi Alena,

On Thu, Oct 12, 2023 at 5:01 AM Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
wrote:

> Hi!
>
> I reviewed your patch and it was interesting for me!
>
> Thank you for the explanation. It was really informative for me!
>
Thanks for your interest in this, and I am glad to know it is informative.

> Unfortunately, I found a request when sublink did not pull-up, as in the
>
examples above. I couldn't quite figure out why.
>
I'm not sure what you mean with the "above", I guess it should be the
"below"?

> explain (analyze, costs off, buffers)
> select b.x, b.x, a.y
> from b
> left join a
> on b.x=a.x and
>
> *b.t in (select max(a0.t) *
> from a a0
> where a0.x = b.x and
> a0.t = b.t);
>
...

> SubPlan 2
>

Here the sublink can't be pulled up because of its reference to
the LHS of left join, the original logic is that no matter the 'b.t in ..'
returns the true or false, the rows in LHS will be returned. If we
pull it up to LHS, some rows in LHS will be filtered out, which
breaks its original semantics.

I thought it would be:
>
> explain (analyze, costs off, buffers)
> select b.x, b.x, a.y
> from b
> left join a on
> b.x=a.x and
>
> *b.t = (select max(a0.t) *
> from a a0
> where a0.x = b.x and
> a0.t <= b.t);
> QUERY
> PLAN
>
> ---------------------------------------------------------------------------------------------------------------------
> Hash Right Join (actual time=1.181..67.927 rows=1000 loops=1)
> Hash Cond: (a.x = b.x)
> *Join Filter: (b.t = (SubPlan 2))*
> Buffers: shared hit=3546
> -> Seq Scan on a (actual time=0.022..17.109 rows=100000 loops=1)
> Buffers: shared hit=541
> -> Hash (actual time=1.065..1.068 rows=1000 loops=1)
> Buckets: 4096 Batches: 1 Memory Usage: 72kB
> Buffers: shared hit=5
> -> Seq Scan on b (actual time=0.049..0.401 rows=1000 loops=1)
> Buffers: shared hit=5
> SubPlan 2
> -> Result (actual time=0.025..0.025 rows=1 loops=1000)
> Buffers: shared hit=3000
> InitPlan 1 (returns $2)
> -> Limit (actual time=0.024..0.024 rows=1 loops=1000)
> Buffers: shared hit=3000
> -> Index Only Scan Backward using a_t_x_idx on a a0
> (actual time=0.023..0.023 rows=1 loops=1000)
> Index Cond: ((t IS NOT NULL) AND (t <= b.t) AND
> (x = b.x))
> Heap Fetches: 1000
> Buffers: shared hit=3000
> Planning Time: 0.689 ms
> Execution Time: 68.220 ms
> (23 rows)
>
> If you noticed, it became possible after replacing the "in" operator with
> "=".
>
I didn't notice much difference between the 'in' and '=', maybe I
missed something?

> I took the liberty of adding this to your patch and added myself as
> reviewer, if you don't mind.
>
Sure, the patch after your modification looks better than the original.
I'm not sure how the test case around "because of got one row" is
relevant to the current changes. After we reach to some agreement
on the above discussion, I think v4 is good for committer to review!

--
Best Regards
Andy Fan