skip to content

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'.