# v0.23.0 — TPC-H DVM Scaling Performance > **Full technical details:** [v0.23.0.md-full.md](v0.23.0.md-full.md) **Status: ✅ Released** | **Scope: Large** (~4–5 weeks) > Root-cause investigation and targeted fixes for three differential refresh > failure modes discovered at scale — bringing complex analytical queries from > thousands of times slower than full refresh down to the expected incremental > performance. --- ## What problem does this solve? Benchmarking the differential engine at a realistic data scale (TPC-H SF=1.0 — roughly 1 GB of data) revealed that 18 of 22 TPC-H queries had DIFFERENTIAL refresh *slower* than FULL recomputation — in the worst case, 2,246× slower. This is the opposite of the expected behaviour. The root causes were three distinct failure modes, each requiring a different fix. --- ## The Three Failure Modes ### 1. Threshold Collapse in Multi-Join Queries Queries joining four or more tables (TPC-H Q05, Q07, Q08, Q09) were producing intermediate query results with O(n) rows — proportional to the total table size — rather than O(Δ) rows proportional to the change batch. The delta SQL was not effectively restricting which rows from unchanged tables needed to be re-scanned. The fix restructures the aggregate UPDATE handling to split UPDATE rows into DELETE+INSERT pairs at the source, so the join delta never needs to re-scan unchanged base tables. Multi-join differential refresh is now O(Δ). *In plain terms:* changing 100 rows in a table with 1 million rows now refreshes by processing 100 rows, not 1 million. ### 2. Early Collapse in EXISTS Anti-Join Queries The EXISTS anti-join query (TPC-H Q04 — "orders with at least one late lineitems") was 140× slower at 10× the data size because the key filter used to restrict the "unchanged" portion of the right-side table was not applying correctly when additional predicates were present. The fix generates a key filter that restricts the right-side scan to only the keys that appear in the delta: `WHERE l_orderkey IN (SELECT o_orderkey FROM delta_orders)`. This turns an O(n) scan into O(Δ). ### 3. Structural Bug in Doubly-Nested EXISTS TPC-H Q20 (which uses a doubly-nested correlated EXISTS subquery) was re-materialising an expensive inner subquery for every row in the outer delta. The fix hoists the inner subquery to a named CTE that is computed once and shared, reducing Q20's refresh time from ~2 seconds to under 50 milliseconds. --- ## Debug and Tuning Tools New operational tools introduced alongside the performance fixes: - **`pgtrickle.log_delta_sql = on`** GUC — logs the generated delta SQL at DEBUG level, enabling `EXPLAIN ANALYZE` on the generated queries for diagnosis - **`pgtrickle.delta_work_mem`** GUC — sets a higher `work_mem` for delta SQL execution to avoid hash/sort spills in complex joins - **`pgtrickle.analyze_before_delta = on`** (default on) — runs `ANALYZE` on the change buffer tables before executing delta SQL, ensuring the query planner has accurate row count estimates --- ## DIFF Output Format Compatibility The UPDATE-split fix changes the format of differential output for aggregate stream tables: previously UPDATE events appeared as single rows, now they appear as DELETE+INSERT pairs. A **`pgtrickle.diff_output_format`** GUC (`split` or `merged`) allows users to opt into the new format on their own schedule — set `merged` first, migrate application code, then switch to `split`. --- ## Result: All 22 TPC-H Queries Under Target After the three fixes, all 22 TPC-H queries pass differential correctness validation at SF=1.0, and the previously-failing queries (Q04, Q05, Q07, Q08, Q09, Q20, Q22) are all within their performance targets. AUTO mode now correctly routes all 22 queries to DIFFERENTIAL rather than FULL at SF=1.0. --- ## Scope v0.23.0 is a targeted performance investigation and fix release. The three failure modes were addressed in strict priority order: confirm the hypothesis before coding, apply the minimal fix, validate with regression tests. The result is a differential engine that delivers its theoretical O(Δ) performance on the full TPC-H benchmark suite at production scale.