Leading wildcard LIKE

Severity: Warning

A LIKE or ILIKE pattern that starts with % forces PostgreSQL to scan every row in the table. B-tree indexes work by matching from the left edge of a string, so a leading wildcard removes any anchor the index could use.

Example

SELECT * FROM products WHERE name LIKE '%widget%';

Even with an index on name, PostgreSQL must read every row and test the pattern against every value.

Why it happens

B-tree indexes store strings in sorted order. A query like WHERE name LIKE 'widget%' can efficiently narrow to the range of strings starting with “widget”. But '%widget%' could match anywhere in the string — “blue widget”, “widget pro”, “a widget” — so there’s no sorted range to seek to. PostgreSQL falls back to a sequential scan.

How to fix it

Option 1: Trigram index

The pg_trgm extension supports GIN or GiST indexes that can accelerate LIKE '%…%' patterns:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

With this index, WHERE name LIKE '%widget%' and WHERE name ILIKE '%widget%' both use the trigram index instead of scanning the whole table.

Option 2: Full-text search

For natural language search, PostgreSQL’s built-in full-text search is often a better fit:

ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;

CREATE INDEX idx_products_search ON products USING gin (search_vector);

-- Query using full-text search instead of LIKE
SELECT * FROM products WHERE search_vector @@ to_tsquery('english', 'widget');

Option 3: Remove the leading wildcard

If you only need prefix matching, drop the leading %:

-- Instead of LIKE '%widget%', if prefix matching is sufficient:
SELECT * FROM products WHERE name LIKE 'widget%';

This uses a standard B-tree index efficiently.

Detection

Query Doctor flags this nudge when it finds a LIKE or ILIKE expression where the pattern string starts with %. The nudge marker points to the pattern literal in the formatted SQL view.