sql - 如何加速 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

参见 the documentation :

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;