[YSQL] Bucketized Scan on partition table is 17 times slower than a YB Batched Nested Loop Join

[dhruva4869]

Jira Link: DB-20250

Description

Version-2.29.0.0-b453
Query-

with seed as (
    select c1
    from t100
    where bucketid in (0,1,2)
)
select seed.c1, t.c2, t.c3
from seed,
lateral (
    select a.c2, b.c3
    from t10000 a
    full join t10000 b
      on a.c1 = b.c1
    where a.c1 = seed.c1
      and coalesce(a.c2,0) > 10
      and greatest(a.c3, b.c3, 0) < 100
      and least(a.c4, b.c4, 1000) > 0
      and abs(a.c2 - b.c2) % 7 in (1,3,5)

) t
order by t.c2, t.c3

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[andrei-mart]

What was different between the runs?
The after plan costs 6x, why it was selected? Were hints used?
Parallel plan's expected overhead is about 50ms, costing reflects that. I don't see what's the issue here.