skip to content

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%

answer

  1. a prefix match becomes a range
  2. linguistic order versus byte order
  3. an operator class that compares bytes
  4. the index's collation, not the query's
  5. ordinary < and > still need the default

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

solid answer

~50 s

For `title LIKE 'Dune%'` the planner derives a range, everything from `'Dune'` up to but not including `'Dunf'`, and rechecks the `LIKE` on each match. That range is only correct if the index orders strings byte by byte. A database created with `en_US.UTF-8` gives `title` a linguistic collation, so the default B-tree on `title` follows locale rules and the planner cannot turn the prefix into an index condition on it. Fix it with `CREATE INDEX ... (title text_pattern_ops)`, whose plan shows `~>=~` and `~<~`, or with an index on `(title COLLATE "C")`, which uses ordinary `>=` and `<`; the planner checks the index's collation, not the query's. A pattern-ops index serves `LIKE`, `~ '^Dune'` and `=`, but not `<`, `>` or `ORDER BY title`, so keep the default index if those matter. A leading wildcard needs a trigram GIN index instead.

code

sql · 14 lines
sql
-- database default collation: en_US.UTF-8
CREATE INDEX books_title_idx ON books (title);                         -- =, <, >, ORDER BY title
CREATE INDEX books_title_prefix_idx ON books (title text_pattern_ops); -- LIKE 'x%'

EXPLAIN SELECT book_id, title FROM books WHERE title LIKE 'Dune%';
-- the index condition becomes:
--   (title ~>=~ 'Dune'::text) AND (title ~<~ 'Dunf'::text)

-- alternative: one index stored in byte order
CREATE INDEX books_title_c_idx ON books (title COLLATE "C");

-- case-insensitive prefix search
CREATE INDEX books_title_lower_idx ON books (lower(title) text_pattern_ops);
SELECT book_id FROM books WHERE lower(title) LIKE 'dune%';

go deeper

for a junior

Recall that only patterns anchored at the start can use a B-tree, and that text_pattern_ops exists for prefix LIKE in databases that do not use the C locale.

for a middle

Explain the rewrite into a byte-wise range, why a linguistic collation breaks it, and both fixes, including what a pattern-ops index can no longer serve.

for a senior

Decide per column between a second pattern-ops index and a C-collated one, handle ILIKE with an expression index, and check generic plans for parameterised prefixes.

for a principal

Weigh the database's default collation against per-column byte-order indexes: user-facing sort order, index count on hot tables, and when prefix search should move to trigram or full-text search.

