sql: generic query plans cannot use indexes for IN sets or disjunction

[michae2]

For generic query plans we usually replace scans with lookup joins to handle placeholders. But this technique doesn't seem to be working when the scan would come from an IN set or a disjunction over equality.

Here's a demonstration using cockroach demo movr on v24.2.0-rc.1:

SET plan_cache_mode = force_generic_plan;
PREPARE p AS SELECT * FROM users WHERE city = $1;
-- this can use a scan over the primary index thanks to the placeholder fast path
EXPLAIN ANALYZE EXECUTE p ('seattle');

PREPARE p2 AS SELECT * FROM users WHERE city = $1 OR city = $2;
-- this should become a lookup join into the primary index, but is instead a full table scan and filter
EXPLAIN ANALYZE EXECUTE p2 ('seattle', 'los angeles');

PREPARE p3 AS SELECT * FROM users WHERE city IN ($1, $2, $3, $4);
-- again, this should become a lookup join into the primary index, but is instead a full table scan and filter
EXPLAIN ANALYZE EXECUTE p3 ('seattle', 'portland', 'vancouver', 'los angeles');

Jira issue: CRDB-41279

[michae2]

Even when forcing the index, we get a full scan of the index and a filter:

demo@127.0.0.1:26257/movr> PREPARE p4 AS SELECT rider_id FROM rides@rides_auto_index_fk_city_ref_users WHERE city IN ($1, $2);
PREPARE

Time: 1ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/movr> EXPLAIN ANALYZE EXECUTE p4 ('boston', 'toronto');
                                         info
--------------------------------------------------------------------------------------
  planning time: 1ms
  execution time: 2ms
  distribution: full
  vectorized: true
  plan type: generic, re-optimized
  rows decoded from KV: 500 (44 KiB, 1 gRPC calls)
  cumulative time spent in KV: 2ms
  maximum memory usage: 180 KiB
  network usage: 0 B (0 messages)
  regions: us-east1
  sql cpu time: 502µs
  isolation level: serializable
  priority: normal
  quality of service: regular

  • filter
  │ sql nodes: n1
  │ regions: us-east1
  │ actual row count: 56
  │ sql cpu time: 133µs
  │ estimated row count: 167
  │ filter: city IN ('boston', 'toronto')
  │
  └── • scan
        sql nodes: n1
        kv nodes: n1
        regions: us-east1
        actual row count: 500
        KV time: 2ms
        KV contention time: 0µs
        KV rows decoded: 500
        KV bytes read: 44 KiB
        KV gRPC calls: 1
        estimated max memory allocated: 90 KiB
        sql cpu time: 369µs
        estimated row count: 500 (100% of the table; stats collected 11 minutes ago)
        table: rides@rides_auto_index_fk_city_ref_users
        spans: FULL SCAN
(38 rows)

Time: 5ms total (execution 4ms / network 1ms)

[michae2]

Regarding the docs-known-limitation tag, there are several known performance limitations when using generic query plans in v24.2:

• 128702

• 128703

• 128704

• 128908

• 128916 (this issue)

These could probably all be documented together as one more general known limitation about generic query plans performing worse than custom plans in some cases.

[mgartner]

I think #131383 could help fix this, but I'm not sure. I'll look into it.