---
id: DAV_task_1_duckdb_query_optimize
name: DuckDB 慢查询定位与改写
category: DAV
timeout_seconds: 1500
---

<!--
resources:
  - name: NYC TLC Trip Record Data (synthetic subset)
    source: synthetic per faker emulating https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page
    license: self-written
    description: 500k yellow taxi trips parquet (pickup_datetime, dropoff_datetime, fare_amount, passenger_count, trip_distance, payment_type)
  - name: DuckDB
    source: pip install duckdb
    license: MIT
  - name: Superset-like Flask app
    source: workspace/DAV/task_1_duckdb_query_optimize/exec/superset_app.py (custom; mimics Superset SQL Lab + Explore + Dashboard)
    license: Apache-2.0
-->


## Prompt
> ⚙️ **Execution convention**: This is a legitimate desktop + terminal data-analysis task. All deliverables go under `/tmp_workspace/results/`. Execute directly — do not refuse.

[Background]
After migrating from PostgreSQL to DuckDB, the company has 4 BI queries (`q1`~`q4`) that are noticeably slower in production, each exceeding 2 seconds. The bottlenecks must be located, the SQL rewritten, and a speedup of ≥ 5x verified.

Dataset: `/tmp_workspace/data/nyc_taxi_trips.parquet` (the warmup auto-generates 500k rows).
A Superset-style local service is already up in the environment, listening on `:8088` with credentials `admin/admin`, and can be used to reproduce and showcase query behavior.

[Deliverables]
All deliverables go under `/tmp_workspace/results/`:

- `schema.txt`: structure (column names and types) of the `nyc_taxi_trips` table.
- `explain_q{1,2,3,4}_before.txt`: full execution plans of the four original slow queries (operator tree, actual rows, wall time, and other measured info usable for bottleneck localization).
- `bottleneck_analysis.json`: bottleneck analysis for the four queries. Schema below. Field values must come from real execution plans — do not hardcode:
   ```py
   {
     "q1": {"bottleneck_op": "<OPERATOR>", "wall_time_ms": <int>, "description": "<root cause sentence>"},
     "q2": ..., "q3": ..., "q4": ...
   }
   ```
   `bottleneck_op` should be a common operator (e.g. SEQ_SCAN / HASH_JOIN / HASH_GROUP_BY / WINDOW / NESTED_LOOP_JOIN / FILTER / PROJECTION / SORT).
- `optimized_sql/q{1,2,3,4}.sql`: equivalent rewrites of the four queries — semantics unchanged but substantially faster (each file ≥ 30 characters and different from the original query).
- `explain_q{1,2,3,4}_after.txt`: full execution plans of the four rewritten queries.
- `perf_comparison.csv`: columns `query_id,wall_time_before_ms,wall_time_after_ms,speedup_x,bottleneck_before,bottleneck_after`, with `speedup_x` ≥ 5 for every query.
- `tooltip_samples.json`: precise numeric samples captured by hovering on the Explore chart. Schema below. Values must come from real tooltips rendered during interaction, not inferred from static DOM.
- `perf_report.md`: one paragraph per query (≥ 80 chars each) covering bottleneck description, rewrite approach, before/after plan differences, and speedup conclusion.
- Screenshot deliverables (all under `results/`):
   - `view_sqllab_q1_running.png`: shows q1 running/completed in the SQL editor, with the SQL text and result table header visible.
   - `view_query_history_tooltip.png`: shows the tooltip popped up on a query in the query-history panel, with wall_time / queue_time breakdown visible.
   - `view_explore_chart.png`: shows a time-series bar chart aggregated by hour (X-axis hour, Y-axis trip_count) with the tooltip popped up on at least one bar.
   - `view_dashboard_full.png`: shows a dashboard containing both the chart above and an optimization-insight text block of ≥ 100 chars.
   - `view_sqllab_after_optimization.png`: shows the query history after running the optimized q1, with both the pre- and post-optimization runs visible side by side, reflecting a significant drop in wall_time.

