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.

Sign up for GitHub

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

Jump to bottom

opt: teach the optimizer that columns with a valid UNIQUE WITHOUT INDEX constraint form a key #58944

Closed
rytaft opened this issue Jan 13, 2021 · 0 comments · Fixed by #60591
Closed

opt: teach the optimizer that columns with a valid UNIQUE WITHOUT INDEX constraint form a key #58944

rytaft opened this issue Jan 13, 2021 · 0 comments · Fixed by #60591
Assignees
@rytaft
Labels
A-multiregion Related to multi-region A-sql-optimizer SQL logical planning and optimizations. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) C-performance Perf of queries or internals. Solution not expected to change functional behavior. T-multiregion

Comments

@rytaft
Copy link
Collaborator

rytaft commented Jan 13, 2021

The optimizer maintains functional dependencies (FDs) on all relational expressions during query optimization. These FDs can enable certain types of optimizations that wouldn't be possible otherwise. We should use the fact that columns are part of a valid UNIQUE WITHOUT INDEX constraint as an indication that we can add a key on those columns to the FDs.
@rytaft rytaft added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) C-performance Perf of queries or internals. Solution not expected to change functional behavior. A-sql-optimizer SQL logical planning and optimizations. A-multiregion Related to multi-region T-multiregion labels Jan 13, 2021
@rytaft rytaft self-assigned this Jan 13, 2021
rytaft added a commit to rytaft/cockroach that referenced this issue Feb 15, 2021
@rytaft
opt: create key for UNIQUE WITHOUT INDEX constraints
e3dd20a 
This commit teaches the optimizer that columns with a valid UNIQUE
WITHOUT INDEX constraint form a key, and the functional dependencies
should reflect that. This will be necessary to support locality
optimized search.

Fixes cockroachdb#58944
Informs cockroachdb#55185

Release note (performance improvement): The optimizer now knows that
the unique columns in an implicitly partitioned unique index form a
key. This can be used to enable certain optimizations and may result
in better plans.
@rytaft rytaft mentioned this issue Feb 15, 2021
Merged
rytaft added a commit to rytaft/cockroach that referenced this issue Feb 15, 2021
@rytaft
opt: create key for UNIQUE WITHOUT INDEX constraints
f5330e7 
This commit teaches the optimizer that columns with a valid UNIQUE
WITHOUT INDEX constraint form a key, and the functional dependencies
should reflect that. This will be necessary to support locality
optimized search.

Fixes cockroachdb#58944
Informs cockroachdb#55185

Release note (performance improvement): The optimizer now knows that
the unique columns in an implicitly partitioned unique index form a
key. This can be used to enable certain optimizations and may result
in better plans.
rytaft added a commit to rytaft/cockroach that referenced this issue Feb 16, 2021
@rytaft
opt: create key for UNIQUE WITHOUT INDEX constraints
5980e56 
This commit teaches the optimizer that columns with a valid UNIQUE
WITHOUT INDEX constraint form a key, and the functional dependencies
should reflect that. This will be necessary to support locality
optimized search.

Fixes cockroachdb#58944
Informs cockroachdb#55185

Release note (performance improvement): The optimizer now knows that
the unique columns in an implicitly partitioned unique index form a
key. This can be used to enable certain optimizations and may result
in better plans.
rytaft added a commit to rytaft/cockroach that referenced this issue Feb 17, 2021
@rytaft
opt: create key for UNIQUE WITHOUT INDEX constraints
6c14505 
This commit teaches the optimizer that columns with a valid UNIQUE
WITHOUT INDEX constraint form a key, and the functional dependencies
should reflect that. This will be necessary to support locality
optimized search.

Fixes cockroachdb#58944
Informs cockroachdb#55185

Release note (performance improvement): The optimizer now knows that
the unique columns in an implicitly partitioned unique index form a
key. This can be used to enable certain optimizations and may result
in better plans.
rytaft added a commit to rytaft/cockroach that referenced this issue Feb 17, 2021
@rytaft
opt: create key for UNIQUE WITHOUT INDEX constraints
dd91572 
This commit teaches the optimizer that columns with a valid UNIQUE
WITHOUT INDEX constraint form a key, and the functional dependencies
should reflect that. This will be necessary to support locality
optimized search.

Fixes cockroachdb#58944
Informs cockroachdb#55185

Release note (performance improvement): The optimizer now knows that
the unique columns in an implicitly partitioned unique index form a
key. This can be used to enable certain optimizations and may result
in better plans.
craig bot pushed a commit that referenced this issue Feb 17, 2021
@rytaft
Merge #60591
06486c9 
60591: opt: create key for UNIQUE WITHOUT INDEX constraints r=rytaft a=rytaft

**opt: don't add unique constraints with an index to test catalog**

This commit updates the test catalog so that it is consistent with the
real catalog and doesn't add unique constraints with an index to the
catalog table. This also prevents some tests from failing unnecessarily.

Release note: None

**opt: create key for UNIQUE WITHOUT INDEX constraints**

This commit teaches the optimizer that columns with a valid `UNIQUE WITHOUT INDEX`
constraint form a key, and the functional dependencies should reflect that. This will be
necessary to support locality optimized search.

Fixes #58944
Informs #55185

Release note (performance improvement): The optimizer now knows that
the unique columns in an implicitly partitioned unique index form a
key. This can be used to enable certain optimizations and may result
in better plans.

Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>
@craig craig bot closed this as completed in 99c3af7 Feb 17, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rytaft rytaft

Labels
A-multiregion Related to multi-region A-sql-optimizer SQL logical planning and optimizations. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) C-performance Perf of queries or internals. Solution not expected to change functional behavior. T-multiregion
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

opt: create key for UNIQUE WITHOUT INDEX constraints rytaft/cockroach
1 participant
@rytaft