[Performance]: Avoid full probe-table scans for right SINGLE joins without breaking multi-CN execution

[XuPeng-SH]

Is there an existing issue for performance?

• I have checked the existing issues.

Environment

• Version or commit-id: main at 946427b877c1b12c497743eff264047e60a00be2
• Deployment: two-CN make dev-up cluster
• Hardware parameters: 24 CPU cores; 6 GiB file-service memory cache per CN
• OS type: Linux

Details of Performance

A correlated scalar subquery can be flattened into a right SINGLE hash join when the preserved outer relation is much smaller than the inner table:

CREATE DATABASE rsingle_perf;
USE rsingle_perf;

CREATE TABLE big(id BIGINT PRIMARY KEY, v BIGINT);
INSERT INTO big
SELECT result, result * 10
FROM generate_series(1, 1000000, 1);

CREATE TABLE small(id BIGINT PRIMARY KEY);
INSERT INTO small VALUES (1), (5000000), (10000000);

ANALYZE TABLE big;
ANALYZE TABLE small;

SELECT s.id,
       (SELECT b.v FROM big b WHERE b.id = s.id) AS v
FROM small s
ORDER BY s.id;

The optimizer physically swaps the scalar SINGLE join so that the small preserved relation is the hash-build side and big is the probe table:

Join Type: RIGHT SINGLE
Join Cond: (b.id = s.id)
├── Table Scan: big    -- probe; unmatched rows are discarded
└── Table Scan: small  -- build and preserved side

On main, generateRuntimeFilters rejects every Node_SINGLE join. As a result, the query scans and probes all rows from big, even though the build side contains only three keys.

A one-CN prototype that generates a runtime filter only for right SINGLE reduced the work as follows:

Metric from EXPLAIN ANALYZE	Main-equivalent path (RF disabled)	Right-SINGLE RF prototype
big input blocks	123	1
big input rows	1,000,000	1
Join input rows	1,000,003	4
Scan read size	7.68 MiB	96.33 KiB
Warm-cache client latency	0.21 s	0.21 s

The warm-cache latency at this scale is dominated by fixed/client overhead, but the avoided scan, I/O, and hash-probe work is substantial and should matter for larger/wider tables, cold cache, and concurrent workloads.

Important multi-CN constraint

Simply allowing Node_SINGLE && IsRightJoin through the existing runtime-filter gate is not safe.

With the same one-million-row data set forced onto the multi-CN execution path:

Optimizer settings	Result
runtimeFilter=1,execType=3 (RF disabled)	Completed correctly in 0.23 s
execType=3 (naive right-SINGLE RF enabled)	Did not finish after 37.10 s; canceled manually with context canceled

A natural ten-million-row AP multi-CN plan with the naive RF also failed to finish after more than 80 seconds. Therefore, this issue must not be fixed by only relaxing the join-type check. The right-SINGLE runtime-filter message/scheduling lifecycle must work across CNs, or the eligible plan must be safely constrained to one CN.

Expected behavior / acceptance criteria

1. Eligible right SINGLE joins can filter a discardable table-scan probe using keys from the preserved build side.
2. Left SINGLE joins remain excluded because unmatched probe rows must be preserved.
3. Existing runtime-filter eligibility guards remain effective, including non-leading composite-primary-key checks and cardinality/selectivity limits.
4. Scalar-subquery semantics remain correct for matching keys, missing keys (NULL), and duplicate inner matches (cardinality violation).
5. Both one-CN and multi-CN executions finish without hangs and return identical results with the optimization enabled or disabled.
6. A multi-CN BVT/regression test covers the runtime-filter message lifecycle, not only the presence of Build/Probe specs in the logical plan.
7. EXPLAIN ANALYZE demonstrates reduced probe blocks/rows on a large-table/small-build case.

Additional information

The right-SINGLE output-cardinality calculation has a separate optimizer-phase concern: IsRightJoin is set before swapJoinChildren, while the physical child order changes later. A generic cardinality change based only on IsRightJoin can be correct after the swap but wrong during earlier join-ordering passes. That change should not be bundled into this performance fix without a phase-safe preserved-side model and a downstream-join plan regression test.

[XuPeng-SH]