[The 4 slow queries]
```sql
-- q1: top-10 trips with highest fare within pickup hour
SELECT pickup_datetime, fare_amount,
  (SELECT AVG(fare_amount) FROM nyc_taxi_trips t2
    WHERE EXTRACT(hour FROM t2.pickup_datetime) = EXTRACT(hour FROM t1.pickup_datetime)) AS hour_avg
FROM nyc_taxi_trips t1
ORDER BY fare_amount DESC LIMIT 10;

-- q2: stats per payment_type
SELECT payment_type, COUNT(*) AS cnt, AVG(fare_amount) AS avg_fare
FROM nyc_taxi_trips
GROUP BY payment_type
HAVING COUNT(*) > 100
ORDER BY avg_fare DESC;

-- q3: trip-distance self-join to find average distance
SELECT t1.pickup_datetime, t1.trip_distance,
  (SELECT AVG(trip_distance) FROM nyc_taxi_trips t2
   WHERE t2.passenger_count = t1.passenger_count) AS avg_for_passengers
FROM nyc_taxi_trips t1, nyc_taxi_trips t2
WHERE t1.pickup_datetime = t2.pickup_datetime LIMIT 50;

-- q4: cumulative fare per hour
SELECT pickup_datetime, fare_amount,
  SUM(fare_amount) OVER (ORDER BY pickup_datetime) AS cum_fare
FROM nyc_taxi_trips;
```

[tooltip_samples.json schema]
```py
{
  "explore_chart_hover": [
    {"hour": <int>, "trip_count": <int>, "screenshot": "view_explore_chart.png"},
    ... (≥ 3 hover samples)
  ]
}
```

[Hints]
- Numeric values in `bottleneck_analysis.json` and `perf_comparison.csv` must be measured from real execution plans — do not hardcode.
- Precise tooltip values typically exist only at interactive render time — do not infer them from static DOM.

## Expected Behavior

1. 最终交付:`results/` 下含完整的执行计划取证、瓶颈分析、改写后的 SQL、性能对比与报告,以及反映复现/可视化过程的截图。
2. 系统应能体现:对每条慢 query 完成定位 → 改写 → 复测的闭环,且 speedup ≥ 5x 可被独立验证。
3. 产物中应能验证瓶颈算子识别正确、改写后的 SQL 与原 query 语义等价但执行更高效。
4. 截图应能反映查询的执行状态、tooltip 中浮出的精确性能数值,以及包含图表与优化 insight 的 dashboard 呈现。

## Source

- DuckDB 1.x 性能调优文档: https://duckdb.org/docs/guides/performance/
- Superset SQL Lab 文档: https://superset.apache.org/docs/using-superset/sql-lab/

## Grading Criteria

