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: avoid planning uniqueness checks for upsert if new values are null #58300

Open
rytaft opened this issue Dec 28, 2020 · 1 comment
Open
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 T-sql-queries SQL Queries Team

Comments

@rytaft
Copy link
Collaborator

rytaft commented Dec 28, 2020

We currently plan some unnecessary uniqueness checks on UNIQUE WITHOUT INDEX columns when planning UPSERT queries. There are a couple of reasons for this:

  1. The input for an UPSERT may need to be wrapped in an EnsureUpsertDistinctOn and a LeftJoin. Since the function OutputColumnIsAlwaysNull doesn't recurse into JOINs or GROUP BYs, we may not be able to determine that a particular column is always NULL. We'll need to update this function to add support for JOINs and GROUP BYs. This will fix the case where the insert and update mapping for a column are the same (both NULL).
  2. For columns that have different insert and update mappings, we don't need to plan checks if (a) the insert mapping for at least one of the columns in the unique constraint is always NULL, and (b) none of the columns in the unique constraint are being updated (i.e., they don't have an update mapping).

Epic CRDB-13964

Jira issue: CRDB-3411

@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 Dec 28, 2020
@rytaft rytaft self-assigned this Dec 28, 2020
@rytaft rytaft added this to Triage in BACKLOG, NO NEW ISSUES: SQL Optimizer via automation Dec 28, 2020
@RaduBerinde RaduBerinde moved this from Triage to Planned for 21.1 in BACKLOG, NO NEW ISSUES: SQL Optimizer Jan 11, 2021
@RaduBerinde RaduBerinde moved this from Planned for 21.1 to Plan enhancements (higher priority) in BACKLOG, NO NEW ISSUES: SQL Optimizer Feb 23, 2021
@jlinder jlinder added the T-sql-queries SQL Queries Team label Jun 16, 2021
@rytaft rytaft added this to Triage in SQL Queries via automation Jul 7, 2022
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Jul 7, 2022
@rytaft rytaft removed their assignment Jul 7, 2022
@rytaft rytaft moved this from Triage to Backlog in SQL Queries Jul 7, 2022
@rytaft rytaft moved this from Backlog (DO NOT ADD NEW ISSUES) to New Backlog in SQL Queries Mar 30, 2023
Copy link

github-actions bot commented Jan 1, 2024

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!

@exalate-issue-sync exalate-issue-sync bot removed the T-sql-queries SQL Queries Team label Mar 15, 2024
@yuzefovich yuzefovich added the T-sql-queries SQL Queries Team label May 2, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
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 T-sql-queries SQL Queries Team
Projects
BACKLOG, NO NEW ISSUES: SQL Optimizer
Plan enhancements (higher priority)
Status: Backlog
SQL Queries
New Backlog
Development

No branches or pull requests

3 participants