skip to content

Indexes & Query Planning

PostgreSQL offers B-tree, GIN, GiST, SP-GiST, BRIN and hash indexes, and a cost-based planner chooses among them from statistics. Interviewers hand you EXPLAIN ANALYZE and ask why.

part ofPostgreSQLread it →
on this pageshow

explore

questions

page 1 of 2

In PostgreSQL, what index does a plain CREATE INDEX build, and which WHERE and ORDER BY clauses on that column can it serve?

level: juniorimportance: must knowfreq 72%

answer

  1. the method you get without USING
  2. five comparison operators and their combinations
  3. NULL tests count too
  4. stored ascending, nulls last
  5. constraints that create indexes, and one that doesn't

basics

~20 s

A plain CREATE INDEX builds a B-tree, PostgreSQL's default method. It serves <, <=, =, >=, > and combinations such as BETWEEN and IN, IS NULL and IS NOT NULL, and ORDER BY on the column in either direction.

solid answer

~40 s

Without a `USING` clause, `CREATE INDEX` uses `btree`. `PRIMARY KEY` and `UNIQUE` constraints also create unique B-tree indexes for you, but a `FOREIGN KEY` does not index the referencing columns. The planner considers a B-tree whenever the indexed column is compared with `<`, `<=`, `=`, `>=` or `>`, with constructs built from them such as `BETWEEN` and `IN`, and with `IS NULL` or `IS NOT NULL`. Entries are stored ascending with NULLs last, so a forward scan yields `ORDER BY published_on` and a backward scan yields `ORDER BY published_on DESC`, whose default is `NULLS FIRST`; `DESC NULLS LAST` needs an index declared that way. The index does not help `<>`, a function wrapped around the column such as `lower(title)`, or `LIKE 'Dune%'` in a non-C collation without a pattern operator class.

code

sql · 19 lines
sql
CREATE TABLE books (
  book_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category_id  integer NOT NULL,
  publisher_id integer NOT NULL REFERENCES publishers,
  title        text NOT NULL,
  price        numeric(10,2) NOT NULL,
  published_on date
);

-- btree by default; the PRIMARY KEY already created books_pkey
CREATE INDEX books_published_on_idx ON books (published_on);

-- both can use books_published_on_idx
SELECT title FROM books WHERE published_on IS NULL;
SELECT title FROM books ORDER BY published_on DESC LIMIT 20;

-- these need their own indexes
CREATE INDEX books_published_desc_idx ON books (published_on DESC NULLS LAST);
CREATE INDEX books_publisher_id_idx ON books (publisher_id);

go deeper

for a junior

Recall that btree is the default method, the five comparison operators it serves, and that IS NULL is indexable. Mention that primary keys and unique constraints create their indexes automatically.

for a middle

Explain NULL placement: ascending nulls-last storage, why DESC defaults to NULLS FIRST, and why DESC NULLS LAST forces a sort. Point out that foreign keys get no index automatically.

for a senior

Show judgment about which foreign keys deserve an index, when a declared sort order earns its own index, and how you confirm a Sort node disappeared by comparing plans.

for a principal

Frame index choice as a workload budget: each extra B-tree costs write time and storage, so favour indexes that serve several query shapes and retire ones nothing uses.

