Matching: pick an index type for each query Intermediate

“Add an index” is too generic an answer. The right question is which type, and sometimes the answer is that no index is needed. Drag an index type onto each query.

• Every answer was actually run on PostgreSQL 17 with tables sized as in the prompt, so the explanations include measurements.
• The most commonly confused pairs: single-column B-tree vs partial, composite vs covering, and B-tree vs GIN for LIKE.

Matching

Which index type does this query need?

Each scenario is a real query on PostgreSQL 17. Drag the best-fitting index type onto each scenario. One type can be used for several scenarios, and some types go unused.

Tip: look at the operator in the WHERE and the shape of the data first, then think about the index.

Tap Choose answer on each case. On a computer you can drag a card below onto a case.

No new index neededMulti-column B-tree (order matters)BRINSingle-column B-treePartial indexExpression indexCovering index (INCLUDE)GIN

Solved 0/8 · 0 mistakes

1Case-insensitive login on a 1-million-row users table. There is already an index on email, but the plan is still a Parallel Seq Scan, 42 ms.

SELECT id FROM users WHERE lower(email) = lower($1);

Choose answer ▾

2A worker fetches unprocessed work from a 2-million-row jobs table. Only 0.1% are pending; the rest are done and never queried again.

SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 50;

Choose answer ▾

3The "My orders" screen on a 30-million-row orders table. Each customer has about 30 orders; one B2B customer has 200,000.

SELECT id, status, total, created_at FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC LIMIT 20;

Choose answer ▾

4Filter products by attributes stored in an attrs jsonb column (1 million rows). Each product category has a different attribute set. Currently a Parallel Seq Scan, 46 ms.

SELECT id FROM products
WHERE attrs @> '{"brand": "b42", "color": "black"}';

Choose answer ▾

5An insert-only events table, about 2 billion rows, written in time order. Every query filters on a created_at range of a few hours. pg_stats.correlation for created_at is 1.

SELECT count(*) FROM events
WHERE created_at >= $1 AND created_at < $1 + interval '1 hour';

Choose answer ▾

6A 30-day revenue chart for a B2B customer (200,000 orders), polled constantly by a dashboard. There is already an index on (customer_id, created_at), but each query reads 1,292 buffers for 1,281 rows because the orders are scattered across the table.

SELECT created_at, total FROM orders
WHERE customer_id = $1
  AND created_at >= now() - interval '30 days';

Choose answer ▾

7An app_config table of 200 rows, fitting in 2 pages. Someone proposes adding an index on category because "every query should have an index".

SELECT key, value FROM app_config
WHERE category = 'payment';

Choose answer ▾

8A product search box matching any fragment of the name, on 1 million rows. There is already a B-tree index on name, and the plan is still a Parallel Seq Scan.

SELECT id FROM products
WHERE name LIKE '%' || $1 || '%';

Choose answer ▾

To understand why the operator decides the index type, read GIN, GiST and BRIN.