Indexing LIKE query on full name-CodePudding

In my database I often use the following query:

SELECT "consultant_profiles".* 
FROM "consultant_profiles" 
WHERE (LOWER(CONCAT_WS(' ', first_name, last_name)) LIKE LOWER('john%'))

I want to have an index on that so my database will not need to do a sequential scan every time. I tried something like this but database is not using this index:

CREATE INDEX CONCURRENTLY idx_consultant_profile_name 
  ON consultant_profiles USING btree (lower(first_name), lower(last_name) text_pattern_ops)

CodePudding user response：

concat_ws() is probably marked as not immutable for the same reason why concat isn't.

You can use the || operator instead, which will require fiddling with coalesce() to properly deal with null values. You will have to use the exact same expression in your query.

Alternatively you can create an immutable function with the necessary expression because you know that in this case the concat_ws() function is indeed immutable. You then have to use that function in your query.

create function search_name(p_first_name text, p_last_name text)
  returns text
as
$$
  select LOWER(CONCAT_WS(' ', p_first_name, p_last_name));
$$
language sql
immutable;

Then create the index using that function:

CREATE INDEX idx_consultant_profile_name 
  ON consultant_profiles (search_name(first_name,last_name) text_pattern_ops);

and change your query to:

SELECT consultant_profiles.* 
FROM consultant_profiles 
WHERE search_name(first_name, last_name) LIKE LOWER('john%'))

CodePudding user response：

according to this answer IMMUTABLE in Postgres you must create a new function :

CREATE OR REPLACE FUNCTION LOWER_CONCAT_I(text1 text, text2 text) 
  RETURNS text
AS
$BODY$
    select LOWER(CONCAT_WS(' ', text1, text2));
$BODY$
LANGUAGE sql
IMMUTABLE;

and use it in your query:

SELECT "consultant_profiles".* 
FROM "consultant_profiles" 
WHERE LOWER_CONCAT_I(first_name, last_name) LIKE LOWER('john%')

and create the corresponding index:

CREATE INDEX CONCURRENTLY idx_consultant_profile_name 
  ON consultant_profiles USING btree (LOWER_CONCAT_I(first_name, last_name) text_pattern_ops)