Things your explain plan is not telling you
summary
Shows concrete gaps in PostgreSQL EXPLAIN: large JSONB values (10,000 rows with 32 KB each) are stored in a TOAST table so EXPLAIN can report 0.9 ms but real client-side work is 1.7 s; Postgres 17 adds explain serialized text to measure serialization. Repeated detoasting across 100 rule sets makes a 300 ms plan balloon to 2.5 s; forcing eager detoast or using subqueries cuts it to ~300 ms. Visibility and long open transactions make an index scan hit 95k pages; clustering and correct index column order can yield ~100x speedups.
AI-written summary · about
chapters (8)
- 0:00Introduction and setup
- 1:50What explain plans reveal
- 8:41TOAST performance pitfalls
- 21:26Visibility and long transactions
- 29:16Index-only scans and caching
- 33:12Index condition order impact
- 36:45Limitations and future improvements
- 37:20Q&A session
related talks
32mEXPLAIN: make it make sense
35mLeveraging PostgreSQL 17's JSONB for Enhanced Data Modeling
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
48mPostgres and Json - How to get the best of them?
34mSimple and Awesome Database Tricks
41mUsing Databases to Pull Your Application Weight
26mWhere do the performance cliffs come from?
47mIdentifying Slow Queries and Fixing Them!