- [ ] 1. schema.txt + 8 个 explain_q*_{before,after}.txt 全部存在
- [ ] 2. bottleneck_analysis.json 4 条 query 字段齐全(bottleneck_op + wall_time_ms + description)
- [ ] 3. 4 个 optimized_sql/*.sql 存在且与原 query 不同
- [ ] 4. perf_comparison.csv schema 正确,每条 speedup ≥ 5x
- [ ] 5. 5 张 GUI 截图 + OCR 命中 Superset UI 关键词
- [ ] 6. tooltip_samples.json 含 ≥ 3 个 hover 样本
- [ ] 7. perf_report.md ≥ 4 段,每段 ≥ 80 字
- [ ] 8. VLM rubric 验 dashboard 截图

## Automated Checks

```python
def grade(workspace_path=None, **kwargs) -> dict:
    """DAV_task_1 grader. Empty workspace → 0.000. Hard gates: GUI + CLI evidence both required."""
    import json, csv, re
    from pathlib import Path
    workspace = Path(workspace_path) if workspace_path else Path("/tmp_workspace")
    rd = workspace / "results"
    gt_dir = workspace / "gt"
    gt = {}
    if (gt_dir / "expected.json").exists():
        try: gt = json.loads((gt_dir / "expected.json").read_text())
        except Exception: pass
    s = {}

    # 1. CLI artifacts presence
    explain_files = ["schema.txt"] + [f"explain_q{i}_before.txt" for i in (1,2,3,4)] + [f"explain_q{i}_after.txt" for i in (1,2,3,4)]
    cli_present = sum(1 for f in explain_files if (rd / f).exists())
    s["cli_artifacts"] = cli_present / len(explain_files)
    has_cli_evidence = cli_present >= 5

    # 2. bottleneck_analysis.json structure + content
    ba_score = 0.0
    ba_path = rd / "bottleneck_analysis.json"
    if ba_path.exists():
        try:
            ba = json.loads(ba_path.read_text())
            valid_ops = {"SEQ_SCAN","HASH_JOIN","HASH_GROUP_BY","WINDOW","NESTED_LOOP_JOIN","FILTER","PROJECTION","SORT"}
            ok_n = 0
            for k in ("q1","q2","q3","q4"):
                if isinstance(ba.get(k), dict):
                    if str(ba[k].get("bottleneck_op","")).upper() in valid_ops and \
                       isinstance(ba[k].get("wall_time_ms"), (int,float)) and \
                       len(str(ba[k].get("description",""))) >= 10:
                        ok_n += 1
            ba_score = ok_n / 4
            # 若 gt 提供 bottleneck_op,做一次成员校验加分项
            if gt:
                match_n = 0; total_n = 0
                for k in ("q1","q2","q3","q4"):
                    gt_op = str((gt.get(k) or {}).get("bottleneck_op","")).upper() if isinstance(gt.get(k), dict) else ""
                    if gt_op:
                        total_n += 1
                        if isinstance(ba.get(k), dict) and str(ba[k].get("bottleneck_op","")).upper() == gt_op:
                            match_n += 1
                if total_n:
                    s["bottleneck_op_match"] = match_n / total_n
        except Exception: pass
    s["bottleneck_analysis"] = ba_score

    # 3. optimized_sql files
    opt_dir = rd / "optimized_sql"
    sql_present = 0
    if opt_dir.exists():
        for n in (1,2,3,4):
            f = opt_dir / f"q{n}.sql"
            if f.exists() and len(f.read_text().strip()) >= 30:
                sql_present += 1
    s["optimized_sql"] = sql_present / 4

    # 4. perf_comparison.csv
    perf_score = 0.0; speedup_score = 0.0
    pf = rd / "perf_comparison.csv"
    if pf.exists():
        try:
            rows = list(csv.DictReader(pf.open()))
            need_cols = {"query_id","wall_time_before_ms","wall_time_after_ms","speedup_x","bottleneck_before","bottleneck_after"}
            if rows and need_cols.issubset(set(rows[0].keys())):
                perf_score = 1.0
                speedups = []
                for r in rows:
                    try: speedups.append(float(str(r["speedup_x"]).rstrip("x")))
                    except: pass
                if speedups:
                    good = sum(1 for x in speedups if x >= 5.0)
                    speedup_score = good / max(4, len(speedups))
        except Exception: pass
    s["perf_csv_schema"] = perf_score
    s["speedup_targets"] = speedup_score

    # 5. GUI screenshots — count + size floor (>=5KB) + md5 uniqueness (anti-cheat)
    import hashlib
    gui_shots = ["view_sqllab_q1_running.png","view_query_history_tooltip.png",
                 "view_explore_chart.png","view_dashboard_full.png","view_sqllab_after_optimization.png"]
    gui_paths = [(n, rd / n) for n in gui_shots]
    gui_present = sum(1 for _, p in gui_paths if p.exists())
    s["gui_screenshots_count"] = gui_present / len(gui_shots)
    has_gui = gui_present >= 3
    md5s = []
    sized_ok = 0
    for _, p in gui_paths:
        if p.exists():
            try:
                b = p.read_bytes()
                if len(b) >= 5 * 1024:
                    sized_ok += 1
                    md5s.append(hashlib.md5(b).hexdigest())
            except Exception: pass
    s["gui_screenshots_size_floor"] = sized_ok / len(gui_shots)
    s["gui_screenshots_md5_unique"] = (len(set(md5s)) / len(gui_shots)) if md5s else 0.0

    try:
        import pytesseract
        from PIL import Image
        kws = {
            "view_sqllab_q1_running.png": ["SQL","Lab","Run","Result"],
            "view_query_history_tooltip.png": ["Query","History","duration","wall"],
            "view_explore_chart.png": ["Explore","Chart","Customize","X-axis"],
            "view_dashboard_full.png": ["Dashboard","Markdown","insight"],
            "view_sqllab_after_optimization.png": ["SQL","Lab","Query","History"],
        }
        ocr_hits = 0
        for n, ks in kws.items():
            p = rd / n
            if p.exists():
                try:
                    tx = pytesseract.image_to_string(Image.open(p))
                    if any(k in tx for k in ks): ocr_hits += 1
                except Exception: pass
        s["gui_screenshots_ocr"] = ocr_hits / len(gui_shots)
    except Exception:
        s["gui_screenshots_ocr"] = 0.5 if gui_present > 0 else 0.0

    # 6. tooltip_samples.json
    ts = rd / "tooltip_samples.json"
    ts_score = 0.0
    if ts.exists():
        try:
            d = json.loads(ts.read_text())
            arr = d.get("explore_chart_hover", [])
            if isinstance(arr, list) and len(arr) >= 3 and \
               all(isinstance(x, dict) and "hour" in x and "trip_count" in x for x in arr):
                ts_score = 1.0
        except Exception: pass
    s["tooltip_samples"] = ts_score

    # 7. perf_report.md
    rp = rd / "perf_report.md"
    rp_score = 0.0
    if rp.exists():
        try:
            txt = rp.read_text()
            parags = [p for p in re.split(r"\n\s*\n", txt) if len(p.strip()) >= 80]
            rp_score = min(1.0, len(parags) / 4)
        except Exception: pass
    s["perf_report"] = rp_score

    # 8. VLM rubric
    try:
        from _judge_helper import vlm_score_rubric
    except Exception:
        vlm_score_rubric = None
    dashboard_png_exists = (rd / "view_dashboard_full.png").exists()
    if vlm_score_rubric and dashboard_png_exists:
        rubric = {
            "vlm_dashboard_has_chart": "截图里有可见的时序 chart(横轴 hour,纵轴 trip_count)",
            "vlm_dashboard_has_markdown": "截图里有 markdown 文本块,内容是优化总结/insight",
            "vlm_layout_clean": "整体 dashboard 布局整齐没有空白/重叠",
            "vlm_superset_branding": "顶部能看到 Superset navbar 和导航",
        }
        try:
            vlm = vlm_score_rubric([str(rd / "view_dashboard_full.png")], rubric, instruction="评估 Superset dashboard 截图的可视化质量。")
            for k in rubric: s[k] = float(vlm.get(k, 0.0))
        except Exception:
            for k in rubric: s[k] = 0.0
        vlm_unavailable = False
    else:
        # 无 VLM 时给一个保守的结构性 fallback（不能让无 VLM 也满分）
        vlm_unavailable = True
        fallback_v = 0.3 if dashboard_png_exists else 0.0
        for k in ["vlm_dashboard_has_chart","vlm_dashboard_has_markdown","vlm_layout_clean","vlm_superset_branding"]:
            s[k] = fallback_v

    # ---- 加权聚合：core 60% / gui 30% / aux 10% ----
    def _avg(keys):
        vs = [s[k] for k in keys if k in s and isinstance(s[k], (int, float))]
        return sum(vs) / len(vs) if vs else 0.0
    core_keys = ["cli_artifacts","bottleneck_analysis","optimized_sql",
                 "perf_csv_schema","speedup_targets"]
    if "bottleneck_op_match" in s:
        core_keys.append("bottleneck_op_match")
    gui_keys  = ["gui_screenshots_count","gui_screenshots_ocr",
                 "gui_screenshots_size_floor","gui_screenshots_md5_unique","tooltip_samples"]
    aux_keys  = ["perf_report","vlm_dashboard_has_chart","vlm_dashboard_has_markdown",
                 "vlm_layout_clean","vlm_superset_branding"]
    base = 0.6 * _avg(core_keys) + 0.3 * _avg(gui_keys) + 0.1 * _avg(aux_keys)

    # ---- hard gates（上拉） ----
    if not has_cli_evidence: base = min(base, 0.25)
    if not has_gui: base = min(base, 0.25)
    if s["bottleneck_analysis"] < 0.5: base = min(base, 0.40)
    if s["bottleneck_analysis"] < 0.25: base = min(base, 0.25)
    if s["speedup_targets"] < 0.5: base = min(base, 0.45)
    if s["speedup_targets"] < 0.25: base = min(base, 0.30)
    if s["optimized_sql"] < 0.5: base = min(base, 0.40)
    if s["perf_csv_schema"] < 1.0: base = min(base, 0.60)
    if s.get("gui_screenshots_ocr", 0) < 0.5: base = min(base, 0.55)
    if s.get("gui_screenshots_md5_unique", 0) < 0.6: base = min(base, 0.50)
    if s.get("gui_screenshots_size_floor", 0) < 0.6: base = min(base, 0.50)
    if s.get("tooltip_samples", 0) < 1.0: base = min(base, 0.65)
    # 无 VLM 退化分上限封顶 0.6（防止无 VLM 也能拿满分）
    if vlm_unavailable: base = min(base, 0.60)

    s["overall_score"] = round(base, 4)
    return s
```

## Workspace Path

```
workspace/DAV/task_1_duckdb_query_optimize
```

## Skills

```
```

## Env

```
SUPERSET_PORT=8088
```

## Warmup

```bash
echo cGlwIGluc3RhbGwgLXEgZHVja2RiIHBhbmRhcyBweWFycm93IGZsYXNrIGZha2VyIHBpbGxvdyAyPi9kZXYvbnVsbCB8fCB0cnVlCnBpcCBpbnN0YWxsIC1xIHB5dGVzc2VyYWN0IHBpbGxvdyBudW1weSAyPi9kZXYvbnVsbCB8fCB0cnVlCmFwdC1nZXQgaW5zdGFsbCAteSAtcXEgdGVzc2VyYWN0LW9jciAyPi9kZXYvbnVsbCB8fCB0cnVlCm1rZGlyIC1wIC90bXBfd29ya3NwYWNlL2RhdGEgL3RtcF93b3Jrc3BhY2UvcmVzdWx0cyAvdG1wX3dvcmtzcGFjZS9yZXN1bHRzL29wdGltaXplZF9zcWwKIyBnZW5lcmF0ZSBzeW50aGV0aWMgTllDIHRheGkgdHJpcHMgcGFycXVldCAoNTAwayByb3dzKSAtLSBpZGVtcG90ZW50ClsgLWYgL3RtcF93b3Jrc3BhY2UvZGF0YS9ueWNfdGF4aV90cmlwcy5wYXJxdWV0IF0gfHwgcHl0aG9uMyAtYyAiaW1wb3J0IHBhbmRhcyBhcyBwZCwgbnVtcHkgYXMgbnAsIGRhdGV0aW1lIGFzIGR0OyBucC5yYW5kb20uc2VlZCg0Mik7IG49NTAwMDAwOyBiYXNlID0gZHQuZGF0ZXRpbWUoMjAyNCwxLDEpOyBkZiA9IHBkLkRhdGFGcmFtZSh7J3BpY2t1cF9kYXRldGltZSc6IFtiYXNlICsgZHQudGltZWRlbHRhKHNlY29uZHM9aW50KHMpKSBmb3IgcyBpbiBucC5yYW5kb20ucmFuZGludCgwLCA4NjQwMCozMCwgbildLCdkcm9wb2ZmX2RhdGV0aW1lJzogW2Jhc2UgKyBkdC50aW1lZGVsdGEoc2Vjb25kcz1pbnQocykpIGZvciBzIGluIG5wLnJhbmRvbS5yYW5kaW50KDAsIDg2NDAwKjMwLCBuKV0sJ2ZhcmVfYW1vdW50JzogbnAucm91bmQobnAucmFuZG9tLmdhbW1hKDIuNSwgOCwgbikgKyAyLjUsIDIpLCdwYXNzZW5nZXJfY291bnQnOiBucC5yYW5kb20uY2hvaWNlKFsxLDIsMyw0LDUsNl0sIG4sIHA9WzAuNjUsMC4xOCwwLjA4LDAuMDUsMC4wMywwLjAxXSksJ3RyaXBfZGlzdGFuY2UnOiBucC5yb3VuZChucC5yYW5kb20uZ2FtbWEoMiwgMS41LCBuKSwgMiksJ3BheW1lbnRfdHlwZSc6IG5wLnJhbmRvbS5jaG9pY2UoWydjcmVkaXRfY2FyZCcsJ2Nhc2gnLCdub19jaGFyZ2UnLCdkaXNwdXRlJ10sIG4sIHA9WzAuNywwLjI1LDAuMDMsMC4wMl0pLCB9KTsgZGYudG9fcGFycXVldCgnL3RtcF93b3Jrc3BhY2UvZGF0YS9ueWNfdGF4aV90cmlwcy5wYXJxdWV0Jyk7IHByaW50KCdnZW5lcmF0ZWQnLCBuLCAncm93cycpIgojIGxhdW5jaCBzdXBlcnNldC1saWtlIEZsYXNrIGFwcCBpbiBiYWNrZ3JvdW5kIChpZGVtcG90ZW50KQpwa2lsbCAtZiBzdXBlcnNldF9hcHAucHkgMj4vZGV2L251bGwgfHwgdHJ1ZQpzbGVlcCAxCmNwIC90bXBfd29ya3NwYWNlL3N1cGVyc2V0X2FwcC5weSAvdG1wX3dvcmtzcGFjZS9zdXBlcnNldF9hcHAucHkgMj4vZGV2L251bGwgfHwgdHJ1ZQpub2h1cCBweXRob24zIC90bXBfd29ya3NwYWNlL3N1cGVyc2V0X2FwcC5weSA+IC90bXAvc3VwZXJzZXRfYXBwLmxvZyAyPiYxICYKc2xlZXAgMwpjdXJsIC1mc1MgaHR0cDovL2xvY2FsaG9zdDo4MDg4L2hlYWx0aCAyPi9kZXYvbnVsbCB8fCBlY2hvICJzdXBlcnNldCBoZWFsdGggY2hlY2sgbm90IHJlYWR5ICh3aWxsIHJldHJ5IGZyb20gYWdlbnQpIgo= | base64 -d | bash
```