Does index on string column impact on where clause when providing only part of string?

Question

Let's start with example:

select title from books where name ilike '% of the rings';

If i create index on column title it will upgrade my performance or only if full correct string is provided in query?

Does this answer your question? [What makes a SQL statement sargable?](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) — Andronicus, Jan 19 '20 at 14:47
No, it won't. The search pattern should not start with a wildcard. — The Impaler, Jan 19 '20 at 14:48

score 3 · Accepted Answer · answered Jan 19 '20 at 14:48

For a standard index, Postgres should be able to use the index when the like pattern starts with a constant. That would be:

where name like 'Lord of the %'

A standard index is not used when the pattern starts with a wildcard, as explained in the documentation:

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

As a general rule, though, this does not extend to ilike (although there are exceptions explained in the documentation).

Postgres offers other indexes (such a GIN indexes and trigram indexes) that can be used to speed such queries.

Does index on string column impact on where clause when providing only part of string?

1 Answers1