PostgreSQL LIKE and ILIKE: why `'abc%'` is not the same as `'%abc%'`

PostgreSQL LIKE and ILIKE: why 'abc%' and '%abc%' behave differently#

Suppose you have a users table with several million rows and a username column.

At first, the requirement is simple: find users whose name starts with "bodan".

SELECT *
FROM users
WHERE username LIKE 'bodan%';

With the right index, the query performs well.

Later, the requirement changes. Now "bodan" can appear anywhere in the username:

SELECT *
FROM users
WHERE username LIKE '%bodan%';

Same table. Same search term. Very different access pattern.

It is tempting to conclude that LIKE is simply slow.

A better question is:

what part of the pattern can PostgreSQL use to reduce the search space before it starts checking values?

That distinction explains much of the performance behavior around LIKE, ILIKE, B-tree indexes and pg_trgm.

How PostgreSQL can use a B-tree with LIKE#

A B-tree index keeps its keys ordered.

That makes range access possible without scanning every stored value. Prefix searches can benefit because there is a fixed part of the pattern before the first wildcard.

For example:

SELECT *
FROM users
WHERE username LIKE 'bodan%';

Conceptually, PostgreSQL may be able to restrict the scan to the part of the index where values beginning with bodan can exist.

There is an important condition: efficient B-tree pattern matching also depends on the column collation and the index operator class.

With a compatible collation such as C, a normal B-tree may be sufficient for prefix matching.

With other locale configurations, a pattern-specific operator class may be required.

For a text column:

CREATE INDEX idx_users_username_pattern
ON users (username text_pattern_ops);

For a varchar column:

CREATE INDEX idx_users_username_pattern
ON users (username varchar_pattern_ops);

The idea remains the same: PostgreSQL needs a fixed prefix that can be translated into a useful range inside the index.

This should not be interpreted as:

LIKE 'text%' always uses a B-tree.

A better statement is:

a left-anchored pattern can provide a B-tree-usable range when the collation and operator class support that access.

The fixed prefix ends at the first wildcard#

LIKE mainly uses two wildcard characters:

• % matches any sequence of characters;
• _ matches a single character.

PostgreSQL can use the fixed portion before the first wildcard.

For example:

WHERE username LIKE 'bodan%'

has a clear fixed prefix:

bodan

Now consider:

WHERE username LIKE 'bodan%t'

The useful prefix is still bodan.

The final t can be checked after candidate rows are found, but it does not create one continuous B-tree range containing only values that both start with bodan and end with t.

The same applies to _:

WHERE username LIKE 'bod_n%'

The fixed prefix ends before _.

The practical idea is:

the more useful fixed text exists at the beginning of the pattern, the more opportunity an ordered index has to reduce the search space.

Why LIKE '%bodan%' is a different problem#

Now consider:

SELECT *
FROM users
WHERE username LIKE '%bodan%';

The first character is already a wildcard.

There is no fixed prefix such as:

bodan...

that gives PostgreSQL an obvious entry point into an index ordered by the full username value.

The string may contain bodan at the beginning, in the middle, or near the end.

That is why a conventional B-tree loses much of its usefulness for this pattern.

PostgreSQL may end up inspecting a large number of entries or choosing a Seq Scan and applying the pattern as a filter.

This is the central distinction.

It is not:

fast LIKE versus slow LIKE.

It is:

a pattern with a usable ordered prefix versus a pattern without a useful B-tree entry point.

ILIKE adds case-insensitive matching#

ILIKE performs case-insensitive pattern matching:

SELECT *
FROM users
WHERE username ILIKE 'bodan%';

You should not assume this can use a normal B-tree in exactly the same way as:

LIKE 'bodan%'

A common approach for case-insensitive prefix matching is to normalize the indexed expression.

For example:

CREATE INDEX idx_users_lower_username
ON users (LOWER(username));

and then query the same expression:

SELECT *
FROM users
WHERE LOWER(username) LIKE 'bodan%';

Now the index expression and the query expression match.

If the collation requires a pattern-specific operator class, make that explicit.

For a text expression:

CREATE INDEX idx_users_lower_username_pattern
ON users ((LOWER(username)) text_pattern_ops);

