sql/opt: index recommendations can significantly increase planning time

[rytaft]

For certain types of complex queries, finding index recommendations can take significantly longer than the time to optimize or even execute the query. This is a problem because when the cluster setting sql.metrics.statement_details.index_recommendation_collection.enabled is true (which it is by default), we periodically collect index recommendations as part of execution to populate the DB Console SQL Insights. As a result, every few executions of a query could have high latency due to the time needed to collect index recommendations. This seems to have gotten worse in 23.2+ with #103782, which sometimes increases the number of index candidates considered.

We should prevent index recommendations from significantly increasing the latency of production queries. Some ideas:

• Limit the time for index recommendations to some configurable value (maybe 1s by default). Don't collect recommendations if it takes longer than this (make the lack of recommendations due to a timeout visible in the SQL Insights page as well).
• Provide a separate session setting to set the timeout for index recommendations on EXPLAIN.
• Collect index recommendations in a separate goroutine to avoid increasing the latency of production queries.

Jira issue: CRDB-42591

[blathers-crl]

Hi @rytaft, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mgartner]

Some notes:

• This got worse in indexrec: remove the overlap between equalCandidates, rangeCandidates and joinCandidates #103782, which adds a few more index candidates that are better than candidates that were previously generated.
• The additional candidates cause exploration to take much longer. Maybe there is some pathological behavior in the optimizer with these new candidates?