opt: allow lookup joins to preserve index ordering with DESC columns#93673
opt: allow lookup joins to preserve index ordering with DESC columns#93673craig[bot] merged 1 commit intocockroachdb:masterfrom
Conversation
|
This change is |
msirek
left a comment
There was a problem hiding this comment.
Reviewed 7 of 7 files at r1, all commit messages.
Reviewable status:complete! 1 of 0 LGTMs obtained (waiting on @mgartner and @yuzefovich)
yuzefovich
left a comment
There was a problem hiding this comment.
Nice!
Let's also backport this to 22.2 but after having it bake on master for like a week.
Reviewed 7 of 7 files at r1, all commit messages.
Reviewable status:
pkg/sql/opt/ordering/lookup_join.go line 218 at r1 (raw file):
// input of the lookup join. Additionally, the 'x' column alone is not a key // for the 'xy' table, so each lookup may return multiple rows (which need // to be ordered among themselves). Since the postfix of the ordering that
nit: I think using "suffix" instead of "postfix" is more common.
This patch fixes an oversight of cockroachdb#84689 that prevented lookup joins from maintaining the index ordering for each lookup if the index ordering contained descending columns. The execution logic will respect descending index columns as-is, so only the optimizer code needed to be changed. This will allow plans with lookup joins to avoid sorts in more cases. Fixes cockroachdb#88319 Release note (performance improvement): The optimizer can now avoid planning a sort in more cases with joins that perform lookups into an index with one or more columns sorted in descending order. This can significantly decrease the number of rows that have to be scanned in order to satisfy a `LIMIT` clause.
148dbe5 to
5615482
Compare
DrewKimball
left a comment
There was a problem hiding this comment.
TFTRs!
Reviewable status:
pkg/sql/opt/ordering/lookup_join.go line 218 at r1 (raw file):
Previously, yuzefovich (Yahor Yuzefovich) wrote…
nit: I think using "suffix" instead of "postfix" is more common.
Done.
|
bors r+ |
|
Build succeeded: |
This patch fixes an oversight of #84689 that prevented lookup joins from maintaining the index ordering for each lookup if the index ordering contained descending columns. The execution logic will respect descending index columns as-is, so only the optimizer code needed to be changed. This will allow plans with lookup joins to avoid sorts in more cases.
Fixes #88319
Release note (performance improvement): The optimizer can now avoid planning a sort in more cases with joins that perform lookups into an index with one or more columns sorted in descending order. This can significantly decrease the number of rows that have to be scanned in order to satisfy a
LIMITclause.