The double parentheses make it explicit that LOWER(username) is the indexed expression and text_pattern_ops is the operator class applied to it.

The important lesson is not to memorize this exact index.

It is to understand that the indexed expression and the query expression need to be compatible with the access path you expect PostgreSQL to consider.

Expression indexes also have a cost. PostgreSQL has to maintain them when username changes, and queries need to use a compatible expression to benefit from them.

How to inspect the real plan with EXPLAIN ANALYZE#

You should not decide whether a pattern is properly indexed by looking at the SQL alone.

Look at the execution plan.

The following plans and timings are hypothetical and illustrative. They show how the type of work can change, not a benchmark you should expect to reproduce on every server. Actual behavior depends on hardware, data distribution, selectivity, statistics, cache state, collation and configuration.

Suppose users contains one million rows and has an index correctly prepared for prefix matching.

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE username LIKE 'bodan%';

A simplified plan might look like:

Index Scan using idx_users_username_pattern on users
  Index Cond: (range compatible with prefix 'bodan')
  Filter: (username ~~ 'bodan%'::text)
  Buffers: shared hit=3

Execution Time: 0.040 ms

The important part is not 0.040 ms.

It is that PostgreSQL has an index condition that narrows the portion of the index it needs to examine.

The exact Index Cond representation can vary by PostgreSQL version, collation and operator class.

Now change only the pattern:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE username LIKE '%bodan%';

A possible plan is:

Seq Scan on users
  Filter: (username ~~ '%bodan%'::text)
  Rows Removed by Filter: 999999
  Buffers: shared hit=4425

Execution Time: 45.705 ms

Again, the timing is illustrative.

The useful information is:

Rows Removed by Filter: 999999

PostgreSQL had to inspect a very large number of candidate rows to find the result.

That is the cost we care about.

pg_trgm for searches anywhere in the string#

If the actual requirement is:

WHERE username LIKE '%bodan%'

a B-tree is no longer a good match for the access pattern.

This is where pg_trgm provides another strategy.

pg_trgm is an official PostgreSQL extension that represents strings using trigrams: groups of three characters.

For a value such as:

bodan

useful fragments include:

bod
oda
dan

This makes it possible to index pieces of the string instead of relying only on the beginning of the complete value.

First enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

The extension must be available in the PostgreSQL installation, and the user running the command needs the required privileges.

Then create a GIN trigram index:

CREATE INDEX idx_users_username_trgm
ON users
USING GIN (username gin_trgm_ops);

Now inspect:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE username LIKE '%bodan%';

A hypothetical plan might look like:

Bitmap Heap Scan on users
  Recheck Cond: (username ~~ '%bodan%'::text)

  -> Bitmap Index Scan on idx_users_username_trgm
       Index Cond: (username ~~ '%bodan%'::text)

Execution Time: 0.150 ms

The important change is the strategy.

Instead of:

Seq Scan

PostgreSQL may be able to use:

Bitmap Index Scan

The trigram index can produce a much smaller candidate set before the full pattern is rechecked.

Again, the goal is not to obtain exactly this plan node or timing.

The goal is to reduce how many values PostgreSQL needs to inspect.

pg_trgm can also help with ILIKE#

pg_trgm operator classes support both LIKE and ILIKE.

For example:

SELECT *
FROM users
WHERE username ILIKE '%bodan%';

can potentially benefit from a trigram index without requiring the pattern to be anchored at the beginning.

That makes pg_trgm particularly useful when the real requirement is:

find a character sequence anywhere in a string without case sensitivity.

But that does not mean it belongs on every text column.

If the application only needs:

LIKE 'bodan%'

a properly configured B-tree may be simpler.

The index structure should match the actual query pattern.

The limit of pg_trgm: the pattern needs to contain useful information#

A trigram index cannot create selectivity that does not exist.

Consider:

WHERE username LIKE '%bodan%'

The pattern contains multiple fragments that can help eliminate candidates.

Now compare it with:

WHERE username LIKE '%a%'

The second pattern contains much less useful information.

