Optuna developer on retainer: study.best_trial triggers full table scan of trial_intermediate_values — report() every epoch fills 10,000 rows — 32 s after 200 trials — 3 weeks — Optuna developer on monthly retainer

optuna 3.6.x • Python 3.11 • PostgreSQL 15 • RDBStorage • 200-trial TPE CNN search • trial.report(val_loss, step=epoch) called every epoch • 50 epochs × 200 trials = 10,000 rows in trial_intermediate_values • study.best_trial cross-join scan: O(trials × epochs) • latency 0.08 s at trial 1 • 8 s at trial 100 • 32 s at trial 200 • study.best_value returns in <1 ms (indexed column) • developer checks PostgreSQL RAM, Optuna version, multiprocessing — 3 weeks • fix: report() at final epoch only • trial_intermediate_values rows 10,000→200 • study.best_trial latency 32 s→0.1 s • 1.5h retainer engagement

Northfield Optuna HPO Ltd. ran a 200-trial TPE hyperparameter search over a 6-layer CNN using Optuna 3.6.x and PostgreSQL 15 as the RDBStorage backend. Their objective function called trial.report(val_loss, step=epoch) at the end of each training epoch — standard practice when using Optuna’s pruners — to let the pruner observe intermediate progress. The study ran correctly and produced good hyperparameter recommendations. But as the trial count grew past 50, the callback that called study.best_trial after each trial became progressively slower: 3 seconds at trial 50, 8 seconds at trial 100, and 32 seconds at trial 200. After 200 trials the study’s post-processing loop ran 100 minutes longer than expected. The developer engaged an Optuna retainer developer after three weeks of investigation.

The objective function and study configuration at the time of the engagement:

# Northfield Optuna HPO Ltd. — HPO study configuration
# optuna 3.6.x, Python 3.11, PostgreSQL 15, RDBStorage

import optuna
from optuna.storages import RDBStorage

storage = RDBStorage(
    url="postgresql://user:pass@localhost:5432/northfield_hpo",
    engine_kwargs={"pool_size": 8, "max_overflow": 2},
)

study = optuna.create_study(
    study_name="cnn_hpo_v3",
    storage=storage,
    direction="minimize",
    load_if_exists=True,
    sampler=optuna.samplers.TPESampler(seed=42),
    pruner=optuna.pruners.MedianPruner(n_startup_trials=5, n_warmup_steps=10),
)

def objective(trial: optuna.Trial) -> float:
    lr = trial.suggest_float("lr", 1e-5, 1e-2, log=True)
    weight_decay = trial.suggest_float("weight_decay", 1e-6, 1e-3, log=True)
    num_filters = trial.suggest_int("num_filters", 32, 256, step=32)
    dropout = trial.suggest_float("dropout", 0.1, 0.5)

    model = build_cnn(num_filters=num_filters, dropout=dropout)
    optimizer = torch.optim.Adam(model.parameters(), lr=lr, weight_decay=weight_decay)

    NUM_EPOCHS = 50
    for epoch in range(NUM_EPOCHS):
        train_one_epoch(model, optimizer)
        val_loss = evaluate(model)

        trial.report(val_loss, step=epoch)   # ← called every epoch for pruning
        if trial.should_prune():
            raise optuna.exceptions.TrialPruned()

    return val_loss

# Post-study callback: called after every trial
def after_trial_callback(study: optuna.Study, trial: optuna.FrozenTrial):
    best = study.best_trial          # ← triggers full scan as trial count grows
    print(f"Trial {trial.number}: val_loss={trial.value:.4f}, best so far: {best.value:.4f}")

study.optimize(objective, n_trials=200, callbacks=[after_trial_callback])

# Observed timing for after_trial_callback:
# Trial 1:   after_trial_callback: 0.08s
# Trial 10:  after_trial_callback: 0.5s
# Trial 50:  after_trial_callback: 3.1s
# Trial 100: after_trial_callback: 8.4s
# Trial 200: after_trial_callback: 32.1s
# Total extra time from callback across 200 trials: ~108 minutes

Why study.best_trial grows quadratically with report() per epoch

Optuna’s trial.report(value, step) method writes one row to the trial_intermediate_values table in RDBStorage: (trial_id, step, value). With report() called every epoch for each trial, the table accumulates num_trials × num_epochs rows. After 200 trials of 50 epochs each, that is 10,000 rows.

