optimizer not take into account expression index (even with index hinting)

[kocoten1992]

cockroachdb version 22.1

create table why_u_no_use_index (id uuid, long_text text);
insert into why_u_no_use_index (select gen_random_uuid(), random() * 100 from generate_series(1, 100000));
create index null_check on why_u_no_use_index((long_text is null));
explain analyze select * from why_u_no_use_index where long_text is null limit 100;

# postgres
postgres=# explain analyze select * from why_u_no_use_index where long_text is null limit 100;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..4.31 rows=1 width=32) (actual time=0.075..0.075 rows=0 loops=1)
   ->  Index Scan using null_check on why_u_no_use_index  (cost=0.29..4.31 rows=1 width=32) (actual time=0.074..0.074 rows=0 loops=1)
         Index Cond: ((long_text IS NULL) = true)
         Filter: (long_text IS NULL)
 Planning Time: 0.168 ms
 Execution Time: 0.093 ms
(6 rows)

# cockroachdb
root@:26257/defaultdb> explain analyze select * from why_u_no_use_index@null_check where long_text is null limit 100;
                             info
---------------------------------------------------------------
  planning time: 579µs
  execution time: 12.5s
  distribution: full
  vectorized: true
  rows read from KV: 200,000 (9.0 MiB)
  cumulative time spent in KV: 12.5s
  maximum memory usage: 10 MiB
  network usage: 0 B (0 messages)

  • limit
  │ nodes: n1
  │ actual row count: 0
  │ count: 100
  │
  └── • filter
      │ nodes: n1
      │ actual row count: 0
      │ filter: long_text IS NULL
      │
      └── • index join
          │ nodes: n1
          │ actual row count: 100,000
          │ KV time: 12.1s
          │ KV contention time: 0µs
          │ KV rows read: 100,000
          │ KV bytes read: 5.3 MiB
          │ estimated max memory allocated: 6.4 MiB
          │ estimated max sql temp disk usage: 0 B
          │ table: why_u_no_use_index@why_u_no_use_index_pkey
          │
          └── • scan
                nodes: n1
                actual row count: 100,000
                KV time: 477ms
                KV contention time: 0µs
                KV rows read: 100,000
                KV bytes read: 3.6 MiB
                estimated max memory allocated: 3.7 MiB
                missing stats
                table: why_u_no_use_index@null_check
                spans: FULL SCAN
(41 rows)


Time: 12.541s total (execution 12.540s / network 0.001s)

Jira issue: CRDB-17042

[blathers-crl]

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

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

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

• @cockroachdb/sql-queries (found keywords: vectorized,PLAN)
• @cockroachdb/kv (found keywords: KV)

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 otan.}

[mgartner]

Thank you for the report! We'll aim to address this soon.