## What a plain CREATE INDEX builds PostgreSQL ships several index **access methods**: `btree`, `hash`, `gist`, `spgist`, `gin` and `brin`. You choose one with `CREATE INDEX ... USING method`, and when the `USING` clause is left out the default is **`btree`**. In `psql`, `\d books` lists every index with its method, for example `"books_published_on_idx" btree (published_on)`. The B-tree is the default because it handles the widest range of everyday queries: anything on a data type whose values can be sorted. Integers, `numeric`, `text`, `date`, `timestamptz`, `uuid` and `boolean` all have a default B-tree **operator class**, the catalogue entry that tells the index which comparison functions to use for the type. The other methods exist for operators that are not plain comparisons, such as containment, full-text matching or geometric nearness. ## The clauses a B-tree index can serve The planner considers a B-tree index when an indexed column is compared using one of the five B-tree operators, or a construct that reduces to them. Using the online bookstore's `books` table: | Clause on the indexed column | Can the B-tree serve it? | |---|---| | `price = 12.50`, `price < 20`, `price >= 5` | Yes: the five core operators | | `price BETWEEN 5 AND 20` | Yes: the parser turns it into `>=` and `<=` | | `category_id IN (3, 7, 9)` | Yes: it becomes `= ANY (array)` | | `published_on IS NULL` / `IS NOT NULL` | Yes | | `title LIKE 'Dune%'` | Only under the C collation or with a pattern operator class | | `price <> 10` | No: `<>` is not a B-tree operator | | `lower(title) = 'dune'` | No: needs an expression index on `lower(title)` | Two lessons follow from the table: - **The column must appear bare.** Wrapping it in a function, such as `lower(title)`, hides it from an index on `title`. An expression index on exactly that expression is the remedy. - **NULL tests are indexable.** A PostgreSQL B-tree stores entries for NULL values too, so `WHERE published_on IS NULL` (pre-order books with no release date yet) can use the index on `published_on`. ## NULLs and ordering A B-tree returns rows already in key order, which lets the planner drop a Sort node for `ORDER BY`. The same property speeds up `min()` and `max()`: the planner rewrites them into a subquery that reads one end of the index with `LIMIT 1`, adding `IS NOT NULL`. By default entries are stored **ascending with NULLs last**, and that default has consequences: 1. A forward scan satisfies `ORDER BY published_on`, which means `ASC NULLS LAST`. 2. A backward scan satisfies `ORDER BY published_on DESC`, because `DESC` defaults to `NULLS FIRST`, exactly the reverse of the stored order. 3. `ORDER BY published_on DESC NULLS LAST` matches neither direction, so the planner adds a Sort, unless an index is declared as `(published_on DESC NULLS LAST)`. The NULL placement rule is the usual reason a "newest first, undated last" listing still shows a Sort node even though an index exists on the column. ## Indexes PostgreSQL creates for you, and one it does not - A **`PRIMARY KEY`** or **`UNIQUE`** constraint automatically creates a unique B-tree index. Only the B-tree method supports unique indexes. - An **`EXCLUDE`** constraint also creates its own index, usually GiST. - A **`FOREIGN KEY`** does **not** index the referencing columns. A `books.publisher_id` foreign key leaves joins on `publisher_id`, and the reference check that runs when a publisher row is deleted, without an index until you create one. The foreign-key gap is a frequent interview follow-up, because it is a PostgreSQL behaviour that differs from what some developers expect, and it bites only once the referencing table is large. ## Choosing another method Reach for `gin`, `gist`, `brin` or `hash` when the operators you query with are not the five comparisons: containment on `jsonb` or arrays, text search, nearest-neighbour ordering, or block-range summaries of very large append-only tables. Those methods have their own operator classes and trade-offs. For equality, ranges, NULL tests and sorting on scalar columns, the default B-tree is the right answer, and it is the only method that can enforce uniqueness. To check what you have, `\d books` in `psql` lists each index with its method and columns, and `EXPLAIN` on the query shows whether the planner chose an Index Scan, an Index Only Scan or a Bitmap Index Scan on it. If a query you expected to be indexed still shows a sequential scan, compare its `WHERE` clause against the table above before assuming the index is broken: the usual cause is a wrapped column, a `<>` test or a sort order the index does not store.

  • Why would you create an index as (published_on DESC NULLS LAST) when a backward scan of the default index already exists?
    A backward scan of the default ascending, nulls-last index produces `DESC NULLS FIRST`. A listing written `ORDER BY published_on DESC NULLS LAST`, newest first with undated pre-orders at the end, matches neither scan direction, so the planner sorts. An index declared `DESC NULLS LAST` matches that order with a forward scan and removes the Sort node.
  • Can a B-tree index make SELECT max(price) FROM books fast in PostgreSQL?
    Yes. The planner rewrites `min()` and `max()` over an indexed column into a subquery of the form `SELECT price FROM books WHERE price IS NOT NULL ORDER BY price DESC LIMIT 1`, which reads a single entry from one end of the index instead of aggregating every row. It applies only when the query has no other aggregates that would force a full scan.

  • PostgreSQL builds a hash index when CREATE INDEX has no USING clause.
  • A FOREIGN KEY constraint automatically indexes the referencing columns.
  • A B-tree cannot serve WHERE col IS NULL because NULLs are not stored in the index.
  • An ascending index cannot help ORDER BY col DESC; a second DESC index is always needed.
  • An index on title also serves WHERE lower(title) = 'dune'.
