Exploring Query Optimization: How a few lines of code can save hours of time
summary
An analyst backfills 4 billion rows into Amazon Redshift, crashes nightly ETLs and turns a seven hour query into under one hour after rewriting the job. Uses EXPLAIN plan to measure estimated cost, changes table distribution to dist style key (choosing session_id for joins) and adds a sort key on session_start_time so WHERE filters avoid full-table scans. Result: runtime drops by ~6 hours, joins use all compute nodes, and temporary session-level tables become efficient for downstream joins.
AI-written summary · about
chapters (8)
- 0:00Query optimization intro
- 1:11Disastrous backfill experience
- 5:10Query optimization basics
- 7:41Distribution styles
- 9:31Sort keys power
- 11:40Optimizing real queries
- 13:52Results and lessons
- 14:53Final tips
related talks
16mExploring Query Optimization: How a few lines of code can save hours of time
38mAccelerating Query Performance in CrateDB
31mThe 8-Second Nightmare: How One Change Reduced Latency by 99.9%
32mEXPLAIN: make it make sense
23mOne day with Redshift
25mOptimize your Data Pipeline without Rewriting it
45mPerformance tricks you have never seen before 2.0
25mData Demons: Unleashing Chaos or Driving Performance?