Reverse string with leading wildcard scan in Postgres

Question

SQL Server: Index columns used in like?

I've tried using the query method in the link above with Postgres (0.3ms improvement), it seems to only work with MySQL (10x faster).

MYSQL

User Load (0.4ms)  SELECT * FROM users WHERE reverse_name LIKE REVERSE('%Anderson PhD')
User Load (5.8ms)  SELECT * FROM users WHERE name LIKE ('%Anderson Phd')

POSTGRES

User Load (2.1ms)  SELECT * FROM users WHERE reverse_name LIKE REVERSE('%Scot Monahan')
User Load (2.5ms)  SELECT * FROM users WHERE name LIKE '%Scot Monahan'

Did some googling but couldn't quite understand as I'm quite new to DBs. Could anyone explain why this is happening?

Answer 1

To support a prefix match in Postgres for character type columns you either need an index with a suitable operator class: text_pattern_ops for text etc. (Unless you work with the "C" locale ...)

Or you use a trigram index to support any pattern - then you don't need the "reverse" trick any more.

See:

• PostgreSQL LIKE query performance variations

You'll see massive performance improvement for tables of none-trivial size, as soon as an index can be used for the query. Have a look at the query plan with EXPLAIN.