07/ 11·6 lessons·2 h 20 min

Indexing and EXPLAIN

Performance through reading plans: EXPLAIN ANALYZE with buffers (on by default in PG18), B-tree and column order (PG18 skip-scan), when indexes don't help (expression index), partial/covering/unique and Index-Only Scan, GIN for jsonb/arrays, CREATE INDEX CONCURRENTLY.

Start module→Continue · Reading EXPLAIN ANALYZE→Reread module→Next module →
/ lessons

Lessons

6 lessons · 2 h 20 min
  1. 01Reading EXPLAIN ANALYZE↳ continue from here#explain#analyze#buffers25m
  2. 02B-tree and column order↳ continue from here#btree#composite-index#left-prefix25m
  3. 03When indexes don't help↳ continue from here#non-sargable#expression-index#lower20m
  4. 04Partial, covering, and unique↳ continue from here#partial-index#covering-index#include25m
  5. 05GIN for jsonb and arrays↳ continue from here#gin#jsonb#arrays25m
  6. 06CREATE INDEX CONCURRENTLY↳ continue from here#create-index-concurrently#locks#migrations20m