CODEWITHSUNDEEP
sqlpostgresqlpg-trgm
Aleksandr Maybach
Aleksandr Maybach

Reputation: 610

How to speed up pg_trgm?

Create table:

CREATE TABLE public.personal
    (
      id bigserial NOT NULL PRIMARY KEY,
      first_name character varying(255) NOT NULL,
      last_name character varying(255),
      middle_name character varying(255),
      deleted integer NOT NULL DEFAULT 0
    );

Query: 

select last_name, first_name, middle_name, similarity(concat_ws(' ', last_name, first_name, middle_name), 'Smit') AS sml
from personal ORDER BY sml DESC LIMIT 100

How to speed up this query?

Upvotes: 2

Views: 419

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246463

See the documentation:

CREATE INDEX ON personal USING gist
   ((coalesce(last_name, '')  || ' ' ||
     coalesce(first_name, '') || ' ' ||
     coalesce(middle_name, '')) gist_trgm_ops);

It might be faster to query like this:

SELECT last_name, first_name, middle_name,
   (coalesce(last_name, '')  || ' ' ||
    coalesce(first_name, '') || ' ' ||
    coalesce(middle_name, '')  <-> 'Smit') AS dist
   FROM personal
   ORDER BY dist LIMIT 100;

Upvotes: 3

Related Questions