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.
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;