Optimization of search on concatenated firstname and lastname in PostgreSQL

Question

I've written a SQL query in Postgres which search for a user by both firstname and lastname. My question is simply if it can be optimized, since it will be used a lot.

CREATE INDEX users_firstname_special_idx ON users(firstname text_pattern_ops);
CREATE INDEX users_lastname_special_idx ON users(lastname text_pattern_ops);

SELECT id, firstname, lastname FROM users WHERE firstname || ' ' || lastname ILIKE ('%' || 'sen' || '%') LIMIT 25;

If I run an explain I get the followin output:

Limit  (cost=0.00..1.05 rows=1 width=68)
  ->  Seq Scan on users  (cost=0.00..1.05 rows=1 width=68)
        Filter: (((firstname || ' '::text) || lastname) ~~* '%sen%'::text)

As I understand I should try and make postgrep skip the "Filter:"-thing. Is that correct?

Hope you guys have any suggestions.

Cheers.

Answer 1

If you have more than 1 % wildcards in a string, you need to use a trigram index.

In your case, however, you are doing something odd. You concatenate firstname and lastname, with a space in the middle. The search string '%sen%' is therefore only present in the firstname or the lastname and never including the space. A better solution would therefore be:

CREATE INDEX users_firstname_special_idx ON users USING gist (firstname gist_trgm_ops);
CREATE INDEX users_lastname_special_idx ON users USING gist (lastname gist_trgm_ops);

SELECT id, firstname || ' ' || lastname AS fullname
FROM users
WHERE firstname ILIKE ('%sen%') OR lastname ILIKE ('%sen%')
LIMIT 25;

Answer 2

You described situation exactly from PostgreSQL Documentation:

Indexes on Expressions