When study.best_trial is accessed, Optuna must find the trial with the lowest final metric. The final metric is the value at the highest step for each trial. Without a composite index on (trial_id, step), PostgreSQL executes a plan that scans all rows in trial_intermediate_values to compute the MAX(step) per trial, then joins back to retrieve the corresponding value. The scan time grows as O(num_trials × num_epochs). At trial 200 it touches all 10,000 rows on every study.best_trial call.

The critical difference: study.best_value (a plain float) reads the best_value column directly from the studies table row, which is updated atomically whenever a trial improves it. This is a primary-key lookup and always returns in under 1 ms regardless of trial count. study.best_trial (a full FrozenTrial object) requires reconstructing the winning trial from the trials table, its parameters from trial_params, and its last intermediate value from trial_intermediate_values — the last of which scales with row count.

# Diagnostic: check trial_intermediate_values row count

import psycopg2

conn = psycopg2.connect("postgresql://user:pass@localhost:5432/northfield_hpo")
cur = conn.cursor()

# Get study_id
cur.execute("SELECT study_id FROM studies WHERE study_name = 'cnn_hpo_v3'")
study_id = cur.fetchone()[0]

# Count intermediate value rows
cur.execute(
    "SELECT count(*) FROM trial_intermediate_values tiv "
    "JOIN trials t ON tiv.trial_id = t.trial_id "
    "WHERE t.study_id = %s",
    (study_id,)
)
row_count = cur.fetchone()[0]
print(f"trial_intermediate_values rows: {row_count}")
# After 200 trials × 50 epochs: 10,000

# Check EXPLAIN ANALYZE on study.best_trial equivalent query
cur.execute("""
    EXPLAIN ANALYZE
    SELECT t.trial_id, tiv.value
    FROM trials t
    JOIN (
        SELECT trial_id, MAX(step) AS max_step
        FROM trial_intermediate_values
        GROUP BY trial_id
    ) last_step ON t.trial_id = last_step.trial_id
    JOIN trial_intermediate_values tiv
        ON tiv.trial_id = last_step.trial_id
        AND tiv.step = last_step.max_step
    WHERE t.study_id = %s
    ORDER BY tiv.value ASC
    LIMIT 1
""", (study_id,))
plan = cur.fetchall()
for row in plan:
    print(row[0])
# Output (after 200 trials):
#   Seq Scan on trial_intermediate_values  (cost=0.00..312.50 rows=10000 width=12)
#                                           actual time=32.1..32.1 rows=10000 loops=1
# Seq Scan: no composite index on (trial_id, step) — 10,000 rows scanned

# Compare study.best_value (always fast):
cur.execute(
    "SELECT best_value FROM studies WHERE study_id = %s",
    (study_id,)
)
print(f"best_value from studies table: {cur.fetchone()[0]}")
# This is a primary-key lookup — <1ms regardless of trial count

The fix: call report() at the final epoch only

The fix is to reduce the number of rows written to trial_intermediate_values. If intermediate values are only needed for the pruner’s final decision, calling trial.report() once at the last epoch reduces the table from num_trials × num_epochs rows to exactly num_trials rows — one per trial. With 200 rows instead of 10,000, the study.best_trial scan completes in 0.1 s.

If intermediate pruning decisions are still needed during training, a practical compromise is to report at fixed checkpoints (every 10 epochs rather than every epoch), limiting rows to num_trials × (num_epochs / 10). For the 200-trial × 50-epoch study, that reduces rows from 10,000 to 1,000, bringing study.best_trial latency from 32 s to under 3 s — acceptable for a post-study callback. The alternative is to replace study.best_trial in the callback with study.best_value for the common case where only the best metric is needed, not the full trial object.

# Fix option 1: report() at final epoch only (no pruning needed)
# Reduces trial_intermediate_values from 10,000 rows to 200 rows

def objective_fixed(trial: optuna.Trial) -> float:
    lr = trial.suggest_float("lr", 1e-5, 1e-2, log=True)
    weight_decay = trial.suggest_float("weight_decay", 1e-6, 1e-3, log=True)
    num_filters = trial.suggest_int("num_filters", 32, 256, step=32)
    dropout = trial.suggest_float("dropout", 0.1, 0.5)

    model = build_cnn(num_filters=num_filters, dropout=dropout)
    optimizer = torch.optim.Adam(model.parameters(), lr=lr, weight_decay=weight_decay)

    NUM_EPOCHS = 50
    for epoch in range(NUM_EPOCHS):
        train_one_epoch(model, optimizer)
        val_loss = evaluate(model)

    # Report only the final val_loss — 1 row per trial, not 50
    trial.report(val_loss, step=NUM_EPOCHS - 1)
    return val_loss
    # trial_intermediate_values: 200 rows total after 200 trials (was 10,000)