Latest-main SQL-shape verification

I reproduced the issue directly with a clean image built from main commit 946427b877c1b12c497743eff264047e60a00be2. Both CNs in the two-CN make dev-up cluster were replaced with that image while keeping the same TN, LogService, and data. The test used:

• customer: 1,000,000 rows, primary key id
• product: 1,000,000 rows, primary key id
• delta / DML target: 3 rows with sparse lookup keys

No experimental runtime-filter code was present in the main image.

Confirmed affected SQL shapes

SQL shape	Plan on latest main	Actual large-table work from EXPLAIN ANALYZE
Scalar lookup in the SELECT list	1 RIGHT SINGLE	123 blocks / 1,000,000 rows
Scalar lookup used by a WHERE predicate	1 RIGHT SINGLE	123 blocks / 1,000,000 rows
Two scalar lookups in one SELECT	2 nested RIGHT SINGLE joins	246 blocks / 2,000,000 rows
Correlated scalar lookup in UPDATE ... SET	1 RIGHT SINGLE under Multi Update	123 blocks / 1,000,000 rows
Correlated scalar lookup in INSERT ... SELECT	1 RIGHT SINGLE under Lock and DEDUP	123 blocks / 1,000,000 rows
Correlated scalar lookup in a DELETE predicate	1 RIGHT SINGLE under Filter and Multi Update	123 blocks / 1,000,000 rows

Representative SELECT:

SELECT d.id,
       (SELECT c.val FROM customer c WHERE c.id = d.customer_id) AS v
FROM delta d
ORDER BY d.id;

Latest-main runtime evidence:

Join Type: RIGHT SINGLE
Join Cond: (c.id = d.customer_id)
Table Scan on customer:
  inputBlocks=123
  inputRows=1000000
  outputRows=1000000
  ReadSize=7.68 MiB
Join:
  inputRows=1000003
  outputRows=3

The multiple-lookup case independently scanned both million-row tables:

SELECT d.id,
       (SELECT c.val FROM customer c WHERE c.id = d.customer_id),
       (SELECT p.val FROM product p WHERE p.id = d.product_id)
FROM delta d;

customer scan: 123 blocks / 1,000,000 rows
product scan:  123 blocks / 1,000,000 rows
final output:  3 rows

DML correctness and scan amplification

The DML statements completed correctly:

UPDATE update_target t
SET v = (SELECT c.val FROM customer c WHERE c.id = t.customer_id)
WHERE t.id <= 3;

INSERT INTO insert_dest
SELECT d.id,
       (SELECT c.val FROM customer c WHERE c.id = d.customer_id)
FROM delta d;

DELETE FROM delete_target t
WHERE (SELECT c.status FROM customer c WHERE c.id = t.customer_id) = 1;

Verified results:

UPDATE: (1,10), (2,5000000), (3,10000000)
INSERT: (1,10), (2,5000000), (3,10000000)
DELETE: removed ids 1 and 3; id 2 remained

Each statement nevertheless fed 1,000,003 rows into the right-SINGLE join to produce only 3 rows. Thus this is a performance/resource-amplification issue, not a result-correctness failure.

One-CN prototype comparison

With the right-SINGLE runtime-filter prototype enabled on the same data and SQL shapes:

SELECT projection: customer 123 blocks / 1,000,000 rows -> 3 blocks / 3 rows
Two lookups:       each large table                    -> 3 blocks / 3 rows
UPDATE:            customer                            -> 3 blocks / 3 rows
INSERT SELECT:     customer                            -> 3 blocks / 3 rows
DELETE:            customer 123 blocks / 1,000,000 rows -> 1 block / 3 rows

All output/DML results remained identical in the one-CN tests. This confirms that the optimization opportunity is real across SELECT and DML shapes. It does not remove the multi-CN safety requirement documented in the issue: the naive prototype still stalls on the multi-CN execution path and is not mergeable as-is.

Negative controls

The following did not produce SINGLE and are outside this issue's specific scope:

• correlated MAX(...) scalar subquery: RIGHT join with aggregate
• correlated EXISTS: SEMI join

The test database was removed after verification, optimizer hints were restored, both CNs were switched back to the local development image, and all five cluster services are healthy.