opt: plan inner lookup joins on virtual column indexes in more cases#76078
Conversation
|
This change is |
RaduBerinde
left a comment
There was a problem hiding this comment.
Reviewable status:
complete! 1 of 0 LGTMs obtained (waiting on @mgartner and @msirek)
pkg/sql/opt/xform/testdata/rules/join, line 4144 at r1 (raw file):
# Covering case. Join on virtual column expression but do not produce it. # opt expect=GenerateLookupJoinsWithVirtualCols
[nit] is the variant with expect= not working? Same on more testcases below
msirek
left a comment
There was a problem hiding this comment.
I spent quite some time trying to understand the column id mapping code for virtual columns and related logic, to see if there could be potential problems. I couldn't find any issues.
Reviewable status:
56b5ce5 to
55ce1dc
Compare
mgartner
left a comment
There was a problem hiding this comment.
Reviewable status:
pkg/sql/opt/xform/testdata/rules/join, line 4144 at r1 (raw file):
Previously, RaduBerinde wrote…
[nit] is the variant with
expect=not working? Same on more testcases below
Nope, just something I forgot to clean up. Thanks for catching.
ExtractJoinEqualities now reuses computed columns instead of synthesizing new columns when it creates projections that exactly match a computed column expression of a base table. This allows GenerateLookupJoinsWithVirtualCols to generate lookup joins in more cases. This also paves the way for exploring anti- and semi-lookup joins on indexes with virtual columns and expression indexes. Fixes cockroachdb#75872 Release note (performance improvement): The optimizer now plans inner lookup joins using expression indexes in more cases, resulting in more efficient query plans.
55ce1dc to
82b6db4
Compare
|
bors r+ |
|
Build succeeded: |
ExtractJoinEqualities now reuses computed columns instead of
synthesizing new columns when it creates projections that exactly match
a computed column expression of a base table. This allows
GenerateLookupJoinsWithVirtualCols to generate lookup joins in more
cases. This also paves the way for exploring anti- and semi-lookup
joins on indexes with virtual columns and expression indexes.
Fixes #75872
Release note (performance improvement): The optimizer now plans inner
lookup joins using expression indexes in more cases, resulting in more
efficient query plans.