sql/opt: DISTINCT inner join misses semi-join reduction when joined table contributes no output columns

**Describe the problem**

I observed a large performance gap between a `SELECT DISTINCT` query over an inner join and an equivalent `EXISTS` rewrite.

The query projects and deduplicates only columns from `t0` and `t3`. Table `t1` is only used to test whether at least one matching row exists for `t0.c0 = t1.c0`; no column from `t1` is projected or included in the `DISTINCT` key.

However, CockroachDB plans the original query as a regular inner join and produces all duplicate matches from `t1` before applying `DISTINCT`. In the attached reproduction, this creates 140,000,000 intermediate rows below the `DISTINCT` operator, while the final result has only 7 rows.

An equivalent `EXISTS` rewrite lets CockroachDB deduplicate the existence side, `t1.c0`, first and reduces the corresponding intermediate result to 280,000 rows.

This looks like a missed semi-join / duplicate-elimination optimization for `DISTINCT` over inner join.

Observed median execution times over three `EXPLAIN ANALYZE` runs:

```text
Query A: DISTINCT INNER JOIN
runs: 30.5s, 30.1s, 30.8s
median: 30.5s
rows below DISTINCT: 140,000,000
final rows: 7

Query B: EXISTS rewrite
runs: 12ms, 12ms, 13ms
median: 12ms
rows below DISTINCT: 280,000
final rows: 7

Control C: same join without DISTINCT
runs: 3ms, 2ms, 2ms
median: 2ms
```

So the semantically equivalent `EXISTS` form is about 2500x faster in this reproduction.

**To Reproduce**

1. Start a single-node CockroachDB cluster.

2. Run the attached SQL file:

```bash
cockroach sql --url 'postgresql://root@127.0.0.1:xxxxx/defaultdb?sslmode=disable' \
< distinct_semijoin_fanout_repro_scaled.sql \
> distinct_semijoin_fanout_repro_scaled_result.txt 2>&1
```

3. Compare `QUERY A: DISTINCT INNER JOIN` and `QUERY B: EXISTS REWRITE` in the output.

The reproduction creates three small tables:

```text
t0: 1600 rows
t1: 3500 rows
t3: 1600 rows
```

The data is constructed so that `t1` has many duplicate rows per join key. The raw inner join has a large fanout:

```text
raw_inner_join_rows: 140,000,000
existence_style_rows: 280,000
fanout_ratio: 500x
```

The original query shape is:

```sql
SELECT DISTINCT
t0.c1 AS ref0,
t3.c1 AS t3_c1,
t0.c0 AS t0_c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
JOIN t1 ON t0.c0 = t1.c0
ORDER BY t0.c0
LIMIT 8;
```

The equivalent rewrite is:

```sql
SELECT DISTINCT
t0.c1 AS ref0,
t3.c1 AS t3_c1,
t0.c0 AS t0_c0
FROM t3
JOIN t0 ON t3.c0 = t0.c0
WHERE EXISTS (
SELECT 1
FROM t1
WHERE t1.c0 = t0.c0
)
ORDER BY t0.c0
LIMIT 8;
```

Both queries return the same 7 rows, but the physical work is very different.

For the original `DISTINCT INNER JOIN` query, `EXPLAIN ANALYZE` reports:

```text
execution time: 30.5s / 30.1s / 30.8s
actual row count below DISTINCT: 140,000,000
actual row count after DISTINCT: 7
```

For the `EXISTS` rewrite, `EXPLAIN ANALYZE` reports:

```text
execution time: 12ms / 12ms / 13ms
actual row count below DISTINCT: 280,000
actual row count after DISTINCT: 7
```

The `EXISTS` plan includes a `distinct-on` over `t1.c0` before joining with `t0`, which avoids multiplying by all duplicate `t1` rows. The original `DISTINCT INNER JOIN` plan does not infer this reduction and instead pushes all duplicate join results into the final `DISTINCT`.

**Expected behavior**

I expected the optimizer to recognize that `t1` is only used as an existence side of the join because:

- no column from `t1` is projected;
- no column from `t1` is part of the `DISTINCT` key;
- the join predicate is an equality predicate on `t1.c0 = t0.c0`;
- duplicates from `t1` cannot change the final `DISTINCT` result.

Therefore, the optimizer could use a semi-join-style plan or deduplicate `t1.c0` before the join, similar to the explicit `EXISTS` rewrite.

In other words, the original query:

```sql
SELECT DISTINCT cols_from_t0_t3
FROM t3
JOIN t0 ON ...
JOIN t1 ON t1.c0 = t0.c0;
```

could be optimized similarly to:

```sql
SELECT DISTINCT cols_from_t0_t3
FROM t3
JOIN t0 ON ...
WHERE EXISTS (
SELECT 1 FROM t1 WHERE t1.c0 = t0.c0
);
```

or to a plan that first computes distinct join keys from `t1`.

**Additional data / screenshots**

I will attach:

- `distinct_semijoin_fanout_repro_scaled.sql`

[distinct_semijoin_fanout_repro_scaled.sql](https://github.com/user-attachments/files/29855645/distinct_semijoin_fanout_repro_scaled.sql)

- `distinct_semijoin_fanout_repro_scaled_result.txt`

[distinct_semijoin_fanout_repro_scaled_result.txt](https://github.com/user-attachments/files/29855655/distinct_semijoin_fanout_repro_scaled_result.txt)

The SQL file contains the schema, data generation, `ANALYZE`, fanout summary, result-cardinality check, and three `EXPLAIN ANALYZE` runs for each query variant.

Important observations from the attached output:

```text
CockroachDB version:
CockroachDB CCL v26.2.2

Data shape:
t0: 1600 rows
t1: 3500 rows
t3: 1600 rows

Total fanout summary:
raw_inner_join_rows: 140,000,000
existence_style_rows: 280,000
fanout_ratio: 500x

Query A: DISTINCT INNER JOIN
median execution time: 30.5s
actual row count below DISTINCT: 140,000,000
final row count: 7

Query B: EXISTS rewrite
median execution time: 12ms
actual row count below DISTINCT: 280,000
final row count: 7
```

**Environment:**

- CockroachDB version: 26.2.2
- Server OS: Ubuntu 22.04.4 LTS
- Client app: `cockroach sql`

**Additional context**

This issue has a significant performance impact when the joined table has many duplicate rows for the join key and the query uses `DISTINCT` over columns from other tables.

The query is semantically correct and returns the expected result, but the optimizer misses an opportunity to avoid duplicate-producing inner-join fanout. The explicit `EXISTS` form demonstrates that CockroachDB can execute the existence-style plan efficiently; the missing part seems to be recognizing the same opportunity from the `DISTINCT INNER JOIN` formulation.

GitHub suggested #172389 as a potential duplicate. That issue appears to be related to materializing unused columns. This report seems different: the main issue here is not just unused column materialization, but that a joined table which only provides existence information is not reduced to a semi-join or duplicate-eliminated input under `DISTINCT`, causing 140,000,000 duplicate join rows before the final `DISTINCT`.

Jira issue: CRDB-65633

Start by running distinct_semijoin_fanout_repro_scaled.sql with cockroach sql and compare the EXPLAIN ANALYZE plans for the DISTINCT inner join and EXISTS rewrite. Investigate the optimizer path that handles DISTINCT and inner joins; done means the original query avoids duplicate-producing fanout while preserving the seven-row result and improves its plan relative to the reproduction.

Written by the indexing model from the issue text.