Inefficient query plan of a normal SELECT with merge join

[DerZc]

Describe the problem

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

Hi,

In the following test case, there are two equivalent queries, one is a normal SELECT, and the other is a prepared SELECT. However, I found that the normal SELECT generates a query plan that is less efficient than the one generated by the prepared SELECT. The difference is that the normal SELECT uses merge join, but the prepared SELECT uses lookup join, and the prepared SELECT is near 6 times faster than the normal SELECT. Given that prepared statements typically result in suboptimal query plans, the query plan generation process for normal SELECT statements may still have room for further optimization.

SET plan_cache_mode = force_generic_plan;
CREATE TABLE t1 (c0 SERIAL8 PRIMARY KEY);
INSERT INTO t1 (c0) SELECT (random() * 9223372036854775807)::INT8 FROM generate_series(1, 10000) ON CONFLICT DO NOTHING;
EXPLAIN ANALYZE SELECT DISTINCT SUM((-592761556)::INT8), XOR_AGG((19169261)::INT8), AVG((-1296868711)::INT8) FROM t1  FULL OUTER JOIN t1 AS t1_0 ON true  CROSS JOIN t1 AS t1_1  NATURAL JOIN t1 AS t1_2 WHERE NULLIF(false, ((t1_1.c0) IS NULL));
                                               info
---------------------------------------------------------------------------------------------------
  planning time: 3ms
  execution time: 73ms
  distribution: local
  plan type: custom
  rows decoded from KV: 40,000 (1.4 MiB, 4 gRPC calls)
  cumulative time spent due to contention: 24ms
  cumulative time spent waiting to acquire latches: 24ms

  • group (scalar)
  │ sql nodes: n1
  │ execution time: 25µs
  │ sql cpu time: 25µs
  │ actual row count: 1
  │
  └── • render
      │ execution time: 19µs
      │ sql cpu time: 19µs
      │ actual row count: 0
      │
      └── • merge join
          │ sql nodes: n1
          │ execution time: 15µs
          │ sql cpu time: 15µs
          │ actual row count: 0
          │ equality: (c0) = (c0)
          │ left cols are key
          │ right cols are key
          │
          ├── • merge join
          │   │ sql nodes: n1
          │   │ execution time: 15µs
          │   │ sql cpu time: 15µs
          │   │ actual row count: 0
          │   │ equality: (c0) = (c0)
          │   │ left cols are key
          │   │ right cols are key
          │   │
          │   ├── • merge join
          │   │   │ sql nodes: n1
          │   │   │ execution time: 28µs
          │   │   │ sql cpu time: 28µs
          │   │   │ actual row count: 0
          │   │   │ equality: (c0) = (c0)
          │   │   │ left cols are key
          │   │   │ right cols are key
          │   │   │
          │   │   ├── • filter
          │   │   │   │ sql nodes: n1
          │   │   │   │ execution time: 4ms
          │   │   │   │ sql cpu time: 4ms
          │   │   │   │ actual row count: 0
          │   │   │   │ filter: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
          │   │   │   │
          │   │   │   └── • scan
          │   │   │         sql nodes: n1
          │   │   │         kv nodes: n1
          │   │   │         KV time: 36ms
          │   │   │         KV contention time: 24ms
          │   │   │         KV latch wait time: 24ms
          │   │   │         KV rows decoded: 10,000
          │   │   │         sql cpu time: 2ms
          │   │   │         actual row count: 10,000
          │   │   │         missing stats
          │   │   │         table: t1@t1_pkey
          │   │   │         spans: FULL SCAN
          │   │   │
          │   │   └── • filter
          │   │       │ sql nodes: n1
          │   │       │ execution time: 3ms
          │   │       │ sql cpu time: 3ms
          │   │       │ actual row count: 0
          │   │       │ filter: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
          │   │       │
          │   │       └── • scan
          │   │             sql nodes: n1
          │   │             kv nodes: n1
          │   │             KV time: 9ms
          │   │             KV rows decoded: 10,000
          │   │             sql cpu time: 2ms
          │   │             actual row count: 10,000
          │   │             missing stats
          │   │             table: t1@t1_pkey
          │   │             spans: FULL SCAN
          │   │
          │   └── • filter
          │       │ sql nodes: n1
          │       │ execution time: 3ms
          │       │ sql cpu time: 3ms
          │       │ actual row count: 0
          │       │ filter: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
          │       │
          │       └── • scan
          │             sql nodes: n1
          │             kv nodes: n1
          │             KV time: 8ms
          │             KV rows decoded: 10,000
          │             sql cpu time: 2ms
          │             actual row count: 10,000
          │             missing stats
          │             table: t1@t1_pkey
          │             spans: FULL SCAN
          │
          └── • filter
              │ sql nodes: n1
              │ execution time: 3ms
              │ sql cpu time: 3ms
              │ actual row count: 0
              │ filter: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
              │
              └── • scan
                    sql nodes: n1
                    kv nodes: n1
                    KV time: 7ms
                    KV rows decoded: 10,000
                    sql cpu time: 2ms
                    actual row count: 10,000
                    missing stats
                    table: t1@t1_pkey
                    spans: FULL SCAN


