Postgresql – Why isn’t the Postgres index being used

indexperformancepostgresql

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?

Best Answer

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.

Related Question