Question of the Performance on TPC-H Benchmark Query 2

**Describe the problem**

For the query 2 in TPC-H benchmark:

```sql
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
PART,
SUPPLIER,
PARTSUPP,
NATION,
REGION
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 30
and p_type like '%STEEL'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
PARTSUPP,
SUPPLIER,
NATION,
REGION
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit
100;
```

Its execution time is 11.3s, as shown in its query plan
[plan_before.txt](https://github.com/user-attachments/files/17692515/plan_before.txt)

I found that disabling the following IF code block brings a significant performance improvement:
```patch
diff --git a/pkg/sql/opt/norm/join_funcs.go b/pkg/sql/opt/norm/join_funcs.go
index 67bc2a1ddd6..af31db89ecb 100644
--- a/pkg/sql/opt/norm/join_funcs.go
+++ b/pkg/sql/opt/norm/join_funcs.go
@@ -519,9 +519,6 @@ func (c *CustomFuncs) CanExtractJoinComparison(

// Disallow cases when one side has a correlated subquery.
// TODO(radu): investigate relaxing this.
- if leftProps.HasCorrelatedSubquery || rightProps.HasCorrelatedSubquery {
- return false
- }

if leftProps.OuterCols.Empty() || rightProps.OuterCols.Empty() {
// It's possible for one side to have no outer cols and still not be a
```

Its execution time is reduced to 0.471s, as shown in the new query plan
[plan_after.txt](https://github.com/user-attachments/files/17692530/plan_after.txt)

I wonder whether we can relax this IF condition to enable the second query plan in default, as it is more efficient than the first one.

**To Reproduce**

```sql
cockroach start-single-node --insecure --store=/app/data --listen-addr=0.0.0.0:36257 --sql-addr=0.0.0.0:26257
cockroach workload init tpch
```
Then execute the above query.

**Environment:**
- CockroachDB version [dcb0d27]
- Server OS: [Ubuntu]
- Client app [`psql "postgresql://root@127.0.0.1:26257/tpch"`]

Jira issue: CRDB-44256