1

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?

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.

  • 1
    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."? – Dave Nov 23 '16 at 20:56
  • @Dave updated with information. – Evan Carroll Nov 23 '16 at 21:00
  • Thanks for this clarification. One last question about point #2 -- what do you mean "reverse() of all conditions"? – Dave Nov 23 '16 at 21:19
  • @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* – Evan Carroll Nov 23 '16 at 23:07
  • 2
    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. – Dave Nov 24 '16 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.