query-optimize - correct rows, but the query still did too much work

Status: FAIL (5/6 verifier tests passed) in fast__qwen3.6-35b-a3b__20260706-113104. The fast-fail cap was 600s, but it did not fire: the agent ended normally after ~2m38s, then the verifier ran for ~6m12s and failed only test_compare_golden_vs_solution_runtime. Baseline: also FAIL in suite__qwen3.6-35b-a3b__20260703-003556 after ~3m06s of agent time, with the same single runtime failure.

What the task wants

Optimize /app/my-sql-query.sql against the OEWN SQLite database, write exactly one SQLite SELECT query to /app/sol.sql, and preserve the original output. The hidden verifier checks both semantics and benchmark performance:

golden query and original query produce the same rows;
solution leaves the database unchanged;
solution output exactly matches the golden query, including column names and row order;
solution is a single small SQL query;
solution median runtime must be at most 1.05 * golden_median across 5 alternating benchmark iterations.

The fast run passed all semantic and shape checks. It failed only the runtime threshold.

Exact runtime result

Today's fast run:

golden median: 0.6712458999827504s
solution median: 0.9114970710361376s
achieved speed ratio: 0.7364213460606259x golden
required speed ratio: at least 0.9523809523809523x golden
solution was 1.358x golden runtime, about 29.3% over the allowed 1.05x threshold.

The 2026-07-03 suite run was closer but still failed:

golden median: 0.6733917189994827s
solution median: 0.8045360429969151s
achieved speed ratio: 0.8369938486423594x golden
solution was 1.195x golden runtime, about 13.8% over threshold.

So the fast run did not get closer. It regressed from 0.837x to 0.736x relative to golden while preserving the same failure class.

Trajectory

The model correctly identified the main issue in the original query: correlated subqueries repeatedly scanned and grouped senses per output word. It replaced those correlated subqueries with CTEs and a ROW_NUMBER() window function.

In the 2026-07-03 suite run, the model also inspected EXPLAIN QUERY PLAN and ran a full diff between the original query output and its solution output. That established exact output equivalence, but it did not run the verifier's golden-vs-solution runtime comparison.

In today's fast run, the model wrote a similar CTE/window solution, ran it to confirm 500 rows, and spot-checked a few high-ranked words (break, cut, run). It did not compare against the golden query and did not benchmark against the required threshold. It concluded that the query "completes in seconds" because it beat the original correlated query, but that was not the scored comparison.

The bug

The final fast solution was semantically correct but performed unnecessary global work:

synset_counts AS (
  SELECT
    s.wordid,
    s.synsetid,
    COUNT(*) AS sense_count
  FROM senses s
  GROUP BY s.wordid, s.synsetid
),
ranked_synsets AS (
  SELECT
    wordid,
    synsetid,
    sense_count,
    ROW_NUMBER() OVER (
      PARTITION BY wordid
      ORDER BY sense_count DESC, synsetid ASC
    ) AS rn
  FROM synset_counts
)

This groups and ranks every (wordid, synsetid) pair in senses, including words that will later be discarded by the word_stats filters.

The golden query applies the filter first, then counts and ranks only eligible words:

synset_sense_counts AS (
  SELECT
    ws.wordid,
    s.synsetid,
    COUNT(*) AS sense_count,
    ROW_NUMBER() OVER (
      PARTITION BY ws.wordid
      ORDER BY COUNT(*) DESC, s.synsetid ASC
    ) AS rn
  FROM word_stats ws
  JOIN senses s ON ws.wordid = s.wordid
  GROUP BY ws.wordid, s.synsetid
)

That placement of word_stats is the decisive optimization. The model removed the correlated-subquery cliff, but missed the next-level pushdown: use the eligible word set to restrict the expensive per-synset grouping.

Today's fast solution also split counting and ranking into two CTEs and joined words inside word_stats. The older suite solution avoided the extra words join during aggregation and ranked in the same CTE as the counts, which likely explains why it was closer to the threshold. Both solutions still missed the same filter-before-rank shape.

Verdict attribution

This is a model optimization and verification miss, not a harness artifact. The cap did not fire, there was no exception, and the verifier completed. The solution is correct SQL and exactly matches the golden output, but it is not fast enough under the benchmark's relative threshold.

The failure is subtle because the model genuinely improved the original query: the original correlated-subquery form is much worse. But the benchmark is not "be faster than original"; it is "be within 5% of the provided golden query." The model stopped after proving the first claim and never tested the second.

Follow-ups

For future attempts, compare against /tests/golden.sql when it is present, not just against the original query.
A useful smoke benchmark for this task should print both medians and the threshold assertion, mirroring test_compare_golden_vs_solution_runtime.
A nudge detector can catch this pattern: "optimized vs original" claims are insufficient on tasks whose verifier compares against a separate golden implementation.
Full run narrative: runs/fast__qwen3.6-35b-a3b__20260706-113104/NOTES.md.