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

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?

Answer

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.