Prefix LIKE no index can serve

Claim: Performance

A LIKE pattern with a fixed prefix, such as LIKE 'abc%', can be answered as a range of a B-tree index, but only when the index sorts in byte order. A plain index on a text column sorts by the column’s collation. Under en_US.utf8, an ICU locale or any other collation but C, that order is not byte order. The index cannot serve the match, and every call reads the whole table or the whole index.

Example

-- The database collation is en_US.utf8.
CREATE UNIQUE INDEX ON captures (source_message_id);

SELECT 1 FROM captures WHERE source_message_id LIKE $1 LIMIT 1;
-- called with $1 = '1234:%'

The unique index does not serve the LIKE. Postgres scans all of it and tests the pattern against every row.

Why it happens

Postgres serves col LIKE 'abc%' with the range col >= 'abc' AND col < 'abd'. That range holds exactly the matches only when strings sort byte by byte. A linguistic collation sorts by other rules: it can ignore punctuation or case at first, so strings that start with abc need not sit together in the index. Postgres uses the range only on an index whose order it knows to be byte order.

How to fix it

Create a B-tree index with the pattern operator class of the column’s type:

CREATE INDEX ON captures (source_message_id text_pattern_ops);

Column type	Operator class
text	text_pattern_ops
varchar(n)	varchar_pattern_ops
char(n)	bpchar_pattern_ops

A pattern index serves LIKE with a fixed prefix and equality. It does not serve <, > or ORDER BY on the column, so keep the plain index if another query needs those. An index on (col COLLATE "C") serves the prefix match too, and serves ranges and sorting in byte order.

A pattern that starts with % has no prefix, and no B-tree serves it. That case is Leading wildcard LIKE.

Detection

Query Doctor reports this Finding when a WHERE clause compares a text, varchar or char column with LIKE, and the pattern is a parameter or a literal with a fixed prefix and a wildcard after it. It needs the schema: it reports the Finding only when the column’s collation, its own or the database’s, is known and is not C, POSIX or a builtin-provider collation, and no index on the column can serve the match. A B-tree that leads with the column by a pattern operator class or a C collation can, as can a trigram or SP-GiST index on it. A partial index counts, whatever its predicate. ILIKE, NOT LIKE and a pattern with no wildcard are not reported. A parameter counts as a prefix pattern, because the SQL text does not say what value it is bound to. The Finding’s message names the collation and the CREATE INDEX statement, and its marker points to the column.