sql: create parallel lookup join hint to let people use the unsafe parallel lookup join when required

[jordanlewis]

19.2 closed a critical stability bug that permitted lookup joins to use unlimited memory in certain cases, causing OOMs. Because of #19721, the way we had to close the bug was by banning parallel lookup joins unless the optimizer could prove that the joined relation only had one row per lookup.

This has the potential to erode performance from 19.1 baselines in cases that didn't suffer from potential OOM conditions, which is problematic. We need to restore the performance for cases where it matters for customers.

We need a hint that always turns on the parallel behavior, even if the optimizer can't prove that the join columns are a key. I propose the syntax:

SELECT * FROM t <join type> PARALLEL_LOOKUP JOIN u...

This syntax is deliberately ugly. We shouldn't document it, because it's unsafe, but I also don't think we need to stick unsafe into the name necessarily. We should remove this syntax in 20.1 once #19721 is resolved and lookup join no longer has this issue.

Jira issue: CRDB-5332

[awoods187]

Can we follow the other hint syntax with something like INNER PARALLEL_LOOKUP JOIN? We have so far required hints to be inside the INNER ___ JOIN space. cc @RaduBerinde

[jordanlewis]

What you wrote is not different from what I wrote.

[jordanlewis]

I see, Andy's right - I forgot that you need to have the join type specified up front. I wasn't suggesting that this one be special - was just proposing the name of the thing that would replace lookup/hash/merge.

[RaduBerinde]

How about UNSAFE_PARALLEL_LOOKUP just to discourage usage?

[mgartner]

@yuzefovich does the work on the streamer make this suggestion obsolete?

[yuzefovich]

Yes, I think we can close this one.