Why isn't my Postgres index being used?
Answer, because you're not reversing it in the query, and that's not implicit.

SELECT * FROM my_object_times where reverse(name) like reverse('% LastName');


However, that's a horrible idea. Instead delete both of those indexes. If you have to have non-anchored matching on something like "LastName" try the pgtrgm extension which will just work on LIKE and %. Tell us how it goes.

Install pg_trgm,

CREATE extention pg_trgm;


Create either a gist or gin index.

CREATE index test_trgm_gin ON test USING gin (some_text gin_trgm_ops);
CREATE index test_trgm_gist ON test USING gist (some_text gist_trgm_ops);


See this blog post on depesz for more information.

Why is two text_pattern_ops an inferior idea,


Two text_pattern_ops will never support '%foo%'
Not likely any library even supports this manual reverse() of all conditions.


What do I mean? Most ORM's support assembling a where clause from a hash. Here is an example for Sequalize,. {LastName: { $like: '% LastName' } } It's unlikely that this is going to ever generate, reverse(LastName) LIKE reverse('% LastName'). So the index simply won't be used unless you write it all manually: at best, that will obfuscate your use of a third party tool, at worst it'll be impossible or very difficult to make work. Where as the pg_trgm option just works.

Requires the user to know something of the index-implementation.
I'm not sure it's more space-efficient than GIST, or faster than either.


Also, as another note, if it just comes down to size you can always get another hard drive, set it up as a TABLESPACE and store the GIST index there. That doesn't solve the /space/ problem, but it should mitigate it.