Exploring Query Optimization: How a few lines of code can save hours of time
summary
Backfilling 1.5 years of clickstream (4 billion rows) on Amazon Redshift crashes nightly ETLs until a few changes cut runtime from seven+ hours to under one hour. Use an EXPLAIN plan to see estimated cost before waiting, pick dist style key and choose the dist key that matches join columns (event_id or session_id), and set a sort key on session_start_time so WHERE filters avoid full-table scans; aggregate pageviews to sessions before joining.
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%
45mPerformance tricks you have never seen before 2.0
32mEXPLAIN: make it make sense
25mData Demons: Unleashing Chaos or Driving Performance?
41mUsing Databases to Pull Your Application Weight
42mTips & Tricks to Keep Your Queries under 100ms with ClickHouse®