Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
RaduBerinde opened this issue Mar 5, 2021 · 1 comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team

Comments

@RaduBerinde
Copy link
Member

RaduBerinde commented Mar 5, 2021

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

@RaduBerinde RaduBerinde added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Mar 5, 2021
@jlinder jlinder added the T-sql-queries SQL Queries Team label Jun 16, 2021
@github-actions
Copy link

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team
Projects
Status: Backlog
Development

No branches or pull requests

3 participants