[YSQL] Disable auto analyze once for regress tests instead of per test

Jira Link: DB-23635

Description

Not a settled direction. Filing this to record the problem and the evidence in one place. The team has not agreed on whether to do this, so treat the proposal below as a starting point for discussion.

Auto analyze is on by default whenever the cost based optimizer is on, and regress tests keep having to switch it back off one test at a time. The proposal is to disable it once for regress tests, for example in BasePgRegressTest, and let the few tests that actually exercise auto analyze opt back in.

Why a regress test and auto analyze are a bad fit

A regress test asserts exact output. The auto analyze service runs ANALYZE at moments of its own choosing, so a test that pins a query plan, a metric, or a row estimate is asserting against statistics that may or may not have been refreshed by the time the query runs. The failure is a race, so it shows up as a low rate flake on CI and usually not at all locally.

Evidence that this keeps recurring

The commit that turned the feature on, 6760f0c52bf for #27462, already had to disable it across many tests, and says so directly: auto analyze "introduc[es] noise for tests (e.g. tests checking for metrics, tests checking query plans)", with "a major[ity] of tests ... disabled based on multiple jenkins runs' results" and "a few more query plan related tests ... disabled proactively". That same commit spun off four separate investigations, of which several are still open:

• #28393 java regression error position output, OPEN
• #28391 Restarting a DDL transaction not supported and ERROR: Restart read required, OPEN
• #28693 TestPgTransactions, OPEN
• #28389 PgPartman, closed

Since then:

• #28732 "Disable auto analyze on more flaky tests", OPEN. Making auto analyze more aggressive broke more tests, and b4b77ab1e24 disabled it on the ones that failed.
• #28731 investigation of TestPgRegressInsertOnConflictBatch with auto analyze, closed.
• #32881 flaky TestPgRegressInsertOnConflictBatch9, closed by d911a805dad, which disabled auto analyze for that test.
• #34021 flaky TestPgRegressMergeScan, where a background ANALYZE landing inside the lifetime of an expression index flips the plan.

Current cost of the per test approach

• 40 java test files pin ysql_enable_auto_analyze to false, 42 mention it at all.
• 21 files under src/yb disable it for C++ tests.
• Neither BasePgRegressTest nor BasePgSQLTest disables it centrally, so every new test that pins a plan or a metric inherits the hazard and only finds out through a CI flake.

The pattern in every case above is the same: a test is written, it passes locally, it flakes on CI at a low rate, someone spends time bisecting it to auto analyze, and the fix is one more line disabling the flag. Disabling it once would turn that recurring cost into a single decision.

Counter-argument to weigh

Auto analyze on is what a customer cluster runs when the cost based optimizer is on, so a test with it disabled is not testing the customer's steady state. The counter to that counter is that a test should reach a realistic state deterministically, by analyzing explicitly, rather than by racing a background service. #34021 takes exactly that approach: disable the service and add explicit ANALYZE statements so the suite still covers analyzed statistics. Whether that generalizes to every suite is part of what needs agreement.

See also #34028, which asks for ANALYZE itself to be deterministic. That matters for tables too large to be sampled in full, where even an explicit ANALYZE does not give a stable baseline.

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.