Skip to content

Partial, expression, covering (INCLUDE), GIN and BRIN indexes

Shape the index to the query, and know what it still has to read.

Predict first

Before you read: predict

A payments ledger keeps 100,000 payouts; 2% are pending. To speed up the pending queue, someone added

CREATE INDEX payouts_pending ON payouts (merchant_id) WHERE status = 'pending';

The table has no other index on merchant_id. A merchant's statement page then runs SELECT count(*) FROM payouts WHERE merchant_id = 7.

How does PostgreSQL answer it?

Pick one

The 30-second version

An index helps only the queries it matches. A partial index holds rows matching its WHERE; a query uses it only if its own WHERE implies that. An expression index serves only the same expression. A covering index skips the table except on pages not all-visible. GIN indexes the keys inside jsonb; BRIN keeps a min–max per block range.

Shape the index to the query, and know what it still has to read.

How it works

Step 1 of 12

Fit the index to the query. A plain B-tree on a column serves conditions on that column as stored (db-btree-index-mechanics).

Analogy: A big seasonal cookbook and the indexes at its back (cookery publishing)

Each index is built for one kind of question.

In the cookbook In the database
the "under 30 minutes" index lists only the quick recipes a partial index (step 2)
a reader after any dish, quick or not, cannot use that list a query whose WHERE does not imply the predicate (step 3)
the general index files "Tomato, roast" under the cleaned-up headword, not as the title prints it an expression index and its matching expression (step 4)
each entry also prints the cooking time, so "under 20 minutes?" is answered at the back a covering index and an index-only scan (step 5)
a page carrying a "revised in this printing" sticker must be turned to and checked a page not all-visible: a heap fetch (steps 6–7)
the ingredient index lists every ingredient inside every recipe GIN on the elements inside a value (step 8)
the fore-edge thumb tabs show only which months each chapter spans BRIN's min–max per block range (steps 10–11)

In real terms: each index answers one question shape; a question it was not built for goes to the pages.

Breaks down when: a cookbook is printed once, while a table changes under its indexes and every write updates every index on it (step 9).

Examples, run for real

4.1 ExamHall EXH-3820 → MR !4120: the queue of unresolved flags

ExamHall's proctor queue asks for an exam's unresolved flags, WHERE exam_id = 42 AND resolved_at IS NULL. About 1% of flags are unresolved. The shipped index was on proctor_flags (exam_id); MR !4120 replaces it with a partial one:

4.1 exam 42: 500 flags, 5 unresolved
  shipped (exam_id), 180 index pages                      Bitmap Heap Scan -> Bitmap Index Scan using flags_exam · rows 5 · removed by filter 495 · buffers 503
  MR !4120 (exam_id) WHERE resolved_at IS NULL, 5 pages   Bitmap Heap Scan -> Bitmap Index Scan using flags_open · rows 5 · removed by filter 0 · buffers 7

The shipped index found all 500 flags of exam 42 and the table check threw 495 away. The partial index holds only unresolved flags, so it is 5 pages instead of 180, and the same query reads 7 buffers. The query's resolved_at IS NULL matches the predicate exactly (steps 2–3).

4.2 ExamHall's proctor_events: a BRIN index, and pages it has not summarized

proctor_events is append-only, one event every two seconds, with a BRIN index on occurred_at. One hour's count, then later events appended, then VACUUM:

4.2 table 1471 pages · BRIN 3 pages (a B-tree on the same column: 551)
  one hour, after VACUUM          Aggregate -> Bitmap Heap Scan -> Bitmap Index Scan using events_brin · rows 1 · removed by filter 0 · removed by recheck 15608 · heap blocks exact 0 lossy 128
  77895 later events appended (2044 pages)
  same hour, before VACUUM        Aggregate -> Bitmap Heap Scan -> Bitmap Index Scan using events_brin · rows 1 · removed by filter 0 · removed by recheck 84607 · heap blocks exact 0 lossy 636
  same hour, after VACUUM         Aggregate -> Bitmap Heap Scan -> Bitmap Index Scan using events_brin · rows 1 · removed by filter 0 · removed by recheck 32383 · heap blocks exact 0 lossy 252
  the last range (pages 1920–2043, 124 pages) is partly filled: VACUUM left it unsummarized, so it is still read (128 + 124 = 252)
  before VACUUM: pages 1536–2043 (508) fall in ranges never summarized: 128 + 508 = 636

The BRIN index is 3 pages against a B-tree's 551. The hour sits in one 128-page range, so 128 pages are read and rechecked (steps 10–11). The new pages had no summary, so the old hour read them all, 636 pages, until VACUUM summarized the full ranges. The last, partly filled range stayed unsummarized and is still read (step 12).

4.3 ExamHall's attempt metadata: a GIN index on jsonb

Attempts carry a metadata jsonb; 1 in 211 recorded tab switches. Counting WHERE metadata ? 'tab_switches', before and after CREATE INDEX … USING gin (metadata):

4.3 attempts 670 pages · GIN 44 pages · 284 attempts have tab_switches
  no index        Aggregate -> Seq Scan · rows 1 · removed by filter 59716 · heap blocks exact 0 lossy 0 · buffers 670
  GIN (metadata)  Aggregate -> Bitmap Heap Scan -> Bitmap Index Scan using attempts_meta · rows 1 · removed by filter 0 · heap blocks exact 284 lossy 0 · buffers 286

The GIN index holds an entry for each key inside each document, so the key-exists test ? finds the 284 attempts directly (step 8).

Picture it

Concept illustration for this lesson.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 6

A ticketing site's fans table holds 50,000 e-mail addresses in mixed case (the sign-up form runs trim() on each one and keeps its case). Its only index is CREATE INDEX fans_upper ON fans (upper(email)).

