Skip to content
Hands-on IT IT Drills

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).
  • EXPLAIN shows the plan, EXPLAIN ANALYZE executes 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 pg1 in the database tag05. 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;
EOF

Expected (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;
EOF

Expected (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 ANALYZE really executes the query – including DELETE and UPDATE. Wrap those in BEGIN; … 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;
EOF

Expected (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';
EOF

Expected (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;
EOF

Expected:

      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]';
EOF

Expected (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';
EOF

Expected (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;
EOF

Expected (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'
wait

Expected (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;
EOF

Expected (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;
EOF

Expected (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';
EOF

Expected (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;
EOF

Expected (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

  1. Show all indexes with ix_ in the name along with size and type. Target time: 10 s
  2. Show the plan for SELECT * FROM bestellung WHERE id = 1 without executing it. Target time: 15 s
  3. Update the statistics of bestellung and show how many pages ANALYZE reads. Target time: 20 s
  4. 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;
EOF

Expected (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 tag05

The 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 for INVALID; 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_statements and auto_explain (Day 7) show which plans really cost time.
  • Regular index reviews: unused, duplicate and INVALID indexes cost write performance, WAL volume and backup space.
  • Large indexes in maintenance windows or with CONCURRENTLY and maintenance_work_mem; progress in pg_stat_progress_create_index.
  • Plans can flip after a major upgrade or new statistics – after pg_upgrade, rebuild missing statistics – from 18 on with vacuumdb --all --analyze-in-stages --missing-stats-only (Day 13).
  • For correlated columns, extended statistics (CREATE STATISTICS) help before you start tweaking planner parameters.