Postgresql 9.4: index not working in a pattern search

Question

I have a table called "doctors" and a field called "fullname" which will store names with accents. What I need to do is an "accent insensitive + case insensitive" search, something like:

SELECT * 
FROM doctors
WHERE unaccent_t(fullname) ~* 'unaccented_and_lowercase_string';

where the value to search will come unaccented+lowercase and unaccent_t is a function defined as:

CREATE FUNCTION unaccent_t(text, lowercase boolean DEFAULT false)
RETURNS text AS
$BODY$
SELECT CASE
  WHEN $2 THEN unaccent('unaccent', lower(trim($1)))
  ELSE unaccent('unaccent', trim($1))
END;
$BODY$ LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp;

(I already installed 'unaccent' extension).

So, I went ahead and created the index for "fullname" field:

CREATE INDEX doctors_fullname ON doctors (unaccent_t(fullname) text_pattern_ops);

(I also tried with varchar_pattern_ops and also no specifying ops at all)

In the doctors table, I have around 15K rows.

The query works and I get the expected results, but when I add the explain analyze to the query, I don't see that the index is used:

Seq Scan on doctors  (cost=0.00..4201.76 rows=5 width=395) (actual time=0.282..182.025 rows=15000 loops=1)
  Filter: (unaccent_t((fullname)::text, false) ~* 'garcia'::text)
  Rows Removed by Filter: 1
Planning time: 0.207 ms
Execution time: 183.387 ms

I also tried removing the optional parameter from unaccent_t but I got the same results.

In a scenario like this, how should I define the index so it gets used in a query like the one above?

This technique can work with = or a left-anchored LIKE, but ~* is an operator for matching a regular expression. — Daniel Vérité
– Daniel Vérité, Commented Oct 9, 2015 at 19:41
So just try SELECT * WHERE name like '%garcia' that should use the index. But this wont SELECT * WHERE name like 'garcia%' — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 9, 2015 at 20:11

mnencia · Accepted Answer · 2015-10-09 20:25:24Z

4

Btree indexes are usable to speed up operations only when the pattern is left anchored.

Starting from PostgreSQL 9.3 you can speed up generic regular expression searches using a GIN or GiST index with the operator classes provided by the pg_trgm contrib module.

You can read more about it on the PostgreSQL manual at http://www.postgresql.org/docs/9.4/static/pgtrgm.html#AEN163078

answered Oct 9, 2015 at 20:25

mnencia

3,3881 gold badge26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Roland Pish Over a year ago

Thanks, this sounds good. I'll create then a gin/gist index for this type of search. Cheers.

Roland Pish Over a year ago

Trying to create a gin index with _text_ops tells me that this operator does not accept text data. Without specifiyng any options it tells me that there is no default operator class. Specifying gin_trgm_ops creates the index and it works but I won't be doing 'trigram' full text search in this field. Is there another operator class I can use for this gin index? Or using gin_trgm_ops is fine even if I won't perform any full-text search?

Roland Pish Over a year ago

Also, the explain analyze now tells me that the index is being used, but the execution time is basically the same without using an index. It is like the index is not improving the execution time of the query. What might be wrong?

mnencia Over a year ago

The GIN or GIST _trgm_ops indexes can be used to speed up any regexp or LIKE search. If the resulting execution time is similar to the one without the index it could be because your data set is small or because you are selecting a big part of your your data anyway.

Roland Pish Over a year ago

mnencia, that makes perfect sense. My test data falls into what you've mentioned. The good thing is that now I know that the index is being used. So, in my query scenario this gin index will do the work. Thanks again.

Collectives™ on Stack Overflow

Postgresql 9.4: index not working in a pattern search

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related