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
[YSQL] Avoid unnecessary secondary index writes for UPDATE on table with check constraints #7937
Comments
Right now, we will only fix the case where secondary index UPDATE optimization is not being performed due to CHECK constraints on the table (a requirement for single row update optimization). [D11223] Untangling these two optimizations for all other cases - such as in presence of triggers, more than one input sources etc, requires more digging into and carful changes. D11192 was an attempt to do that, but is saved for future. |
…table with check constraints Summary: To avoid a secondary index update when the indexed/included columns aren't updated as part of an UPDATE statement, an optimization was made as part of commit cf94181. However, this optimization is intertwined with the code that validates requirements for single-row update optimizations. So, for example, one of the validations is that there should be no CHECK constraints on the table for the single-row optimization to be performed. So, the secondary index update optimization doesn't take affect if CHECK constraints are present (even though CHECK constraints don't matter for this optimization to be valid). This commit partly fixes this - presence of CHECK constraints won't block the optimization to avoid secondary index updates when possible. The correct fix is to untangle these two optimizations, so that they apply independently always. This is tracked by D11192 and requires more careful changes. Test Plan: ybd --java-test org.yb.pgsql.TestPgIndexSelectiveUpdate#testUpdateTableIndexWrites Reviewers: rskannan, tnayak, mihnea Reviewed By: mihnea Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D11223
…rites for UPDATE on table with check constraints Summary: To avoid a secondary index update when the indexed/included columns aren't updated as part of an UPDATE statement, an optimization was made as part of commit cf94181. However, this optimization is intertwined with the code that validates requirements for single-row update optimizations. So, for example, one of the validations is that there should be no CHECK constraints on the table for the single-row optimization to be performed. So, the secondary index update optimization doesn't take affect if CHECK constraints are present (even though CHECK constraints don't matter for this optimization to be valid). This commit partly fixes this - presence of CHECK constraints won't block the optimization to avoid secondary index updates when possible. The correct fix is to untangle these two optimizations, so that they apply independently always. This is tracked by D11192 and requires more careful changes. Original commit: https://phabricator.dev.yugabyte.com/D11223 / 79e0e12 Test Plan: ybd --java-test org.yb.pgsql.TestPgIndexSelectiveUpdate#testUpdateTableIndexWrites Jenkins: rebase: 2.5.3 Reviewers: rskannan, tnayak, mihnea Reviewed By: mihnea Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D11247
…DATE on table with check constraints Summary: To avoid a secondary index update when the indexed/included columns aren't updated as part of an UPDATE statement, an optimization was made as part of commit cf94181. However, this optimization is intertwined with the code that validates requirements for single-row update optimizations. So, for example, one of the validations is that there should be no CHECK constraints on the table for the single-row optimization to be performed. So, the secondary index update optimization doesn't take affect if CHECK constraints are present (even though CHECK constraints don't matter for this optimization to be valid). This commit partly fixes this - presence of CHECK constraints won't block the optimization to avoid secondary index updates when possible. The correct fix is to untangle these two optimizations, so that they apply independently always. This is tracked by D11192 and requires more careful changes. Test Plan: ybd --java-test org.yb.pgsql.TestPgIndexSelectiveUpdate#testUpdateTableIndexWrites Reviewers: rskannan, tnayak, mihnea Reviewed By: mihnea Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D11223
Jira Link: DB-1356
About single row update optimization - In case of an UPDATE that touches just a single row (and satisfies a few more requirements such as - no check constraints, no triggers, no secondary indexes etc), we have a bunch of optimizations in place (avoiding multi-row transactions, reducing the number of scans, etc).
Avoid Secondary indexes UPDATE optimization - As part of cf94181, updates to unrelated secondary indexes are avoided in UPDATE execution path - when none of the index/included columns of an index are updated by UPDATE statement.
The second optimization above is orthogonal to the idea of single-row optimization - but it is placed in the code path that checks all requirements for single-row optimization. So, there are bugs where the second optimization doesn't come into affect when some requirements of the single row optimization aren't met. Fix this.
The text was updated successfully, but these errors were encountered: