Postgres多表相似性查询优化咨询（pg_trgm）

问题背景 #

基于pg_trgm扩展实现词相似性查询，需同时检索usr和loc两个表的多列数据。表结构、索引及测试数据如下：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 表定义
CREATE TABLE usr (
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    loc_id bigint,
    first_name text,
    last_name text
);
CREATE TABLE loc (
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    country text,
    city text
);

-- 主键约束
ALTER TABLE ONLY usr
    ADD CONSTRAINT usr_pkey PRIMARY KEY (id);
ALTER TABLE ONLY loc
    ADD CONSTRAINT loc_pkey PRIMARY KEY (id);

-- 外键约束
ALTER TABLE ONLY usr
    ADD CONSTRAINT usr_loc_id_fkey FOREIGN KEY (loc_id) REFERENCES loc(id);

-- 索引
CREATE INDEX usr_loc_id_idx ON usr
    USING btree (loc_id);
    
CREATE INDEX usr_first_name_last_name_idx ON usr
    USING gist ((first_name || ' ' || last_name) gist_trgm_ops);
CREATE INDEX loc_country_city_idx ON loc
    USING gist ((country || ' ' || city) gist_trgm_ops);

-- 插入测试数据
INSERT INTO loc (country, city)
SELECT substr(md5(random()::text), 0, 25), substr(md5(random()::text), 0, 25)
FROM generate_series(1, 100000) AS t;

INSERT INTO usr (first_name, last_name, loc_id)
SELECT substr(md5(random()::text), 0, 25), substr(md5(random()::text), 0, 25), trunc(random() * 99999 + 1)
FROM generate_series(1, 1000000) AS t;

单表查询已优化，执行效率良好：

SELECT
    usr.id AS usr_id, usr.first_name, usr.last_name,
    loc.id AS loc_id, loc.country, loc.city
FROM usr
LEFT JOIN loc ON usr.loc_id = loc.id
WHERE 'baker' <% (usr.first_name || ' ' || usr.last_name)
ORDER  BY 'baker' <<-> (usr.first_name || ' ' || usr.last_name)
LIMIT 20;

对应的查询计划：

Limit  (cost=0.70..236.19 rows=20 width=118) (actual time=397.086..596.842 rows=1 loops=1)
  ->  Nested Loop Left Join  (cost=0.70..1178.16 rows=100 width=118) (actual time=397.084..596.839 rows=1 loops=1)
        ->  Index Scan using usr_first_name_last_name_idx on usr  (cost=0.41..422.41 rows=100 width=66) (actual time=397.038..596.792 rows=1 loops=1)
              Index Cond: (((first_name || ' '::text) || last_name) %> 'baker'::text)
              Order By: (((first_name || ' '::text) || last_name) <<->> 'baker'::text)
        ->  Index Scan using loc_pkey on loc  (cost=0.29..7.55 rows=1 width=56) (actual time=0.037..0.037 rows=1 loops=1)
              Index Cond: (id = usr.loc_id)
Planning Time: 1.252 ms
Execution Time: 596.931 ms

但同时检索两表列的查询性能极差：

SELECT
    usr.id AS usr_id, usr.first_name, usr.last_name,
    loc.id AS loc_id, loc.country, loc.city
FROM usr
LEFT JOIN loc ON usr.loc_id = loc.id
WHERE
    'baker' <% (usr.first_name || ' ' || usr.last_name)
    OR
    'baker' <% (loc.country || ' ' || loc.city)
ORDER BY
    ('baker' <<-> (usr.first_name || ' ' || usr.last_name))
    +
    ('baker' <<-> (loc.country || ' ' || loc.city));

对应的查询计划：

Gather Merge  (cost=21071.24..21090.61 rows=166 width=118) (actual time=6297.322..6301.774 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort  (cost=20071.22..20071.42 rows=83 width=118) (actual time=6286.205..6286.208 rows=0 loops=3)
        Sort Key: ((('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name)) + ('baker'::text <<-> ((loc.country || ' '::text) || loc.city))))
        Sort Method: quicksort  Memory: 25kB
        Worker 0:  Sort Method: quicksort  Memory: 25kB
        Worker 1:  Sort Method: quicksort  Memory: 25kB
        ->  Parallel Hash Left Join  (cost=2460.57..20068.57 rows=83 width=118) (actual time=4197.337..6284.058 rows=0 loops=3)
              Hash Cond: (usr.loc_id = loc.id)
              Filter: (('baker'::text <% ((usr.first_name || ' '::text) || usr.last_name)) OR ('baker'::text <% ((loc.country || ' '::text) || loc.city)))
              Rows Removed by Filter: 333335
              ->  Parallel Seq Scan on usr  (cost=0.00..16512.69 rows=416669 width=66) (actual time=0.078..214.231 rows=333335 loops=3)
              ->  Parallel Hash  (cost=1725.25..1725.25 rows=58825 width=56) (actual time=23.072..23.073 rows=33334 loops=3)
                    Buckets: 131072  Batches: 1  Memory Usage: 10432kB
                    ->  Parallel Seq Scan on loc  (cost=0.00..1725.25 rows=58825 width=56) (actual time=0.007..5.594 rows=33334 loops=3)
Planning Time: 3.470 ms
Execution Time: 6301.859 ms

性能瓶颈分析 #

当前查询的核心问题是OR条件导致PostgreSQL无法有效利用已创建的GIST索引。数据库无法同时对两个表的索引进行高效筛选，只能先执行全表哈希连接，再过滤结果，这导致了大量的无效数据扫描和过滤操作，执行时间从几百毫秒飙升至6秒以上。

优化方案 #

可以通过拆分查询并合并结果的方式，让两个条件分别利用各自的索引，再统一排序。具体步骤如下：

1. 拆分查询为两个部分：
   • 第一部分：匹配usr表的姓名相似性，关联loc表获取位置信息
   • 第二部分：匹配loc表的位置相似性，关联usr表获取用户信息（排除已在第一部分出现的用户，避免重复）
2. 用UNION ALL合并两个查询的结果（避免重复数据去重的开销）
3. 对合并后的结果计算总相似度并排序

优化后的查询语句：

WITH query_results AS (
    -- 匹配用户姓名的结果
    SELECT
        usr.id AS usr_id, usr.first_name, usr.last_name,
        loc.id AS loc_id, loc.country, loc.city,
        ('baker' <<-> (usr.first_name || ' ' || usr.last_name)) + 
        ('baker' <<-> (loc.country || ' ' || loc.city)) AS total_similarity
    FROM usr
    LEFT JOIN loc ON usr.loc_id = loc.id
    WHERE 'baker' <% (usr.first_name || ' ' || usr.last_name)
    
    UNION ALL
    
    -- 匹配位置信息的结果（排除已在第一部分出现的用户）
    SELECT
        usr.id AS usr_id, usr.first_name, usr.last_name,
        loc.id AS loc_id, loc.country, loc.city,
        ('baker' <<-> (usr.first_name || ' ' || usr.last_name)) + 
        ('baker' <<-> (loc.country || ' ' || loc.city)) AS total_similarity
    FROM loc
    INNER JOIN usr ON loc.id = usr.loc_id
    WHERE 'baker' <% (loc.country || ' ' || loc.city)
      AND NOT ('baker' <% (usr.first_name || ' ' || usr.last_name))
)
SELECT usr_id, first_name, last_name, loc_id, country, city
FROM query_results
ORDER BY total_similarity
LIMIT 20; -- 可根据需求调整返回数量

优化原理 #

• 拆分后的两个子查询分别利用usr_first_name_last_name_idx和loc_country_city_idx这两个GIST索引，快速筛选出符合条件的数据，避免全表扫描
• UNION ALL直接合并结果，比UNION少了去重步骤，性能更优；第二部分的NOT条件用于排除已在第一部分匹配到的用户，避免重复返回
• 最后统一排序，保证结果按总相似度排序

额外优化建议 #

1. 调整相似度阈值：如果业务允许，可以通过设置pg_trgm.similarity_threshold参数提高相似度阈值，减少返回的结果数量，进一步提升性能
2. GiST vs GIN索引选择：对于模糊查询，GIST索引适合相似性排序，而GIN索引的等值查询更快。如果你的场景更侧重快速筛选而非排序，可以尝试将GIST索引替换为GIN索引测试性能
3. 预计算合并字段：可以在表中新增一个字段存储first_name || ' ' || last_name和country || ' ' || city的结果，创建索引时直接使用该字段，避免查询时的字符串拼接开销，例如：

   ALTER TABLE usr ADD COLUMN full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;
   CREATE INDEX usr_full_name_idx ON usr USING gist (full_name gist_trgm_ops);
   

总结 #

PostgreSQL支持多表相似性查询，但直接使用OR条件会导致索引失效。通过拆分查询并合并结果的方式，可以有效利用已创建的trgm索引，大幅提升查询性能。

内容的提问来源于stack exchange，提问作者Marnix.hoh