CODEWITHSUNDEEP
postgresqlpostgresql-9.4
Roland Pish
Roland Pish

Reputation: 815

Postgresql 9.4: index not working in a pattern search

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?

Upvotes: 4

Views: 4081

Answers (1)

mnencia
mnencia

Reputation: 3368

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

Upvotes: 4

Related Questions