Media Dashboard Scans 6 PB Despite a Date Filter

The filter is on event_ts, but the table is partitioned by event_day. Presto cannot prune partitions unless the predicate is on the partition column in a form it can evaluate. The dashboard's timestamp predicate therefore leaves every partition eligible. The analyst pattern substr(event_day,1,6) = '202405' is also non-sargable: wrapping the partition column in a function prevents pruning. Why more workers failed: adding workers increases parallel scan capacity, but the query is already I/O- and metadata-bound. It reads far more bytes than needed and pays planning overhead for 12 million small files. More workers cannot fix a predicate that selects the wrong data or a file layout that multiplies splits. Check: run EXPLAIN (distributed) and compare input rows, split count, and scanned bytes with a query that filters directly on event_day. If the plan shows all partitions, the diagnosis is confirmed. The ETL workload on the same engine may also add queueing, but it is not the primary cause of the full scan.

1. Rewrite the predicate: have the dashboard pass a date range that maps to event_day BETWEEN '20240501' AND '20240531' (or a typed date range). Remove substr from analyst queries. Expected effect: partition pruning cuts scanned bytes to the selected days and regions; this is the largest single win. 2. Compact small files: rewrite 2 MB files into 128-256 MB files per partition and expire old snapshots. Expected effect: fewer splits, less planning and COS request overhead, and more stable latency. 3. Separate ETL from BI and improve the join: move nightly ETL to another engine; ensure the 5,000-row dimension is broadcast and statistics exist for the fact table. Expected effect: less queueing and a cheaper join. Order matters: fix pruning first, then compaction, then isolation. Measure scanned bytes, split count, and p95 after each step so the team knows which change paid off rather than attributing all improvement to more hardware.

Use a benchmark harness on a bounded slice: one month of partitions and one region, queried with the dashboard's real SQL and concurrency. Capture EXPLAIN plans, scanned bytes, split count, CPU time, and wall time before and after each change. Compare against the production baseline for the same date range, not an unrelated query. Run a canary with a small user group and compare p95, queue time, and error rate for a week. Keep the old engine or table snapshot available for rollback. Define acceptance criteria before testing: sub-20-second p95 at the expected concurrent refresh rate, scanned bytes within the selected partitions, and no increase in failed queries. If the vendor cannot change generated SQL, test a compatibility view that exposes a derived partition predicate, and verify the optimizer still prunes. Document the plan evidence so the result is repeatable after the next ingestion change.