## How PostgreSQL indexes a LIKE prefix `LIKE` is not one of the five B-tree comparison operators, so a B-tree cannot evaluate it directly. What the planner can do is extract the **fixed prefix** of a pattern that is anchored at the start, such as `'Dune%'`, and turn it into a range: 1. Everything at or above the prefix: `title >= 'Dune'`. 2. Everything below the next possible prefix: `title < 'Dunf'` (the last character incremented). 3. The original `LIKE` is kept as a recheck, because the range is only a necessary condition. The pattern must be a **constant** the planner can see: a pattern that starts with a wildcard has no prefix, and a `$1` parameter in a generic prepared-statement plan cannot be turned into a range at plan time. A pattern with no wildcard at all, such as `LIKE 'Dune'`, is simply equality and can use any index on the column whose collation is deterministic. ## Why the default index fails under a linguistic collation The range in step 1 and 2 is computed **byte by byte**. It is only valid if the index stores strings in that same byte order. A database created with `en_US.UTF-8` gives every text column that collation by default, and a linguistic collation compares strings in several passes (letters first, then accents, case and punctuation), so strings sharing a byte prefix are not guaranteed to sit in one contiguous stretch of the index. PostgreSQL's planner therefore refuses to build the range against an index whose collation is not `C`. The query falls back to a sequential scan, or to a full index scan if ordering makes that worthwhile. The check looks at the **index's** collation, not the expression's. Writing `title COLLATE "C" LIKE 'Dune%'` in the query does not make an `en_US` index usable. ## Fix 1: a pattern operator class The operator classes `text_pattern_ops`, `varchar_pattern_ops` and `bpchar_pattern_ops` order `text`, `varchar` and `char` values strictly character by character, ignoring the locale. An index built with one of them supports the prefix range, and its plan shows the class's own operators: - `Index Cond: ((title ~>=~ 'Dune'::text) AND (title ~<~ 'Dunf'::text))` - `Filter: (title ~~ 'Dune%'::text)`, the `LIKE` recheck The catch is that a pattern-ops index **cannot** serve ordinary `<`, `<=`, `>`, `>=` or `ORDER BY title`, because those use the locale-aware operators. Equality still works. If the application also sorts or range-filters titles, it needs a second index with the default operator class; two indexes on the same column with different operator classes are allowed. ## Fix 2: an index with the C collation `CREATE INDEX books_title_c_idx ON books (title COLLATE "C")` stores titles in byte order using the default operator class. It serves the prefix range with ordinary `>=` and `<` operators, and it also serves `ORDER BY title COLLATE "C"`, but not `ORDER BY title` under the column's `en_US` collation. Choose between the two fixes by which extra query shapes you want the one index to cover; both cost the same write overhead as any extra B-tree. If the whole database uses the `C` locale, none of this is needed: a default index already supports `LIKE` prefixes, at the price of byte-order sorting everywhere. ## What neither fix covers | Predicate | What serves it | |---|---| | `title LIKE 'Dune%'` | `text_pattern_ops` index, or index with `COLLATE "C"` | | `title ~ '^Dune'` | Same as above: an anchored regular expression | | `title LIKE 'Dune'` (no wildcard) | Any index on `title` with a deterministic collation, via equality | | `title ILIKE 'dune%'` | Only if the pattern starts with non-alphabetic characters; otherwise an expression index on `lower(title) text_pattern_ops` | | `title LIKE '%dune%'` | No B-tree; a trigram GIN index | | `title LIKE $1` in a generic plan | No prefix range; a custom plan with the value substituted can use one | Two more limits are worth knowing. With a **nondeterministic** collation, such as a case-insensitive ICU one, the planner builds no prefix range at all, because bytewise tests disagree with that collation's notion of equality. And the case-insensitive workaround only works if the query repeats the indexed expression exactly: `lower(title) LIKE 'dune%'`. ## Putting it together for the bookstore For a catalogue search box that matches titles by prefix, a typical design is the default index on `title` for sorting and exact lookups, plus one `text_pattern_ops` index for the search box. Check with `EXPLAIN` that the plan shows the `~>=~` and `~<~` index conditions; if it shows a `Filter` on a sequential scan instead, the index's collation or operator class is wrong for the pattern.

  • Why not create the whole database with the C locale instead?
    It works, since under `C` a default B-tree already serves `LIKE` prefixes, but every text `ORDER BY` then sorts by byte value: 'Zebra' sorts before 'apple' and accented letters land after 'z'. Many teams keep a linguistic default for user-facing sorting and add a pattern-ops or `COLLATE "C"` index only on the columns searched by prefix.
  • How do you make a case-insensitive prefix search such as ILIKE 'dune%' use an index?
    A B-tree serves `ILIKE` only when the pattern begins with non-alphabetic characters. Index the folded expression instead, `CREATE INDEX ON books (lower(title) text_pattern_ops)`, and write the query as `lower(title) LIKE 'dune%'`, repeating the indexed expression exactly so the planner can match it.
  • Does a prepared statement with title LIKE $1 use the pattern-ops index?
    Only in a custom plan. The planner builds the prefix range only from a constant pattern. A custom plan substitutes the parameter's value and can use the index; a generic plan sees a parameter and cannot. If the statement switches to a generic plan, prefix searches can fall back to a sequential scan.

  • LIKE can never use a B-tree index in PostgreSQL; you always need GIN.
  • A text_pattern_ops index also serves ORDER BY title and range comparisons.
  • Adding COLLATE "C" to the query, not the index, makes the default index usable for LIKE.
  • A leading-wildcard pattern such as '%dune' can use a text_pattern_ops index.
  • The default operator class fails LIKE because it does not support equality.