PREPARE prepare_query (int8, int8, int8, bool, bool) AS SELECT DISTINCT SUM($1), XOR_AGG($2), AVG($3) FROM t1  FULL OUTER JOIN t1 AS t1_0 ON $4  CROSS JOIN t1 AS t1_1  NATURAL JOIN t1 AS t1_2 WHERE NULLIF($5, ((t1_1.c0) IS NULL));
EXPLAIN ANALYZE EXECUTE prepare_query((-592761556)::INT8, (19169261)::INT8, (-1296868711)::INT8, true, false);
                                                    info
------------------------------------------------------------------------------------------------------------
  planning time: 2ms
  execution time: 13ms
  distribution: local
  plan type: generic, re-optimized
  rows decoded from KV: 10,000 (361 KiB, 1 gRPC calls)

  • group (scalar)
  │ sql nodes: n1
  │ execution time: 20µs
  │ sql cpu time: 20µs
  │ actual row count: 1
  │
  └── • render
      │ execution time: 8µs
      │ sql cpu time: 8µs
      │ actual row count: 0
      │
      └── • lookup join (streamer)
          │ sql nodes: n1
          │ KV time: 0µs
          │ KV rows decoded: 0
          │ execution time: 49µs
          │ sql cpu time: 49µs
          │ actual row count: 0
          │ table: t1@t1_pkey
          │ equality: (c0) = (c0)
          │ equality cols are key
          │ pred: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
          │
          └── • lookup join (streamer)
              │ sql nodes: n1
              │ KV time: 0µs
              │ KV rows decoded: 0
              │ execution time: 34µs
              │ sql cpu time: 34µs
              │ actual row count: 0
              │ table: t1@t1_pkey
              │ equality: (c0) = (c0)
              │ equality cols are key
              │ pred: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
              │
              └── • lookup join (streamer)
                  │ sql nodes: n1
                  │ KV time: 0µs
                  │ KV rows decoded: 0
                  │ execution time: 44µs
                  │ sql cpu time: 44µs
                  │ actual row count: 0
                  │ table: t1@t1_pkey
                  │ equality: (c0) = (c0)
                  │ equality cols are key
                  │ pred: CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
                  │
                  └── • filter
                      │ sql nodes: n1
                      │ execution time: 3ms
                      │ sql cpu time: 3ms
                      │ actual row count: 0
                      │ filter: true AND CASE false WHEN c0 IS NULL THEN CAST(NULL AS BOOL) ELSE false END
                      │
                      └── • scan
                            sql nodes: n1
                            kv nodes: n1
                            KV time: 9ms
                            KV rows decoded: 10,000
                            sql cpu time: 2ms
                            actual row count: 10,000
                            missing stats
                            table: t1@t1_pkey
                            spans: FULL SCAN

Expected behavior
The normal SELECT should be faster or the same as the prepared SELECT.

Additional data / screenshots
If the problem is SQL-related, include a copy of the SQL query and the schema
of the supporting tables.

If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).

Note that log files can contain confidential information. Please continue
creating this issue, but contact support@cockroachlabs.com to submit the log
files in private.

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version [CockroachDB CCL v26.2.0-alpha.00000000-dev-1ad60fb00289249b886bf98b08c8066e9475624e-dirty (x86_64-pc-linux-gnu, built 2026/02/03 03:00:41, go1.25.5)]
• Server OS: [Ubuntu 24.04]
• Client app [cockroach sql, JDBC]

Additional context
What was the impact?

Add any other context about the problem here.

Jira issue: CRDB-59508

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/kv (found keywords: KV)
• @cockroachdb/sql-queries (found keywords: plan)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}