Day 5: Indexes and EXPLAIN
Goal: You read execution plans, recognize Seq Scan, Index Scan and Bitmap Scan, build the right index (B-tree, multicolumn, partial, expression, GIN) without a write lock and find indexes that only cost space. · Duration: approx. 90 min · Prerequisite: Day 1
Briefing
- The planner estimates the cost of every possible path and takes the
cheapest. The estimate is based on statistics
(
ANALYZE). EXPLAINshows the plan,EXPLAIN ANALYZEexecutes the query and puts estimate and reality side by side.- An index speeds up reads and slows down every write. It pays off when the query needs a small part of the table.
- Everything on
pg1in the databasetag05. The timings depend on your VM.
Drill 1: Test table, EXPLAIN and EXPLAIN ANALYZE
Two million orders with generate_series; this takes
about a minute:
createdb tag05
psql -d tag05 <<'EOF'
CREATE TABLE bestellung (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kunde_id int NOT NULL,
status text NOT NULL,
betrag numeric(10,2) NOT NULL,
email text NOT NULL,
angelegt timestamptz NOT NULL,
daten jsonb NOT NULL,
notiz text
);
INSERT INTO bestellung (kunde_id, status, betrag, email, angelegt, daten, notiz)
SELECT (random() * 49999)::int + 1,
CASE WHEN i % 100 = 0 THEN 'offen' WHEN i % 10 = 0 THEN 'storniert'
ELSE 'geliefert' END,
round((random() * 500)::numeric, 2),
'Kunde' || (i % 50000) || '@Example.org',
now() - i * interval '10 seconds',
jsonb_build_object('kanal', (ARRAY['web','app','telefon'])[i % 3 + 1],
'gutschein', i % 7 = 0),
(ARRAY['schnelle Lieferung bitte', 'Geschenkverpackung',
'beim Nachbarn abgeben', 'Rechnung per Post'])[i % 4 + 1] || ' ' || i
FROM generate_series(1, 2000000) AS i;
VACUUM ANALYZE bestellung;
SELECT pg_size_pretty(pg_table_size('bestellung')) AS tabelle,
pg_size_pretty(pg_indexes_size('bestellung')) AS indizes;
EOFExpected (abridged):
INSERT 0 2000000
VACUUM
tabelle | indizes
---------+---------
313 MB | 43 MB
The 43 MB of index are the primary key. Now a query without a matching index:
psql -d tag05 <<'EOF'
EXPLAIN SELECT * FROM bestellung WHERE kunde_id = 4711;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EOFExpected (long lines wrapped):
QUERY PLAN
--------------------------------------------------------------------------------
Gather (cost=1000.00..51420.77 rows=41 width=124)
Workers Planned: 2
-> Parallel Seq Scan on bestellung (cost=0.00..50416.67 rows=17 width=124)
Filter: (kunde_id = 4711)
Gather (cost=1000.00..51420.77 rows=41 width=124) (actual rows=39.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared read=40000
-> Parallel Seq Scan on bestellung (cost=0.00..50416.67 rows=17 width=124)
(actual rows=13.00 loops=3)
Filter: (kunde_id = 4711)
Rows Removed by Filter: 666654
Buffers: shared read=40000
Planning Time: 0.097 ms
Execution Time: 319.649 ms
How to read the plan: from the inside out.
cost=startup..total in abstract units, rows is
the estimate (41), actual rows the reality (39) – per loop,
so with loops=3 (two workers plus the leader) 3 × 13. Since
PostgreSQL 18, EXPLAIN ANALYZE shows buffers automatically:
40,000 pages of 8 kB read, i.e. the whole table, to find 39 rows.
TIMING OFF skips the per-node timing and makes the output
narrower.
Warning:
EXPLAIN ANALYZEreally executes the query – includingDELETEandUPDATE. Wrap those inBEGIN; … ROLLBACK;.
Save your progress? Sign up for free
Drill 2: B-tree – Index Scan, Bitmap Scan, Index Only Scan
psql -d tag05 <<'EOF'
\timing on
CREATE INDEX ix_kunde ON bestellung (kunde_id);
\timing off
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM bestellung WHERE id = 4711;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM bestellung WHERE kunde_id BETWEEN 1000 AND 1100;
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id > 100;
EOFExpected (abridged):
CREATE INDEX
Time: 2171.529 ms (00:02.172)
Bitmap Heap Scan on bestellung (actual rows=39.00 loops=1)
Recheck Cond: (kunde_id = 4711)
Heap Blocks: exact=39
Buffers: shared read=42
-> Bitmap Index Scan on ix_kunde (actual rows=39.00 loops=1)
Index Cond: (kunde_id = 4711)
Index Searches: 1
Buffers: shared read=3
Execution Time: 2.818 ms
Index Scan using bestellung_pkey on bestellung (actual rows=1.00 loops=1)
Index Cond: (id = 4711)
Index Searches: 1
Buffers: shared hit=3 read=4
Execution Time: 0.222 ms
Aggregate (actual rows=1.00 loops=1)
Buffers: shared hit=204 read=4
-> Index Only Scan using ix_kunde on bestellung (actual rows=4099.00 loops=1)
Index Cond: ((kunde_id >= 1000) AND (kunde_id <= 1100))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=204 read=4
Seq Scan on bestellung
Filter: (kunde_id > 100)
From 320 ms down to 3 ms, 42 instead of 40,000 pages. The four access methods:
| Plan | When | Characteristic |
|---|---|---|
| Index Scan | very few rows | index, then table, row by row |
| Bitmap Heap Scan | some to many rows | first a page bitmap, then the table |
| Index Only Scan | all columns in the index | table only on Heap Fetches |
| Seq Scan | large share of the table | whole table sequentially |
Index Only Scan works only if the visibility map knows
the pages as “all visible” – VACUUM maintains that (Day 6).
kunde_id > 100 hits almost all rows, so the Seq Scan is
right. Index Searches is new in PostgreSQL 18.
Save your progress? Sign up for free
Drill 3: Multicolumn indexes and skip scan
psql -d tag05 <<'EOF'
CREATE INDEX ix_status_zeit ON bestellung (status, angelegt);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id FROM bestellung
WHERE status = 'offen' AND angelegt > now() - interval '1 day';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM bestellung WHERE angelegt > now() - interval '1 hour';
EOFExpected (abridged):
Index Scan using ix_status_zeit on bestellung (actual rows=86.00 loops=1)
Index Cond: ((status = 'offen'::text) AND (angelegt > (now() - '1 day'::interval)))
Index Searches: 1
Execution Time: 3.133 ms
Aggregate (actual rows=1.00 loops=1)
-> Index Only Scan using ix_status_zeit on bestellung (actual rows=348.00 loops=1)
Index Cond: (angelegt > (now() - '01:00:00'::interval))
Heap Fetches: 0
Index Searches: 4
Execution Time: 2.106 ms
A multicolumn B-tree is sorted by the first column and, within equal
values, by the second – like a phone book by last name, then first name.
Rule: equality conditions first, ranges last. The second query filters
only on the second column. Up to PostgreSQL 17 that
usually meant a Seq Scan; since 18 the planner can use a skip scan to
jump directly to each of the three status values
(Index Searches: 4). That only pays off if the first column
has few distinct values.
Save your progress? Sign up for free
Drill 4: Partial index and expression index
Open orders are 1 % of the table but are queried constantly:
psql -d tag05 <<'EOF'
CREATE INDEX ix_offen ON bestellung (angelegt) WHERE status = 'offen';
SELECT indexrelid::regclass AS index,
pg_size_pretty(pg_relation_size(indexrelid)) AS groesse
FROM pg_index WHERE indrelid = 'bestellung'::regclass ORDER BY 1;
EXPLAIN (COSTS OFF)
SELECT id FROM bestellung WHERE status = 'offen' ORDER BY angelegt DESC LIMIT 10;
EOFExpected:
index | groesse
-----------------+---------
bestellung_pkey | 43 MB
ix_kunde | 14 MB
ix_status_zeit | 77 MB
ix_offen | 456 kB
(4 rows)
QUERY PLAN
--------------------------------------------------------
Limit
-> Index Scan Backward using ix_offen on bestellung
(2 rows)
456 kB instead of 77 MB: the partial index contains only rows that
satisfy the WHERE condition. The planner uses it only if
the query contains the same condition. Reading the index backwards saves
the sort.
Now a search that ignores case:
psql -d tag05 <<'EOF'
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id FROM bestellung WHERE lower(email) = '[email protected]';
CREATE INDEX ix_email_lower ON bestellung (lower(email));
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id FROM bestellung WHERE lower(email) = '[email protected]';
EXPLAIN (COSTS OFF) SELECT id FROM bestellung WHERE email = '[email protected]';
EOFExpected (abridged):
Gather (actual rows=40.00 loops=1)
-> Parallel Seq Scan on bestellung (actual rows=13.33 loops=3)
Filter: (lower(email) = '[email protected]'::text)
Rows Removed by Filter: 666653
Execution Time: 814.361 ms
CREATE INDEX
Bitmap Heap Scan on bestellung (actual rows=40.00 loops=1)
Recheck Cond: (lower(email) = '[email protected]'::text)
-> Bitmap Index Scan on ix_email_lower (actual rows=40.00 loops=1)
Index Cond: (lower(email) = '[email protected]'::text)
Execution Time: 1.273 ms
Gather
Workers Planned: 2
-> Parallel Seq Scan on bestellung
Filter: (email = '[email protected]'::text)
An expression index stores the result of lower(email)
and helps only queries that use exactly this expression – the search on
email itself comes away empty-handed.
Save your progress? Sign up for free
Drill 5: GIN for jsonb and full-text search
B-trees can’t do “contains”. That’s what GIN (Generalized Inverted Index) is for: one entry per key or word, with the list of all rows. First, add a rare attribute to the JSON:
psql -d tag05 <<'EOF'
UPDATE bestellung SET daten = daten || '{"reklamation": true}' WHERE id % 5000 = 0;
VACUUM ANALYZE bestellung;
CREATE INDEX ix_daten ON bestellung USING gin (daten jsonb_path_ops);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM bestellung WHERE daten @> '{"reklamation": true}';
EXPLAIN (COSTS OFF)
SELECT count(*) FROM bestellung WHERE daten->>'kanal' = 'telefon';
EOFExpected (abridged):
UPDATE 400
Aggregate (actual rows=1.00 loops=1)
-> Bitmap Heap Scan on bestellung (actual rows=400.00 loops=1)
Recheck Cond: (daten @> '{"reklamation": true}'::jsonb)
Heap Blocks: exact=10
-> Bitmap Index Scan on ix_daten (actual rows=400.00 loops=1)
Index Cond: (daten @> '{"reklamation": true}'::jsonb)
Execution Time: 1.002 ms
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on bestellung
Filter: ((daten ->> 'kanal'::text) = 'telefon'::text)
The GIN index helps the operator @> (contains), not
->> with =. For the latter, an
expression index on (daten->>'kanal') would be the
right one. jsonb_path_ops is smaller than the default class
but supports only @> and JSON paths.
Full-text search breaks text into normalized words (lexemes):
psql -d tag05 <<'EOF'
SELECT to_tsvector('german', 'beim Nachbarn abgeben 4710');
\timing on
CREATE INDEX ix_notiz_fts ON bestellung USING gin (to_tsvector('german', notiz));
\timing off
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM bestellung
WHERE to_tsvector('german', notiz) @@ to_tsquery('german', 'Nachbarn & 4710');
SELECT pg_size_pretty(pg_relation_size('ix_daten')) AS daten,
pg_size_pretty(pg_relation_size('ix_notiz_fts')) AS fts;
EOFExpected (abridged, long lines wrapped):
to_tsvector
------------------------------------------
'4710':4 'abgeb':3 'beim':1 'nachbarn':2
CREATE INDEX
Time: 26015.517 ms (00:26.016)
Aggregate (actual rows=1.00 loops=1)
-> Bitmap Heap Scan on bestellung (actual rows=1.00 loops=1)
Recheck Cond: (to_tsvector('german'::regconfig, notiz) @@
'''nachbarn'' & ''4710'''::tsquery)
-> Bitmap Index Scan on ix_notiz_fts (actual rows=1.00 loops=1)
Index Cond: (to_tsvector('german'::regconfig, notiz) @@
'''nachbarn'' & ''4710'''::tsquery)
Execution Time: 0.575 ms
daten | fts
---------+--------
4432 kB | 112 MB
The query must call to_tsvector with the same language
configuration as the index. GIN indexes are large and expensive to build
(26 s here), but searches are fast.
Save your progress? Sign up for free
Drill 6: CREATE INDEX CONCURRENTLY
A normal CREATE INDEX blocks all writes for the entire
build time:
psql -d tag05 -c 'CREATE INDEX ix_betrag ON bestellung (betrag)' &
sleep 1
psql -d tag05 -c "SELECT DISTINCT l.mode FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'bestellung'::regclass AND a.query LIKE 'CREATE%'"
wait
psql -d tag05 -c 'DROP INDEX ix_betrag'
psql -d tag05 -c 'CREATE INDEX CONCURRENTLY ix_betrag ON bestellung (betrag)' &
sleep 1
psql -d tag05 -c "SELECT DISTINCT l.mode FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'bestellung'::regclass AND a.query LIKE 'CREATE%'" \
-c 'SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index'
waitExpected (abridged):
mode
-----------
ShareLock
CREATE INDEX
DROP INDEX
mode
--------------------------
ShareUpdateExclusiveLock
phase | blocks_done | blocks_total
--------------------------------+-------------+--------------
building index: scanning table | 33676 | 40009
CREATE INDEX
ShareLock allows reads but no
INSERT/UPDATE/DELETE.
ShareUpdateExclusiveLock blocks only other DDL. In
exchange, CONCURRENTLY reads the table twice and waits for
all running transactions – it must not run inside a BEGIN
block. If it goes wrong, a wreck remains:
psql -d tag05 <<'EOF'
CREATE UNIQUE INDEX CONCURRENTLY ix_email_uniq ON bestellung (email);
\d bestellung
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY ix_email_uniq;
EOFExpected (abridged):
ERROR: could not create unique index "ix_email_uniq"
DETAIL: Key (email)=([email protected]) is duplicated.
...
"ix_email_uniq" UNIQUE, btree (email) INVALID
...
indexrelid | indisvalid
---------------+------------
ix_email_uniq | f
DROP INDEX
An INVALID index is never used for reads. If it is
already ready (indisready = t, the build failed only in the
validation phase), every write still maintains it – pure overhead.
Either drop it or rebuild it with
REINDEX INDEX CONCURRENTLY once the cause is fixed.
Save your progress? Sign up for free
Drill 7: Find unused indexes
psql -d tag05 <<'EOF'
SELECT indexrelname AS index, idx_scan, last_idx_scan IS NOT NULL AS benutzt,
pg_size_pretty(pg_relation_size(indexrelid)) AS groesse
FROM pg_stat_user_indexes WHERE relname = 'bestellung'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;
SELECT pg_size_pretty(pg_indexes_size('bestellung')) AS alle_indizes,
pg_size_pretty(pg_table_size('bestellung')) AS tabelle;
EOFExpected (numbers depend on your queries):
index | idx_scan | benutzt | groesse
-----------------+----------+---------+---------
ix_betrag | 0 | f | 43 MB
ix_offen | 0 | f | 456 kB
ix_email_lower | 1 | t | 15 MB
ix_daten | 1 | t | 4432 kB
ix_notiz_fts | 3 | t | 112 MB
bestellung_pkey | 3 | t | 43 MB
ix_kunde | 3 | t | 14 MB
ix_status_zeit | 5 | t | 77 MB
(8 rows)
alle_indizes | tabelle
--------------+---------
309 MB | 313 MB
By now the indexes are almost as large as the table.
ix_offen shows 0 because an EXPLAIN without
ANALYZE executes nothing. last_idx_scan (since
16) tells you when an index was last needed. Check before dropping:
since when the counters have been running (pg_stat_reset
resets them), whether the query runs only monthly, whether the index
backs a UNIQUE or primary key – and standbys count
separately.
Save your progress? Sign up for free
Drill 8: Statistics and planner traps
What the planner knows about the data:
psql -d tag05 -c "SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'bestellung' AND attname IN ('status', 'kunde_id')"Expected:
attname | n_distinct | most_common_vals | most_common_freqs
----------+------------+-----------------------------+--------------------------------
kunde_id | 48917 | {23737,9911,23095} | {0.00023333334,0.0002,0.0002}
status | 3 | {geliefert,storniert,offen} | {0.90043336,0.08976667,0.0098}
(2 rows)
ANALYZE reads a sample of 30,000 rows (300 ×
default_statistics_target) and stores the most common
values, histograms and the number of distinct values. If the statistics
go stale, the planner estimates wrong:
psql -d tag05 <<'EOF'
CREATE TABLE kopie AS SELECT * FROM bestellung WHERE id <= 200000;
ANALYZE kopie;
UPDATE kopie SET status = 'offen' WHERE status = 'storniert';
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id FROM kopie WHERE status = 'offen';
ANALYZE kopie;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id FROM kopie WHERE status = 'offen';
EOFExpected (long lines wrapped):
UPDATE 18000
Seq Scan on kopie (cost=0.00..7099.01 rows=2137 width=8)
(actual rows=20000.00 loops=1)
Filter: (status = 'offen'::text)
Rows Removed by Filter: 180000
ANALYZE
Seq Scan on kopie (cost=0.00..6882.00 rows=20207 width=8)
(actual rows=20000.00 loops=1)
Estimated 2137, actual 20,000 – off by a factor of 10. With joins,
this leads to wrong join methods and minutes instead of milliseconds.
Autovacuum analyzes only after 10 % of the rows have changed (Day 6);
after large loads, run ANALYZE yourself. Two classics where
an existing index doesn’t kick in:
psql -d tag05 <<'EOF'
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id = 4711.0;
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id = '4711';
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id + 0 = 4711;
EOFExpected (abridged):
-> Parallel Seq Scan on bestellung
Filter: ((kunde_id)::numeric = 4711.0)
Bitmap Heap Scan on bestellung
Recheck Cond: (kunde_id = 4711)
-> Bitmap Index Scan on ix_kunde
-> Parallel Seq Scan on bestellung
Filter: ((kunde_id + 0) = 4711)
4711.0 is numeric: PostgreSQL converts the
column, and the index on int no longer
fits. The string literal '4711' has no type yet and becomes
int – no problem. Any arithmetic or function on the column
(kunde_id + 0, date(angelegt)) disables the
index unless there is a matching expression index. Typical source: ORMs
that bind parameters with the wrong type.
Save your progress? Sign up for free
Exercise: Speed Drills
- Show all indexes with
ix_in the name along with size and type. Target time: 10 s - Show the plan for
SELECT * FROM bestellung WHERE id = 1without executing it. Target time: 15 s - Update the statistics of
bestellungand show how many pagesANALYZEreads. Target time: 20 s - List all invalid indexes in the database. Target time: 30 s
Solution
psql -d tag05 -c '\di+ ix_*'
psql -d tag05 -c 'EXPLAIN SELECT * FROM bestellung WHERE id = 1'
psql -d tag05 -c 'ANALYZE VERBOSE bestellung' 2>&1 | grep scanned
psql -d tag05 -Atc 'SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid'Expected (abridged):
Schema | Name | Type | Owner | Table | Persistence | ...
--------+----------------+-------+--------+------------+-------------+ ...
public | ix_betrag | index | ubuntu | bestellung | permanent | ...
...
Index Scan using bestellung_pkey on bestellung (cost=0.43..8.45 rows=1 width=124)
Index Cond: (id = 1)
INFO: "bestellung": scanned 30000 of 40009 pages, containing 1499657 live rows and
292 dead rows; 30000 rows in sample, ...
The last query returns nothing – as it should.
Save your progress? Sign up for free
Checkpoint
psql -d tag05 -Atc 'SELECT count(*) FROM bestellung'Expected: 2000000
psql -d tag05 -c "EXPLAIN (COSTS OFF) SELECT id FROM bestellung
WHERE status = 'offen' ORDER BY angelegt DESC LIMIT 10"Expected:
Index Scan Backward using ix_offen on bestellung
psql -d tag05 -c "EXPLAIN (COSTS OFF) SELECT count(*) FROM bestellung
WHERE daten @> '{\"reklamation\": true}'"Expected:
Bitmap Index Scan on ix_daten
psql -d tag05 -Atc "SELECT count(*) FROM pg_index WHERE NOT indisvalid"Expected: 0
Challenge
The customer page shows a customer’s five most recent orders:
SELECT id, angelegt FROM bestellung WHERE kunde_id = 4711 ORDER BY angelegt DESC LIMIT 5.
Get rid of the Sort in the plan without blocking writes,
and afterwards remove the index that has become redundant.
Hint: An index can deliver filter and sort order at the same time if the columns are in the right order.
Solution
psql -d tag05 <<'EOF'
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT id, angelegt FROM bestellung
WHERE kunde_id = 4711 ORDER BY angelegt DESC LIMIT 5;
CREATE INDEX CONCURRENTLY ix_kunde_zeit ON bestellung (kunde_id, angelegt);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT id, angelegt FROM bestellung
WHERE kunde_id = 4711 ORDER BY angelegt DESC LIMIT 5;
DROP INDEX CONCURRENTLY ix_kunde;
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EOFExpected (abridged):
Limit (actual rows=5.00 loops=1)
-> Sort (actual rows=5.00 loops=1)
Sort Key: angelegt DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on bestellung (actual rows=39.00 loops=1)
Execution Time: 2.297 ms
CREATE INDEX
Limit (actual rows=5.00 loops=1)
Buffers: shared hit=8
-> Index Scan Backward using ix_kunde_zeit on bestellung (actual rows=5.00 loops=1)
Index Cond: (kunde_id = 4711)
Execution Time: 0.290 ms
DROP INDEX
Bitmap Heap Scan on bestellung
Recheck Cond: (kunde_id = 4711)
-> Bitmap Index Scan on ix_kunde_zeit
(kunde_id, angelegt) covers all queries that
ix_kunde served – an index that starts with the columns of
another makes that other one redundant.
Save your progress? Sign up for free
Cleanup
dropdb tag05The 2 million rows are quickly regenerated; Day 6 builds its own data.
Interview Note
- “Why doesn’t PostgreSQL use my index?” – The query hits too many
rows, statistics are stale, the type doesn’t match or a function wraps
the column; compare estimate and reality with
EXPLAIN (ANALYZE). - “How do you create an index in production?” –
CREATE INDEX CONCURRENTLY, then check forINVALID; in migrations without a transaction block. - “B-tree or GIN?” – B-tree for equality, ranges, sorting; GIN for “contains”: jsonb, arrays, full-text search.
Enterprise Note
- You don’t find slow queries by chance:
pg_stat_statementsandauto_explain(Day 7) show which plans really cost time. - Regular index reviews: unused, duplicate and
INVALIDindexes cost write performance, WAL volume and backup space. - Large indexes in maintenance windows or with
CONCURRENTLYandmaintenance_work_mem; progress inpg_stat_progress_create_index. - Plans can flip after a major upgrade or new statistics – after
pg_upgrade, rebuild missing statistics – from 18 on withvacuumdb --all --analyze-in-stages --missing-stats-only(Day 13). - For correlated columns, extended statistics
(
CREATE STATISTICS) help before you start tweaking planner parameters.