Possibly the fast ones are anchored patterns with case-sensitive like that can use indexes. i.e. there is no wild card at the beginning of the match string so the executor can use an index range scan. Lower and ilike will also lose your ability to use the index unless you specifically create an index for that purpose.

If you want to search for string in the middle of the field, you should look into full text or trigram indexes. First of them is in Postgres core, the other is available in the contrib modules.

Possibly the fast ones are anchored patterns with case-sensitive like that can use indexes. i.e. there is no wild card at the beginning of the match string so the executor can use an index range scan. (the relevant comment in the docs is here) Lower and ilike will also lose your ability to use the index unless you specifically create an index for that purpose (see functional indexes).

If you want to search for string in the middle of the field, you should look into full text or trigram indexes. First of them is in Postgres core, the other is available in the contrib modules.