sql: turning off DistSQL gives 7.5% qps boost on oltp_read_only (1.7% qps on oltp_read_write)

[tbg]

In an ad-hoc experiment, changing sql.defaults.distsql to off resulted in a 7.5% improvement in qps, from around 40k to around 43k.¹

DistSQL is likely used for the sysbench "range" queries, which scan 100 consecutive keys. It likely makes no sense to use DistSQL here, as the overhead does not get amortized. There are no unbounded scans here either.

Epic: CRDB-42584

Jira issue: CRDB-44780

Footnotes

1. https://cockroachlabs.slack.com/archives/C07P0CNLP51/p1732192681032459?thread_ts=1732092976.359539&cid=C07P0CNLP51 ↩

[michae2]

[sql queries triage] We think the aggregation forces us to choose a distributed plan. We could adjust physical planning heuristics, but this is tricky. We could introduce a row-count threshold to decide whether to distribute, along with a cluster setting.

We've also talked about moving the decision of whether to distribute into the optimizer (#47226), but that isn't a quick fix.

Putting into v25.1 to think about whether there is a quick fix.

[ajstorm]

I'm only seeing a 1.7% benefit in my oltp_read_write tests. Revising this estimate downward for now.