Как ускорить pg_trgm?

Question

Создать таблицу:

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

Запрос:

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

Как ускорить этот запрос?

Answer 1

Смотрите документацию:

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

Это может быть быстрее сделать запрос, как это:

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;