04 - Search Optimization
Last modified on Mon 04 Apr 2022

Problem

You have a mission to optimize querying users by their name and surname by using this query:

SELECT *
FROM users
WHERE first_name || ' ' || last_name ILIKE '%joe%'

How would you speed up this query?

Input Data

id first_name last_name role
1 John Doe author
2 Joe Smith author
3 Mark Cohen admin

Setup

DROP TABLE IF EXISTS users;
DROP SEQUENCE IF EXISTS users_id_seq;

CREATE SEQUENCE users_id_seq;

CREATE TABLE users (
  id BIGINT NOT NULL DEFAULT NEXTVAL('users_id_seq'::regclass),
  first_name VARCHAR,
  last_name VARCHAR,
  role VARCHAR,
  PRIMARY KEY (id)
);

INSERT INTO users (first_name, last_name, role) VALUES
('John', 'Doe', 'author'),
('Joe', 'Smith', 'author'),
('Mark', 'Cohen', 'admin');

Solution

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_users
  ON users USING GIN ((first_name || ' ' || last_name) gin_trgm_ops);

Go ahead and check the performance benefits (you can use EXPLAIN to check the execution plan)

Keywords