-
Notifications
You must be signed in to change notification settings - Fork 4.1k
opt: improve filter simplification with partial index involving virtual columns #61566
Copy link
Copy link
Open
Labels
C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-sql-queriesSQL Queries TeamSQL Queries Team
Description
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
Reactions are currently unavailable
Metadata
Metadata
Assignees
Labels
C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-sql-queriesSQL Queries TeamSQL Queries Team
Type
Projects
Status
Backlog