Very short patterns may provide few or no useful trigrams for narrowing the search. When PostgreSQL cannot extract useful trigrams, an indexed search can degrade into a very broad scan.

The practical rule should not be:

three characters means fast.

A better rule is:

the more useful and selective trigrams the pattern provides, the more opportunity the index has to eliminate candidates before checking complete values.

GIN or GiST with pg_trgm#

pg_trgm provides operator classes for both GIN and GiST indexes.

Both can accelerate trigram-based searches, but they are not interchangeable structures.

For workloads centered mainly on LIKE and ILIKE searches, a GIN index is often a reasonable option to evaluate:

CREATE INDEX idx_users_username_trgm
ON users
USING GIN (username gin_trgm_ops);

GiST has different properties and can also be useful for similarity-related operations, including cases involving distance-based searches.

Instead of adopting a universal rule such as:

GIN is always faster.

evaluate the choice against the actual workload:

• search frequency;
• write frequency;
• index size;
• operators being used;
• similarity-search requirements;
• actual text distribution.

The right index depends on the problem you need to solve.

Selectivity still matters with pg_trgm#

The presence of a trigram index does not force PostgreSQL to use it.

Suppose:

WHERE username LIKE '%bodan%'

matches a very large percentage of the table.

The index may produce so many candidate rows that the planner considers a sequential scan cheaper.

A rare pattern such as:

WHERE username LIKE '%xyz123%'

may narrow the candidate set much more aggressively and make index access far more attractive.

The same question we asked for B-tree applies again:

how much work does the index actually remove?

The existence of the index is not enough.

Pattern selectivity still matters.

The cost of maintaining a trigram index#

A pg_trgm index is an additional structure that PostgreSQL has to store and maintain.

That creates costs:

• additional storage;
• additional work during INSERT;
• additional work when the indexed column changes;
• maintenance overhead;
• more schema complexity.

There is no universal size for a trigram index.

It depends on the number of rows, text length, text distribution and other workload characteristics.

That is why it does not make sense to create one simply because the application uses LIKE.

Before adding one, ask:

• Do searches actually use leading wildcards?
• How often do those searches run?
• Are the patterns usually selective?
• Is the table write-heavy?
• Would prefix matching actually satisfy the product requirement?

If the product only needs:

LIKE 'abc%'

a properly configured B-tree can be the simpler solution.

If the real requirement is:

LIKE '%abc%'

or:

ILIKE '%abc%'

then pg_trgm starts solving a different problem.

What to check when LIKE or ILIKE is slow#

When a text-search query starts degrading, this process is more useful than adding indexes blindly:

1. Inspect the actual pattern. Does it begin with fixed text or with %/_?
2. Run EXPLAIN (ANALYZE, BUFFERS).
3. Check the scan type.
4. Compare rows processed with rows discarded.
5. Check the column collation and index operator class.
6. For prefix searches, evaluate B-tree with text_pattern_ops or varchar_pattern_ops when appropriate.
7. For case-insensitive prefix searches, evaluate an expression index compatible with LOWER().
8. For leading-wildcard or anywhere-in-string searches, evaluate pg_trgm.
9. Measure pattern selectivity.
10. Compare the new plan with the old one before considering the optimization complete.

The goal is not to force PostgreSQL to use an index.

The goal is to choose an access path that removes a meaningful amount of work.

Conclusion#

LIKE 'abc%' and LIKE '%abc%' look like small variations of the same query, but they create different access problems.

When a fixed prefix exists, a correctly configured B-tree can use its ordering to reduce the portion of the index PostgreSQL needs to inspect.

When the pattern starts with a wildcard, that entry point disappears. If the application genuinely needs substring search, pg_trgm provides a structure better suited to that workload because it indexes fragments of the text instead of relying only on the beginning of the full value.

ILIKE, collation, operator classes and selectivity add more conditions to the decision.

So the useful question is not:

which index makes LIKE fast?

It is:

what information does my pattern provide for eliminating data, and which index structure lets PostgreSQL use that information?

Optimization starts by understanding that difference.

For a broader diagnostic workflow, see our PostgreSQL query optimization guide, where we cover execution plans, planner statistics and index design in more depth.