PostgreSQL带ORDER BY的相似性匹配查询优化需求

开发者阿泽

2026-8-12

PostgreSQL相似数据查询优化方案

需求背景

需优化基于相似度匹配的PostgreSQL查询，用于在数据库中查找相似用户。当前单查询耗时约580ms，批量处理10K用户耗时超2小时，目标将批量耗时降至1小时以内，同时适配当前100K、未来百万级的数据规模，确保返回最匹配的结果。

原始查询语句

select 
  * 
from 
  (
    select 
      *, 
      (
        (
          score_StateCode + score_DisplayName
        )/ 3
      ) as matching_score 
    from 
      (
        select 
          *, 
          SIMILARITY("StateCode" :: text, 'CA') AS score_StateCode, 
          SIMILARITY(
            "DisplayName" :: text, 'A+ ROOFING & CONSTRUCTION'
          ) AS score_DisplayName 
        from 
          (
            select 
              * 
            from 
              customer_search_source 
            where 
              "CountryCode" = 'US'
          ) as new_table
      ) as nTable 
    order by 
      matching_score desc
  ) as s_table 
order by 
  matching_score desc 
limit 
  200;

执行计划（EXPLAIN ANALYZE）

Limit  (cost=19480.77..19506.10 rows=200 width=304) (actual time=577.034..584.769 rows=200 loops=1)
  ->  Gather Merge  (cost=19480.77..32564.21 rows=112136 width=304) (actual time=577.031..584.512 rows=200 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=18480.74..18620.91 rows=56068 width=304) (actual time=574.211..574.425 rows=215 loops=3)
              Sort Key: (((similarity((customer_search_source."StateCode")::text, 'CA'::text) + similarity(customer_search_source."DisplayName", 'A+ ROOFING & CONSTRUCTION'::text)) / '3'::double precision)) DESC
              Sort Method: external merge  Disk: 12208kB
              ->  Parallel Seq Scan on customer_search_source  (cost=0.00..6200.91 rows=56068 width=304) (actual time=0.064..478.160 rows=44856 loops=3)
                    Filter: ((("CountryCode")::text = 'US'::text))
                    Rows Removed by Filter: 1
Planning time: 0.755 ms
Execution time: 588.093 ms

已尝试的优化方法（效果不佳）

索引优化：为字段添加BTREE索引，但未起作用，因查询过滤阶段耗时极短
相似度阈值过滤：设置0.05阈值后结果仍超15K，排序耗时无明显变化
移除子查询：改为单查询后耗时反而增加
WITH子句拆分：将无ORDER BY的查询作为临时结构，排序耗时上升
降低LIMIT：调整至50后耗时无变化

数据结构说明

物化视图`customer_search_source`定义

SELECT customer."CustTreeNodeID",
    customer.id,
    customer."DisplayName",
    customer."CustomerNumber",
    email."EmailID",
    email."Email",
    phone."PhoneID",
    phone."PhoneNumber",
    address."AddressID",
    address."Street",
    address."Street2",
    address."City",
    address."StateCode",
    address."PostalCode",
    address."County",
    address."CountryCode",
    address."ZipPrefix",
    address."GeoCode",
    address."Latitude",
    address."Longitude",
    address."GeoCodeLocked",
    address."ErrorCode",
    address."Override",
    address."LocGeography",
    address."NeedsGeoUpdate",
    address."BillingContactName"
   FROM (((search_data_customer customer
     LEFT JOIN ( SELECT e."CustTreeNodeID",
            e."EmailID",
            e."Email"
           FROM search_data_email e) email USING ("CustTreeNodeID"))
     LEFT JOIN ( SELECT ph."CustTreeNodeID",
            ph."PhoneID",
            ph."PhoneNumber"
           FROM search_data_phone ph) phone USING ("CustTreeNodeID"))
     LEFT JOIN ( SELECT ad."CustTreeNodeID",
            ad."AddressID",
            ad."Street",
            ad."Street2",
            ad."City",
            ad."StateCode",
            ad."PostalCode",
            ad."County",
            ad."CountryCode",
            ad."ZipPrefix",
            ad."GeoCode",
            ad."Latitude",
            ad."Longitude",
            ad."GeoCodeLocked",
            ad."ErrorCode",
            ad."Override",
            ad."LocGeography",
            ad."NeedsGeoUpdate",
            ad."BillingContactName"
           FROM search_data_address ad) address USING ("CustTreeNodeID"));

基础表结构

CREATE TABLE "public"."search_data_address" (
    "id" integer NOT NULL,
    "CustTreeNodeID" character varying(256),
    "AddressID" character varying(256),
    "LegacyValue" text,
    "AddressTypeCode" character varying(256),
    "AddressName" text,
    "Street" text,
    "Street2" text,
    "City" text,
    "StateCode" character varying(256),
    "PostalCode" character varying(256),
    "County" text,
    "CreateID" character varying(256),
    "CreateDate" character varying(256),
    "LastModfID" character varying(256),
    "LastModfDate" character varying(256),
    "Locked" text,
    "Active" text,
    "ZipPrefix" text,
    "GeoCode" character varying(256),
    "Latitude" text,
    "Longitude" text,
    "CountryCode" character varying(256),
    "GeoCodeLocked" character varying(256),
    "ErrorCode" character varying(256),
    "Override" text,
    "LocGeography" text,
    "NeedsGeoUpdate" text,
    "BillingContactTypeID" character varying(256),
    "BillingContactName" text,
    "DeliveryAddressTypeID" character varying(256),
    "AddressTypeID" character varying(256),
    "ExternalSystemID" character varying(256),
    "IsCleaned" character varying(256)
) WITH (oids = false);

