Skip to content

opt: improve filter simplification with partial index involving virtual columns #61566

@RaduBerinde

Description

@RaduBerinde

Teach indexConstraintCtx.simplifyFilter to remove the redundant filters like the b:2 = 3 below:

exec-ddl
CREATE TABLE tmp (
  a INT,
  b INT,
  v INT AS (b + 10) VIRTUAL,
  INDEX idx (v)
)
----

opt
SELECT * FROM tmp WHERE v = 13
----
  project
   ├── columns: a:1 b:2!null v:3!null
   ├── immutable
   ├── fd: ()-->(2,3)
   ├── select
   │    ├── columns: a:1 b:2!null
   │    ├── fd: ()-->(2)
   │    ├── index-join tmp
   │    │    ├── columns: a:1 b:2
   │    │    └── scan tmp@idx
   │    │         ├── columns: rowid:4!null
   │    │         ├── constraint: /3/4: [/13 - /13]
   │    │         └── key: (4)
   │    └── filters
   │         └── b:2 = 3 [outer=(2), constraints=(/2: [/3 - /3]; tight), fd=()-->(2)]
   └── projections
        └── b:2 + 10 [as=v:3, outer=(2), immutable]

Jira issue: CRDB-2992

Metadata

Metadata

Assignees

No one assigned

    Labels

    C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-sql-queriesSQL Queries Team

    Type

    No type

    Projects

    Status

    Backlog

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions