rowexec: investigate and improve lookup join performance

[asubiotto]

Performance improvements

• distsql: change join reader batch size to be specified in bytes #39471 Lookup joins currently use a constant lookup batch size to attempt to control result memory usage. With the addition of TargetBytes to BatchRequest, the batch size can be modified dynamically and specified as a number of bytes rather than rows.
• rowexec: speed up lookup joins when no ordering is required #48117 Lookup joins maintain ordering in cases where this is not necessary. We need to split out these cases. Cases which don't require ordering will be dramatically sped up with distsql: change join reader batch size to be specified in bytes #39471 while other cases need to be more careful of this.
• rowexec: improve disk spilling in the lookup joiner #48118 Lookup join disk spilling is inefficient.
• rowexec: improve LEFT SEMI/ANTI lookup join performance #49790 Left semi/anti joins are inefficient because they fetch results for all rows within a group. These can be made more efficient by only using one row per group in the lookup and changing ScanRequests to GetRequests or an even cheaper existence check.

General improvements

• sql: move indexJoiner into joinReader #40749 Merge the lookup join and index join operators.

Possibly out of scope but worth mentioning:

Explore better parallelization. A single lookup join is planned on the leaseholder for the bigger table. These rows might have matches on different nodes. Routing rows by expected lookup range location could allow us to parallelize lookups. This might not be something we do this release, but understanding the solution space will allow us to formulate specific work items. A less invasive change is to bucketing batches by expected lookup node #34997, which would allow us to reduce the total number of round trips.

[asubiotto]

Added #48117 and #48118 which were found while investigating #39471

[asubiotto]

Unchecked "better parallelization". It's always been out of scope and might be easier to do once #47473 is complete.

[asubiotto]

Also created a more specific issue to track join reader left semi/anti joins and linked that in the checkbox.

[nvanbenschoten]

It turns out that lookup join performance is extremely important for TPC-E. Many of its transactions require parallel chains of point lookups, which can be expressed as multi-way lookup joins. So far, "vectorizing" these point lookups into lookup joins has improved performance significantly, but I'm sure there's room for improvement.

All that goes to say – TPC-E would be another good testbed for you to take a look at once it's a little more stable and try out changes to while working on this issue.

[asubiotto]

Closing this issue as we improved/investigated the items in the list (refer to specific issues for more information). We didn't benchmark TPC-E but benchmarking/investigation would be interesting to do here.