cockroachdb: consent query slow

[lopezator]

Describe the bug

Related issues:

#1755
cockroachdb/cockroach#47179

Apparently there is an intermittent (sometimes not reproducible) issue when zigzag join queries hit multiple regions.

More info about zigzag joins here:

cockroachdb/cockroach#23520 (comment)

As CockroachDB moves the leaseholder location and learns from usage, this is not reproducible always, but can be performing slow for a while, sometimes days.

Additional context

Underperformant query (sometimes we saw times as high as 50s):

SELECT h.* 
FROM hydra_oauth2_consent_request_handled as h 
JOIN hydra_oauth2_consent_request as r ON (h.challenge = r.challenge) 
WHERE r.subject='my@subject.com' AND r.client_id='my-client' AND r.skip=FALSE 
AND (h.error='{}' AND h.remember=TRUE) 
ORDER BY h.requested_at DESC;

Its query plan (EXPLAIN ):

	distributed	true
	vectorized	false
sort		
 │	order	-requested_at
 └── render		
      └── lookup-join		
           │	table	hydra_oauth2_consent_request_handled@primary
           │	type	inner
           │	equality	(challenge) = (challenge)
           │	equality cols are key	
           │	parallel	
           │	pred	"(@9 = '{}') AND (@7 = true)"
           └── lookup-join		
                │	table	hydra_oauth2_consent_request@primary
                │	type	inner
                │	equality	(challenge) = (challenge)
                │	equality cols are key	
                │	parallel	
                │	pred	@4 = false
                └── zigzag-join		
                     │	type	inner
                     │	pred	"(@3 = 'my@subject.com') AND (@2 = 'my-client')"
                     ├── scan		
                     │	table	hydra_oauth2_consent_request@hydra_oauth2_consent_request_client_id_idx
                     │	fixedvals	1 column
                     └── scan		
	table	hydra_oauth2_consent_request@hydra_oauth2_consent_request_subject_idx
	fixedvals	1 column

You can see the culprit zigzag join here.