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: generate inverted index scans for chained JSON fetch value operators with equality expression #55317

Closed
mgartner opened this issue Oct 8, 2020 · 1 comment · Fixed by #59494
Closed

opt: generate inverted index scans for chained JSON fetch value operators with equality expression #55317

mgartner opened this issue Oct 8, 2020 · 1 comment · Fixed by #59494
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
BACKLOG, NO NEW ISSUES: SQL Optimizer

Comments

@mgartner
Copy link
Collaborator

mgartner commented Oct 8, 2020

CRDB previously generated inverted index scans for queries with filters like j->'a'->'b' = '"c"'. In order to generate these scans, it relied on normalization rules that converted the filters into JSON containment expressions, with the @> operator.

These normalization rules had to be removed in #55316 because they were found to produce inequivalent expressions. While #55316 re-added some support for index acceleration of filters with the -> operator, it did not cover the case of chained -> operators.
@blathers-crl
Copy link

blathers-crl bot commented Oct 8, 2020

Hi @mgartner, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

While you're here, please consider adding an A- label to help keep our repository tidy.

🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.

@mgartner mgartner added this to Triage in BACKLOG, NO NEW ISSUES: SQL Optimizer via automation Oct 8, 2020
@blathers-crl blathers-crl bot added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Oct 8, 2020
@mgartner mgartner mentioned this issue Oct 8, 2020
Merged
@mgartner mgartner mentioned this issue Jan 23, 2021
Open
31 tasks
mgartner added a commit to mgartner/cockroach that referenced this issue Jan 27, 2021
@mgartner
opt: index accelerate chained fetch value operators
553dd54 
Prior to cockroachdb#55316, the optimizer generated inverted index scans on indexed
JSON columns when queries had filters with chained fetch value
operators, for example `j->'a'->'b' = '1'`. The logic that made this
possible was found to create query plans not equivalent to the query, so
it was removed. This commit restores the ability to index accelerate
chained -> operators.

Fixes cockroachdb#55317

Release note (performance improvement): A bug fix included in 20.2.1 for
for the JSON fetch value operator, `->`, resulted in chained `->`
operators in query filters not being index accelerated, e.g.,
`j->'a'->'b' = '1'`. Chained `->` are now index accelerated.
@mgartner mgartner mentioned this issue Jan 27, 2021
Merged
mgartner added a commit to mgartner/cockroach that referenced this issue Jan 28, 2021
@mgartner
opt: index accelerate chained fetch value operators
a8d7205 
Prior to cockroachdb#55316, the optimizer generated inverted index scans on indexed
JSON columns when queries had filters with chained fetch value
operators, for example `j->'a'->'b' = '1'`. The logic that made this
possible was found to create query plans not equivalent to the query, so
it was removed. This commit restores the ability to index accelerate
chained -> operators.

Fixes cockroachdb#55317

Release note (performance improvement): A bug fix included in 20.2.1 for
for the JSON fetch value operator, `->`, resulted in chained `->`
operators in query filters not being index accelerated, e.g.,
`j->'a'->'b' = '1'`. Chained `->` are now index accelerated.
mgartner added a commit to mgartner/cockroach that referenced this issue Jan 29, 2021
@mgartner
opt: index accelerate chained fetch value operators
b72da85 
Prior to cockroachdb#55316, the optimizer generated inverted index scans on indexed
JSON columns when queries had filters with chained fetch value
operators, for example `j->'a'->'b' = '1'`. The logic that made this
possible was found to create query plans not equivalent to the query, so
it was removed. This commit restores the ability to index accelerate
chained -> operators.

Fixes cockroachdb#55317

Release note (performance improvement): A bug fix included in 20.2.1 for
for the JSON fetch value operator, `->`, resulted in chained `->`
operators in query filters not being index accelerated, e.g.,
`j->'a'->'b' = '1'`. Chained `->` are now index accelerated.
mgartner added a commit to mgartner/cockroach that referenced this issue Jan 29, 2021
@mgartner
opt: index accelerate chained fetch value operators
5db07f3 
Prior to cockroachdb#55316, the optimizer generated inverted index scans on indexed
JSON columns when queries had filters with chained fetch value
operators, for example `j->'a'->'b' = '1'`. The logic that made this
possible was found to create query plans not equivalent to the query, so
it was removed. This commit restores the ability to index accelerate
chained -> operators.

Fixes cockroachdb#55317

Release note (performance improvement): A bug fix included in 20.2.1 for
for the JSON fetch value operator, `->`, resulted in chained `->`
operators in query filters not being index accelerated, e.g.,
`j->'a'->'b' = '1'`. Chained `->` are now index accelerated.
craig bot pushed a commit that referenced this issue Jan 29, 2021
@mgartner
Merge #59494
3f7283a 
59494: opt: index accelerate chained fetch value operators r=rytaft a=mgartner

#### opt: add test for JSON fetch val inverse

Release note: None

#### opt: index accelerate chained fetch value operators

Prior to #55316, the optimizer generated inverted index scans on indexed
JSON columns when queries had filters with chained fetch value
operators, for example `j->'a'->'b' = '1'`. The logic that made this
possible was found to create query plans not equivalent to the query, so
it was removed. This commit restores the ability to index accelerate
chained -> operators.

Fixes #55317

Release note (performance improvement): A bug fix included in 20.2.1 for
for the JSON fetch value operator, `->`, resulted in chained `->`
operators in query filters not being index accelerated, e.g.,
`j->'a'->'b' = '1'`. Chained `->` are now index accelerated.


Co-authored-by: Marcus Gartner <marcus@cockroachlabs.com>
@craig craig bot closed this as completed in 0bbcced Jan 29, 2021
BACKLOG, NO NEW ISSUES: SQL Optimizer automation moved this from Triage to Done Jan 29, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
BACKLOG, NO NEW ISSUES: SQL Optimizer
Done
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

opt: index accelerate chained fetch value operators mgartner/cockroach
1 participant
@mgartner