How to teach an elephant to dance rock'n'roll
summary
PostgreSQL query optimization beyond indexes: explains how the optimizer and executor work and shows concrete techniques—loose index scans, using IOS in nonstandard cases, replacing IN (long value lists) with JOIN VALUES(), moving LIMIT/OFFSET inside joins, and tricks with row types and arrays. Applies these methods to optimize a typical "friends feed/subscription news" query and compares performance of four approaches: simple direct SQL, an optimized direct SQL, PL/pgSQL implementations, and converting read-only PL/pgSQL into equivalent plain SQL.
AI-written summary · about
chapters (7)
- 0:00Introduction
- 10:14Performance strategies
- 20:44Case studies
- 30:15Challenges solutions
- 40:03Results learnings
- 50:10Conclusion next steps
- 57:10Closing
related talks
51mPostgreSQL: Practical Examples of SQL Query Optimization
34mSimple and Awesome Database Tricks
45mSELECT 'amazing_features' FROM "postgresql"
42mPostgreSQL Tips & Tricks For App Devs
50mImproving Postgres' Efficiency
29mExplaining the Postgres Query Optimizer
20mTeaching elephants to fish
26mBeyond Joins and Indexes