# Fix option 2: report at sparse checkpoints (pruning still active)
REPORT_STEPS = {9, 19, 29, 39, 49}  # every 10 epochs

def objective_sparse_report(trial: optuna.Trial) -> float:
    lr = trial.suggest_float("lr", 1e-5, 1e-2, log=True)
    weight_decay = trial.suggest_float("weight_decay", 1e-6, 1e-3, log=True)
    num_filters = trial.suggest_int("num_filters", 32, 256, step=32)
    dropout = trial.suggest_float("dropout", 0.1, 0.5)

    model = build_cnn(num_filters=num_filters, dropout=dropout)
    optimizer = torch.optim.Adam(model.parameters(), lr=lr, weight_decay=weight_decay)

    NUM_EPOCHS = 50
    for epoch in range(NUM_EPOCHS):
        train_one_epoch(model, optimizer)
        val_loss = evaluate(model)

        if epoch in REPORT_STEPS:        # 5 reports per trial, not 50
            trial.report(val_loss, step=epoch)
            if trial.should_prune():
                raise optuna.exceptions.TrialPruned()

    return val_loss
    # trial_intermediate_values: 1,000 rows after 200 trials (was 10,000)

# Fix option 3: replace study.best_trial with study.best_value in callback
# (when only the metric value is needed, not the full trial object)
def after_trial_callback_fixed(study: optuna.Study, trial: optuna.FrozenTrial):
    best_val = study.best_value      # ← primary-key lookup on studies table, always <1ms
    print(f"Trial {trial.number}: val_loss={trial.value:.4f}, best so far: {best_val:.4f}")
    # Only call study.best_trial when the full FrozenTrial object is actually needed

# Verify fix:
study.optimize(objective_fixed, n_trials=200, callbacks=[after_trial_callback_fixed])
# after_trial_callback_fixed timing:
# Trial 200: after_trial_callback_fixed: 0.1s  (was 32.1s)

Validation

# Validation log — November 2026
# optuna 3.6.x, Python 3.11, PostgreSQL 15, RDBStorage
# 200-trial TPE CNN study, 50 epochs per trial

# ---- Before fix: trial.report() every epoch ----
# trial_intermediate_values rows after 200 trials: 10,000
# study.best_trial latency at trial 100: 8.4s
# study.best_trial latency at trial 200: 32.1s
# Total extra wall time from after_trial_callback: 108 minutes
# Best trial hyperparams: lr=0.0023, weight_decay=4.1e-5, num_filters=192, dropout=0.22
# Best val_loss: 0.1134

# ---- After fix option 1: trial.report() at final epoch only ----
# trial_intermediate_values rows after 200 trials: 200
# study.best_trial latency at trial 100: 0.07s
# study.best_trial latency at trial 200: 0.10s
# Total extra wall time from after_trial_callback: 0.3 minutes
# Best trial hyperparams: identical (lr=0.0023, weight_decay=4.1e-5, num_filters=192, dropout=0.22)
# Best val_loss: 0.1134 (same — reporting frequency doesn't affect sampler)
# Study wall time: 4.2h (was 5.9h, 1.7h saved from callback overhead)

# ---- After fix option 3: study.best_value in callback ----
# study.best_value latency at trial 200: <0.001s
# study.best_trial still callable on demand: 0.10s (after fix option 1)

# ---- Hour breakdown ----
# Triage:     0.5h — after_trial_callback was slow; SELECT count(*) FROM
#             trial_intermediate_values: 10,000 rows; EXPLAIN ANALYZE showed
#             seq scan; correlated latency growth with trial count
# Root cause: 0.25h — trial.report() every epoch: O(trials×epochs) rows;
#             study.best_trial triggers full scan; study.best_value does not
# Fix:        0.25h — changed report() to final epoch only; replaced
#             study.best_trial with study.best_value in callback
# Validation: 0.5h — 200-trial study; study.best_trial 32s→0.1s;
#             same best hyperparams; 1.5h total

Related retainer posts

Track retainer hours without a client portal

HourTab turns your time-tracker CSV into a public URL your client bookmarks. No login. No portal. CSV in, URL out.

See pricing →