Why isn't my Postgres index being used?

Question

I’m using Postgres 9.5. I want to create indexes that will make queries like

select * FROM my_object_times where name like ‘Dave %’;

and

select * FROM my_object_times where name like '% LastName';

run quickly. So I created ...

CREATE INDEX my_object_times_name_idx ON my_object_times (name text_pattern_ops);
CREATE INDEX my_object_times_rev_name_idx ON my_object_times (reverse(name) text_pattern_ops); 

This works fine for the first case, in which I can see my index being used, but in the second case, "Explain" does not show my index being utilized ...

my_db=> explain select * FROM my_object_times where name like '% LastName';
 QUERY PLAN 
------------------------------------------------------------------
 Seq Scan on my_object_times (cost=0.00..136594.69 rows=51 width=221)
 Filter: ((name)::text ~~ '% LastName'::text)

How do I get my index to be in used in the second case?

Answer 1

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,

1. Two text_pattern_ops will never support '%foo%'
2. 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.
3. Requires the user to know something of the index-implementation.
4. 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.

• Dave: I'll tell you how its going -- I already have a trgm index but that takes up a TON of disk space and since my queries are not of the form like "%something%", I figured I'd tried a more space-efficient approach. Why do you say "However, that's a horrible idea."?
• Evan Carroll: @Dave updated with information.
• Dave: Thanks for this clarification. One last question about point #2 -- what do you mean "reverse() of all conditions"?
• Evan Carroll: @Dave if you could get back to us with what you find works. I'd love to see the size of the GIST index with the two text_pattern_ops, \di+ table_name*
• Dave: Posterity may want to note for #4, in my table with 39M rows, the trgm index took up 13.6 GB of space but with the two other indexes it only takes up 2.4 GB. So since points #1, #2, and #3 don't apply to me, I've found a winner.