sql: investigate using apply joins to execute subqueries

Today, uncorrelated subqueries are not optimized the same way as correlated subqueries, which are hoisted into apply joins. As a result, the final optimizer plan typically includes subqueries as separate plans that must be executed and buffered to be used by the main query plan. A problem with this approach is that the main query is never re-optimized with the output of the subquery, and therefore the plan may be suboptimal. Apply joins, however, do cause the right side of the join to be re-optimized before it is executed. If we treat a subquery as the left side of an apply join, this will allow us to fully optimize the main query before execution.

A potential problem with this approach is that today apply joins are quite inefficient. Additionally, today plans containing apply joins cannot be distributed. We will need to allow the plan to be distributed in order to avoid regressing performance of queries with non-correlated subqueries.

Jira issue: CRDB-22543

The issue names no files, tests, or entry points. Start by tracing how correlated and uncorrelated subqueries are planned with apply joins, then assess the efficiency and distribution constraints; done means non-correlated subqueries are fully optimized without regressing query performance.

Written by the indexing model from the issue text.