The 8-Second Nightmare: How One Change Reduced Latency by 99.9%
summary
A slow aggregation on millions of shift rows proves to be an indexing problem spotted in DataDog logs and inspected with EXPLAIN ANALYZE and SHOW INDEXES the query scans 3.3 million rows and takes ~9.45s because composite indexes never get used due to column order and missing prefiltering. Adding a scope to filter by employer_id and reordering the query to match the composite index (employee_id then reference_date) drops runtime to 0.003s — a 99.9% reduction and thousands fewer rows scanned.
AI-written summary · about
chapters (7)
- 0:00Introduction
- 1:03The 8-second nightmare
- 10:01Investigating slow queries
- 20:33Understanding query costs
- 24:32Root cause analysis
- 26:20Implementing the fix
- 28:50Results and learnings
related talks
16mExploring Query Optimization: How a few lines of code can save hours of time- 16mExploring Query Optimization: How a few lines of code can save hours of time
38mTaming Your Query Latency
26mWhere do the performance cliffs come from?
07mIndeksimi dhe përmirësimi i performancës në SQL
38mAccelerating Query Performance in CrateDB
41mUsing Databases to Pull Your Application Weight
25mPowerful Data: Making Rails Apps Faster with Aggregation