LIKE on PostgreSQL - how to use an index

Using Indexes on LIKE queries on PostgreSQL might be tricky.

First, starting your search with '%' will always lead to full table scan.
OK, this is easy to understand and there is a recommended way to fix it - fulltextsearch.

Second, context-independent search ILIKE will also use table scan instead of index.

But if database is initialized with utf8 you will also get painfully slow table scan, until create an index, with an option, designed for this particular purpose: text_pattern_ops

Here is an example:
CREATE INDEX  ON MyTable (myField text_pattern_ops ASC NULLS LAST);

In non-C locales (which you're almost certainly using if you're using

UTF8) the ordering which the normal text operations use can be quite complex.

Just as an example most locales have spaces being entirely insignificant. 

So no range can reliably match a prefix LIKE pattern. The text_pattern_ops use

simple character-by-character ordering which are useful for LIKE but not for

regular < and > comparisons. They're just two different orderings.

Some sources also recommend indexes with  lower option:

CREATE INDEX  ON MyTable (lower(myField) text_pattern_ops ASC NULLS LAST);

For me, it did not work with postgresql 9.0. Just stay with regular text_pattern_ops.