CREATE TABLE "public"."search_data_customer" (
    "id" integer NOT NULL,
    "CustTreeNodeID" character varying(256),
    "DisplayName" text,
    "CustomerNumber" text
) WITH (oids = false);

CREATE TABLE "public"."search_data_email" (
    "id" integer NOT NULL,
    "EmailID" character varying(256),
    "CustTreeNodeID" character varying(256),
    "CreateDate" character varying(256),
    "IsPrimary" character varying(256),
    "Email" text,
    "OptIn" text,
    "C_LescoFlag" text,
    "OldEmailID" character varying(256),
    "CreateID" character varying(256),
    "LastModfDate" character varying(256),
    "LastModfID" character varying(256),
    "OptInEquipment" text,
    "OptInEvents" text,
    "OptInProducts" text,
    "OptInGreenCat" text,
    "ExternalSystemID" character varying(256)
) WITH (oids = false);

CREATE TABLE "public"."search_data_phone" (
    "id" integer NOT NULL,
    "CustTreeNodeID" character varying(256),
    "PhoneID" character varying(256),
    "PhoneTypeID" character varying(256),
    "PhoneNumber" text,
    "Extension" text,
    "Notes" text,
    "CreateDate" character varying(256),
    "CreateID" character varying(256),
    "LastModfDate" character varying(256),
    "LastModfID" character varying(256),
    "Locked" text,
    "Active" text,
    "LegacyValue" text,
    "ExternalSystemID" character varying(256)
) WITH (oids = false);

优化建议

1. 利用`pg_trgm`索引加速Top-N相似度排序

核心是避免全表计算后排序，通过索引直接定位高相似度数据：

启用pg_trgm扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

为相似度计算字段创建GIN索引（GIN比GIST更适合高基数数据）：

CREATE INDEX idx_cust_search_state_display_trgm ON customer_search_source
USING GIN ("StateCode" gin_trgm_ops, "DisplayName" gin_trgm_ops);

简化查询语句，让PostgreSQL能使用索引：

SELECT
  *,
  (SIMILARITY("StateCode"::text, 'CA') + SIMILARITY("DisplayName"::text, 'A+ ROOFING & CONSTRUCTION'))/3 AS matching_score
FROM customer_search_source
WHERE "CountryCode" = 'US'
ORDER BY matching_score DESC
LIMIT 200;

该索引可让PostgreSQL跳过全表排序，直接检索相似度最高的前200条数据，大幅降低耗时。

2. 批量处理优化（针对10K用户场景）

避免循环执行单查询，改用批量计算减少连接开销：

创建临时表存储批量查询条件：

CREATE TEMP TABLE batch_queries (
  query_id INT PRIMARY KEY,
  target_statecode TEXT,
  target_displayname TEXT
);
-- 插入10K条查询数据，示例：
INSERT INTO batch_queries VALUES (1, 'CA', 'A+ ROOFING & CONSTRUCTION'), (2, 'NY', 'ABC PLUMBING'), ...;

批量计算相似度并取Top200：

WITH ranked_results AS (
  SELECT
    b.query_id,
    c.*,
    (SIMILARITY(c."StateCode"::text, b.target_statecode) + SIMILARITY(c."DisplayName"::text, b.target_displayname))/3 AS matching_score,
    ROW_NUMBER() OVER (PARTITION BY b.query_id ORDER BY matching_score DESC) AS rn
  FROM batch_queries b
  CROSS JOIN customer_search_source c
  WHERE c."CountryCode" = 'US'
)
SELECT * FROM ranked_results WHERE rn <= 200;

这种方式利用PostgreSQL的并行处理能力，一次性完成所有查询的相似度计算，减少数据库连接次数和重复扫描。

3. 物化视图优化

精简物化视图字段：只保留相似度计算所需的字段（如StateCode、DisplayName、CountryCode）和主键，减少数据量和排序开销
预处理字段：刷新物化视图时，将字符串字段标准化（如转为小写、去除特殊字符），减少相似度计算时的字符串处理耗时

4. 内存参数调整

解决排序阶段的磁盘IO问题：

临时增大work_mem（会话级别），让排序在内存中完成：
```
SET work_mem = '64MB';
```
若全局调整，可修改postgresql.conf中的work_mem参数，重启生效。

5. 版本升级（可选）

当前使用PostgreSQL 10，升级至12+版本可获得：

更高效的并行查询和排序优化
pg_trgm模块的性能提升
更好的内存管理机制

内容的提问来源于stack exchange，提问作者Pravin Singh

PostgreSQL带ORDER BY的相似性匹配查询优化需求

需求背景 #

原始查询语句 #

执行计划（EXPLAIN ANALYZE） #

已尝试的优化方法（效果不佳） #

数据结构说明 #

物化视图customer_search_source定义 #

基础表结构 #

优化建议 #

1. 利用pg_trgm索引加速Top-N相似度排序 #

2. 批量处理优化（针对10K用户场景） #

3. 物化视图优化 #

4. 内存参数调整 #

5. 版本升级（可选） #