open as a page

In PostgreSQL, what does EXPLAIN ANALYZE do to an UPDATE or DELETE, and how do you measure one without changing your data?

level: juniorimportance: must knowfreq 64%

basics

~20 s

EXPLAIN ANALYZE really executes an UPDATE or DELETE and only discards its output, so in autocommit the change commits. Wrap it in BEGIN and ROLLBACK: the data change is undone, but the locks, WAL and any sequence values drawn are not.

open as a page

In PostgreSQL, beyond the default B-tree, which index access methods ship in core, and what kind of query is each one built for?

level: juniorimportance: must knowfreq 74%

basics

~10 s

Core PostgreSQL adds hash (equality only), GIN (values holding many keys: arrays, jsonb, tsvector), GiST and SP-GiST (geometry, ranges and nearest-neighbour ordering with <->) and BRIN (tiny per-block-range summaries for huge, physically ordered tables).

open as a page

In PostgreSQL, what does the ANALYZE command do, how does it differ from VACUUM and EXPLAIN ANALYZE, and when should you run it by hand?

level: juniorimportance: must knowfreq 66%

basics

~20 s

ANALYZE samples a table and rewrites its planner statistics in pg_statistic (shown by pg_stats). Unlike VACUUM it reclaims no space; unlike EXPLAIN ANALYZE it runs no query. Run it by hand after bulk loads, on temporary tables and after changing statistics settings.

open as a page

In a PostgreSQL database with an en_US.UTF-8 collation, why does title LIKE 'Dune%' skip the B-tree index on title, and what fixes it?

level: middleimportance: must knowfreq 60%

basics

~10 s

PostgreSQL turns an anchored LIKE prefix into a byte-wise range, which only fits an index ordered byte by byte. Under en_US.UTF-8 the default index sorts linguistically, so build it with text_pattern_ops or COLLATE "C".

open as a page

In PostgreSQL EXPLAIN ANALYZE, what do a node's cost, rows, width, actual time and loops mean, and how do you get its total time?

level: middleimportance: must knowfreq 74%

basics

~20 s

The first group is the planner's estimate: startup..total cost in arbitrary units, rows per execution and average row width in bytes. The second is measured: per-execution average milliseconds and rows, plus loops; multiply by loops for totals.

open as a page

In PostgreSQL, what does random_page_cost model, what is its default, and why do teams on SSD storage often lower it?

level: middleimportance: must knowfreq 60%

basics

~20 s

random_page_cost is the planner's cost of fetching a page out of order, default 4.0 against seq_page_cost 1.0. On SSDs random reads cost little more than sequential ones, so lowering it lets index scans win where they really are cheaper.

open as a page

In PostgreSQL, why use CREATE INDEX CONCURRENTLY on a busy 400 GB orders table, and what does it cost versus a plain CREATE INDEX?

level: middleimportance: must knowfreq 72%

basics

~20 s

Plain CREATE INDEX blocks every INSERT, UPDATE and DELETE on the table until it finishes; CONCURRENTLY keeps writes flowing but pays with two table scans, waits on older transactions, a longer build and an INVALID leftover if it fails.

open as a page

In PostgreSQL 18, a delivery_addresses query filtering city = 'Leeds' AND postal_code = 'LS6' estimates 1,200 rows, returns 40,000 and picks a nested loop; how do extended statistics fix it?

level: seniorimportance: must knowfreq 55%

basics

~20 s

The planner multiplies the two columns' selectivities as if they were independent, so the estimate is too low by exactly the city's share. CREATE STATISTICS (dependencies, mcv) ON city, postal_code FROM delivery_addresses, followed by ANALYZE, shows it that postal_code implies city.

open as a page

A PostgreSQL CREATE UNIQUE INDEX CONCURRENTLY on a 400 GB orders table failed with a duplicate-key error; what state is left behind, and how do you recover?

level: seniorimportance: must knowfreq 58%

basics

~20 s

The index stays in the catalog marked INVALID, and queries ignore it. If it failed after its first build pass, writes still maintain it and it still rejects duplicates. Resolve the duplicates, DROP INDEX CONCURRENTLY it, then rebuild.

