4

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.

Cyberlurk
  • 766
  • 3
  • 9
  • 30
  • do not concatenate the 2 name elements, filter on them separately and don't use double ended wildcards either. Won't matter how many indexes you create if you filter this way, they wont be usable. – Paul Maxwell Aug 05 '15 at 07:37
  • Have you checked postgres full text search? – Ihor Romanchenko Aug 05 '15 at 08:59
  • Yeah, but to be honest I find text searching a bit "confusing". I have tried several ways now, but I can't seem to get the search to use an index unless I make a materialized view with a column of the concatenated names and put an index on that column. That however isn't smart since I need to refresh the view quite often and I expect the table to get several thousand rows. – Cyberlurk Aug 05 '15 at 09:02

2 Answers2

8

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;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Thansk for your answer. I do use the space so that I can write the whole name (both firstname and lastname) in the search field on my website. So if a user is named Jack Jones I can actually write "Jack Jones", or "Jack Jo" and he'll show up in the result. In that way I don't just search for a firstname or a lastname. – Cyberlurk Aug 05 '15 at 07:59
  • Then do the concatenation in the select list and search on the individual fields; see updated answer. – Patrick Aug 05 '15 at 09:28
  • Thanks! You're my new best friend. :) Testing on a user table with 10.000 rows I got from a cost of 460 to 8.3 when using "EXPLAIN"! Thanks a lot! – Cyberlurk Aug 05 '15 at 10:07
  • Hmm, however this still won't work if I write the whole name like "lastname ILIKE ('%Tristan Larson%')" – Cyberlurk Aug 05 '15 at 10:26
  • Always glad to have a new best friend! If you search on short phrases like in your question, then use the approach I suggested.If you search on full names then you should look for (case-insensitive) equality (e.g. without the wildcards). You can create an index on `firstname || ' ' || lastname` to make that faster. Keep in mind that you create the index to match the search. – Patrick Aug 05 '15 at 13:16
  • Thanks for your help. It has been much appreciated. – Cyberlurk Aug 05 '15 at 13:17
  • The approach described in this article is better as it uses a single where clause without the "OR" for the compound columns https://niallburkley.com/blog/index-columns-for-like-in-postgres/ – Sanjiv Jivan Jun 21 '22 at 10:42
3

You described situation exactly from PostgreSQL Documentation:

Indexes on Expressions

sibnick
  • 3,995
  • 20
  • 20