Improve: The optimization time grows exponentially as the number of subqueries increases

[chenzl25]

The optimization time grows exponentially as the number of subqueries increases which could lead to tokio runtime thread pool can't schedule other tasks and cause starving.

create table t1(a int, b int);
create table t2(c int primary key, d int);

-- 4s to optimize
explain
select 
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0),
COALESCE((SELECT b FROM t2 WHERE t1.a = t2.c), 0)
from t1;

[chenzl25]

Related issue #16837

[chenzl25]

Add another case.

create table t1(a int, b int);
create table t2(c int primary key, d int);
create table t3(e int, f int);
create table t4(g int, h int);
create table t5(i int, j int);
create table t6(k int, l int);

select 
COALESCE((SELECT sum(d) FROM t2 left join t3 on e = a and f = c left join t4 on g = a and h = c left join t5 on i = a and j = c WHERE t1.a = t2.c and t1.a = t2.c and j in (select k from t6 where b = l) ), 0),
COALESCE((SELECT sum(d) FROM t2 left join t3 on e = a and f = c left join t4 on g = a and h = c left join t5 on i = a and j = c WHERE t1.a = t2.c and t1.a = t2.c and j in (select k from t6 where b = l) ), 0),
COALESCE((SELECT sum(d) FROM t2 left join t3 on e = a and f = c left join t4 on g = a and h = c left join t5 on i = a and j = c WHERE t1.a = t2.c and t1.a = t2.c and j in (select k from t6 where b = l) ), 0),
COALESCE((SELECT sum(d) FROM t2 left join t3 on e = a and f = c left join t4 on g = a and h = c left join t5 on i = a and j = c WHERE t1.a = t2.c and t1.a = t2.c and j in (select k from t6 where b = l) ), 0),
COALESCE((SELECT sum(d) FROM t2 left join t3 on e = a and f = c left join t4 on g = a and h = c left join t5 on i = a and j = c WHERE t1.a = t2.c and t1.a = t2.c and j in (select k from t6 where b = l) ), 0),
from t1;