open as a page

In PostgreSQL, what does SET enable_seqscan = off actually do, and why is it a diagnostic test rather than a fix?

level: juniorimportance: should knowfreq 52%

basics

~20 s

It makes the planner avoid sequential scans unless no other path exists, exposing the plan it rejected so you can time it. A faster forced plan proves the estimates or cost settings are wrong; fix those, not the switch.

open as a page

In PostgreSQL, which view shows how often each index on the orders table has been used, and how do you read it?

level: juniorimportance: should knowfreq 50%

basics

~20 s

pg_stat_user_indexes has one row per index: idx_scan counts index scans, last_idx_scan dates the latest, and idx_tup_read and idx_tup_fetch count entries read and table rows fetched. An idx_scan stuck at 0 since the last reset marks a candidate for removal.

open as a page

In PostgreSQL, why does CREATE INDEX ON book_orders ((ordered_at::date)) fail when ordered_at is timestamptz, and how do you index the order day safely?

level: middleimportance: should knowfreq 42%

basics

~10 s

Casting timestamptz to date depends on the session's TimeZone, so it is STABLE, and index expressions must be IMMUTABLE. Index (ordered_at AT TIME ZONE 'UTC')::date instead, and query with exactly that expression.

open as a page

In PostgreSQL 15 and later, what does NULLS NOT DISTINCT change about a unique constraint such as UNIQUE (publisher_id, edition_code), and what must you check before adding it to a live table?

level: middleimportance: should knowfreq 38%

basics

~20 s

Unique indexes treat NULLs as distinct by default, so a row with a NULL key column never conflicts. Declaring the constraint or index NULLS NOT DISTINCT (PostgreSQL 15+) makes NULLs compare equal, allowing one (publisher_id, NULL) row per publisher.

open as a page

In PostgreSQL 18 EXPLAIN ANALYZE, what do the Buffers counters shared hit, read, dirtied, written and temp mean, and how do you use them?

level: middleimportance: should knowfreq 56%

basics

~20 s

Buffers counts pages per node: shared hit was in shared_buffers, read was fetched from the OS or storage, dirtied was modified, written was evicted by this backend, temp is spilled work files. PostgreSQL 18 shows them with ANALYZE by default.

open as a page

In PostgreSQL, how do the GIN operator classes jsonb_ops and jsonb_path_ops differ, and when would you choose jsonb_path_ops?

level: middleimportance: should knowfreq 52%

basics

~20 s

jsonb_ops, the default, indexes every key and value separately and serves ?, ?|, ?&, @>, @? and @@. jsonb_path_ops stores one hash per value including its key path, serves only @>, @? and @@, and is usually smaller and more selective.

open as a page

In PostgreSQL, which settings decide how many parallel workers a query plans and actually gets, and why can it get fewer?

level: middleimportance: should knowfreq 45%

basics

~10 s

max_parallel_workers_per_gather (default 2) caps workers per Gather node and table size sets the planned count; max_parallel_workers and max_worker_processes (8 each) cap the shared pool, so a busy server launches fewer or none.

open as a page

In PostgreSQL, how does the planner turn a column's pg_stats row (null_frac, n_distinct, most_common_vals, most_common_freqs) into a row estimate for an equality filter?

level: middleimportance: should knowfreq 48%

basics

~20 s

If the constant is in most_common_vals, selectivity is its most_common_freqs entry. Otherwise the planner takes 1 minus the MCV frequencies minus null_frac, divides by the remaining distinct values (n_distinct minus the MCV count), and multiplies by the table's estimated rows.

open as a page

How large a sample does PostgreSQL's ANALYZE take, and what do default_statistics_target and ALTER TABLE ... SET STATISTICS actually control?

level: middleimportance: should knowfreq 42%

basics

~20 s

ANALYZE samples 300 × the statistics target rows: 30,000 at the default target of 100, whatever the table's size. The target also caps each column's most-common-value list and histogram buckets; SET STATISTICS overrides it per column, up to 10000.

open as a page

In PostgreSQL 18, how can a B-tree index on books(category_id, price) serve WHERE price < 5 with no condition on category_id, and when won't it?

level: seniorimportance: should knowfreq 46%

basics

~20 s