Which clause, put in SELECT id FROM fans WHERE ___, finds fan 4242 through fans_upper?

Pick one

Question 2 of 6

A streaming catalogue stores each video's attributes in attrs jsonb, with CREATE INDEX videos_attrs ON videos USING gin (attrs). Two filters find the same 5,000 dramas:

X: WHERE attrs @> '{"genre": "drama"}' Y: WHERE attrs->>'genre' = 'drama'

Which of them can use videos_attrs?

Pick one

Question 3 of 6

A payments ledger writes an entry every 30 seconds; the table is 1,870 pages, with a BRIN index on created_at. Copy 1 is the table as written; a one-day count there reads 256 of its pages. Copy 2 holds the same rows, reloaded in merchant order, with the same BRIN index, after VACUUM ANALYZE.

Roughly how many table pages does the same one-day count read on copy 2?

Pick one

Question 4 of 6

A payments ledger has CREATE INDEX payments_merchant ON payments (merchant_id) INCLUDE (amount_cents). After VACUUM, SELECT sum(amount_cents) FROM payments WHERE merchant_id = 7 runs as an Index Only Scan. Merchant 7 has 1,000 payments. Then three of them, all on one table page that holds 76 of merchant 7's payments, get a new note.

What does the same query show for Heap Fetches now?

Pick one

Question 5 of 6

A payments ledger has CREATE INDEX payouts_large ON payouts (merchant_id) WHERE amount_cents > 500000 and no other index on merchant_id.

Which clause, put in SELECT count(*) FROM payouts WHERE merchant_id = 7 AND ___, lets the plan use payouts_large?

Pick one

Question 6 of 6

A chat service has CREATE INDEX unread_by_recipient ON messages (recipient_id) INCLUDE (id) WHERE read_at IS NULL, and has just run VACUUM. The unread list runs SELECT id FROM messages WHERE recipient_id = 42 AND read_at IS NULL; recipient 42 has 6 unread messages.

What does the plan show?

Pick one

Go deeper

Pro tip
  1. Before shipping a partial or expression index, run EXPLAIN on the exact query text the application sends, and check the plan names the new index. When: any migration adding an index with WHERE or an expression (steps 3–4). Status: SELF. A bound value lets the planner use the partial index when it plans with that value. That is a custom plan, which "is generated for a specific execution using the parameter values given in that call"; an unnamed statement, sent once per query, gets one, since "Query planning typically occurs when the Bind message is processed." A cached generic plan cannot use the index, since $1 might be any value; this is the case the Partial Indexes page means by "parameterized query clauses do not work with a partial index". A named prepared statement under the default plan_cache_mode starts with custom plans ("the first five executions are done with custom plans") and may then switch to a generic one, so check its plan with plan_cache_mode = force_generic_plan too.
Source

quote-check.mjs checks every quotation below verbatim in both versions (CL-069 (1)). Valid as of 2026-10-13.

  • PostgreSQL 17 and 18, Partial Indexes (the row's first source), https://www.postgresql.org/docs/17/indexes-partial.html and https://www.postgresql.org/docs/18/indexes-partial.html: "The index contains entries only for those table rows that satisfy the predicate." (step 2); "a partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index." and "Matching takes place at query planning time, not at run time." (step 3); "As a result, parameterized query clauses do not work with a partial index.".
  • PostgreSQL 17 and 18, PREPARE, https://www.postgresql.org/docs/17/sql-prepare.html and the 18 page: "A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call." and "By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters." and "The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated.".
  • PostgreSQL 17 and 18, Message Flow (the extended query protocol), https://www.postgresql.org/docs/17/protocol-flow.html and the 18 page: "Query planning typically occurs when the Bind message is processed.". That an unnamed statement, sent once, is planned with its bound value is read from the run, not quoted.
  • PostgreSQL 17 and 18, Indexes on Expressions, https://www.postgresql.org/docs/17/indexes-expressional.html and the 18 page: "This query can use an index if one has been defined on the result of the lower(col1) function:" (step 4); "Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row insertion and non-HOT update." (step 4, write cost).
  • PostgreSQL 17 and 18, Index-Only Scans and Covering Indexes, https://www.postgresql.org/docs/17/indexes-index-only-scans.html and the 18 page: "Visibility information is not stored in index entries, only in heap entries" (step 6); "If it's not set, the heap entry must be visited to find out whether it's visible, so no performance advantage is gained over a standard index scan." (step 7).
  • PostgreSQL 17 and 18, Visibility Map, https://www.postgresql.org/docs/17/storage-vm.html and the 18 page: "The first bit, if set, indicates that the page is all-visible, or in other words that the page does not contain any tuples that need to be vacuumed." (steps 6–7).
  • PostgreSQL 17 and 18, GIN Indexes (the row's second source), https://www.postgresql.org/docs/17/gin.html and the 18 page: "GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search for element values that appear within the composite items." (step 8); "inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed item)." (step 9).
  • PostgreSQL 17 and 18, JSON Types, https://www.postgresql.org/docs/17/datatype-json.html and the 18 page: "The default GIN operator class for jsonb supports queries with the key-exists operators ?, ?| and ?&, the containment operator @>, and the jsonpath match operators @? and @@.".
  • PostgreSQL 17 and 18, BRIN Indexes, https://www.postgresql.org/docs/17/brin.html and the 18 page: "BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table." (step 11); "Data types having a linear sort order can have operator classes that store the minimum and maximum value within each block range" (step 10); "those tuples remain unsummarized until a summarization run is invoked later, creating the initial summary for that range." (step 12). Step 10's default of 128 pages per range is read from the run (4.2's ranges end at multiples of 128), not quoted.

Related lessons

Checked against the tools and docs as of 2026-10-13.