PostgreSQL 18's skip scan treats the query as category_id = N AND price < 5 for each distinct category, repositioning once per value. It pays off when the leading column has few distinct values; with many, other plans win.

open as a page

A PostgreSQL dashboard query takes 900 ms from the app, but EXPLAIN ANALYZE reports Execution Time: 40 ms; which EXPLAIN options help explain the gap?

level: seniorimportance: should knowfreq 34%

basics

~20 s

EXPLAIN ANALYZE discards rows before converting them for the client and never sends them, so detoasting, output functions and network time stay invisible. SERIALIZE measures the conversion, SETTINGS exposes session differences, and the app's prepared-statement plan may differ.

open as a page

On a PostgreSQL append-only readings table of billions of rows, when does a BRIN index on observed_at beat a B-tree, and what does pages_per_range trade?

level: seniorimportance: should knowfreq 47%

basics

~20 s

BRIN wins when observed_at rises with physical position: each block range's min/max summary excludes most of the table at a tiny index size. pages_per_range (default 128 blocks) trades index size against heap pages rechecked per matching range.

open as a page

In PostgreSQL, a GIN index on readings.payload keeps most inserts fast, but a few stall for seconds; what is the GIN pending list, and how do you tame it?

level: seniorimportance: should knowfreq 36%

basics

~20 s

With fastupdate on, the default, GIN appends new entries to an unsorted pending list and merges them in bulk later. The insert that pushes the list past gin_pending_list_limit (4MB) does the whole merge itself and stalls; searches also scan the list.

open as a page

In a PostgreSQL-backed loyalty-points service, a prepared statement is fast for five executions and then turns slow; what changed, and how do you fix it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

After five custom-planned executions, PostgreSQL builds a generic plan and keeps it if its estimated cost is below the custom average. On skewed data that estimate misleads; force_custom_plan, scoped to that role or function, or better statistics fix it.

open as a page

In PostgreSQL 18, why can JIT compilation make a short query slower, and which settings decide whether a query is JIT-compiled?

level: seniorimportance: should knowfreq 34%

basics

~20 s

JIT starts when a plan's estimated total cost exceeds jit_above_cost (100000), with inlining and optimization above 500000. A short query the planner overestimates pays compile time that can exceed its runtime; jit = off or higher thresholds fix it.

open as a page

In PostgreSQL, how do you confirm a B-tree index on the orders table is bloated, and how does REINDEX CONCURRENTLY rebuild it without blocking writes?

level: seniorimportance: should knowfreq 44%

basics

~20 s

Run pgstattuple's pgstatindex(): a freshly built B-tree shows avg_leaf_density around its 90 fillfactor, so a much lower figure on a large index means bloat. REINDEX INDEX CONCURRENTLY builds a _ccnew copy alongside, swaps it in, then drops the old one.

open as a page

A PostgreSQL CREATE INDEX CONCURRENTLY on the 400 GB orders table has run for an hour; how do you see its phase and what it is waiting for?

level: seniorimportance: should knowfreq 34%

basics

~10 s

Query pg_stat_progress_create_index: phase names the step, blocks_done against blocks_total shows scan progress, and in the waiting phases current_locker_pid names the transaction it waits on, often an idle-in-transaction session holding an old snapshot.

open as a page

In PostgreSQL, how does a GiST or SP-GiST index answer ORDER BY location <-> point '(10,20)' LIMIT 3, and how do those two methods differ?

level: middleimportance: nice to knowfreq 28%

basics

~20 s

GiST and SP-GiST both list <-> as an ordering operator for points, so an index scan returns rows nearest-first and stops at the LIMIT. GiST is a balanced, multicolumn tree of overlapping bounding keys; SP-GiST builds single-column, non-overlapping quad-trees, k-d trees or tries.

open as a page

In PostgreSQL 18, which index types can be built with parallel workers, and what decides how many workers a CREATE INDEX actually gets?

level: middleimportance: nice to knowfreq 24%

basics

~20 s

In PostgreSQL 18, B-tree, BRIN and GIN builds can use parallel workers. A size-based cost model, or the table's parallel_workers setting, picks the count, capped by max_parallel_maintenance_workers (default 2), the free worker pool and 32MB of maintenance_work_mem per participant.

open as a page

showing 1–30 of 33