如何加速PostgreSQL中大规模距离表的查询？

1. 重构查询语句，避免重复扫描 #

你当前的写法每个点单独开SELECT再UNION，相当于对表做了4500次扫描，这是慢的核心原因。改成一次性把所有查询点整合后关联查询：

-- 先定义要查询的源点和目标点集合
WITH query_points AS (
    VALUES 
        ('8829abb139fffff', '8829abb555fffff'),
        ('8829abb139fffff', '其他目标点ID'),
        ('8829abb469fffff', '8829abb555fffff'),
        -- 把4500组点对全列在这里
        ...
)
SELECT qp.source_point, MIN(dm.distances) AS min_distance
FROM query_points qp
JOIN dist_mx dm 
    ON (dm.point_id_0 = qp.source_point AND dm.point_id_1 = qp.target_point)
    OR (dm.point_id_1 = qp.source_point AND dm.point_id_0 = qp.target_point)
GROUP BY qp.source_point;

如果所有源点对应同一组目标点，改成交叉连接更高效：

WITH source_points AS (
    SELECT unnest(array['8829abb139fffff', '8829abb469fffff', ...]) AS source_point
),
target_points AS (
    SELECT unnest(array['8829abb555fffff', ...]) AS target_point
)
SELECT sp.source_point, MIN(dm.distances) AS min_distance
FROM source_points sp
CROSS JOIN target_points tp
JOIN dist_mx dm 
    ON (dm.point_id_0 = sp.source_point AND dm.point_id_1 = tp.target_point)
    OR (dm.point_id_1 = sp.source_point AND dm.point_id_0 = tp.target_point)
GROUP BY sp.source_point;

2. 创建针对性索引，解决OR条件瓶颈 #

OR条件会让单索引失效，以下两种索引方案二选一：

方案A：双向复合覆盖索引 #

直接给正反点对建索引，同时包含距离字段避免回表：

CREATE INDEX idx_dist_mx_0_1 ON dist_mx (point_id_0, point_id_1) INCLUDE (distances);
CREATE INDEX idx_dist_mx_1_0 ON dist_mx (point_id_1, point_id_0) INCLUDE (distances);

方案B：有序对表达式索引 #

把点对按字典序排序后建索引，不管正反都能命中：

CREATE INDEX idx_dist_mx_sorted ON dist_mx 
    (LEAST(point_id_0, point_id_1), GREATEST(point_id_0, point_id_1)) 
INCLUDE (distances);

对应的查询也要改成匹配排序后的键：

WITH query_points AS (
    VALUES ('8829abb139fffff', '8829abb555fffff'), ...
),
sorted_pairs AS (
    SELECT source_point,
           LEAST(source_point, target_point) AS p_min,
           GREATEST(source_point, target_point) AS p_max
    FROM query_points
)
SELECT sp.source_point, MIN(dm.distances) AS min_distance
FROM sorted_pairs sp
JOIN dist_mx dm 
    ON LEAST(dm.point_id_0, dm.point_id_1) = sp.p_min
    AND GREATEST(dm.point_id_0, dm.point_id_1) = sp.p_max
GROUP BY sp.source_point;

3. 预处理数据，统一存储有序点对 #

如果表还在构建阶段，或者能离线重排数据，直接把所有点对存成有序对（比如让point_id_0始终是字典序更小的那个）：

-- 插入数据时预处理
INSERT INTO dist_mx (point_id_0, point_id_1, distances)
SELECT LEAST(p1, p2), GREATEST(p1, p2), distance
FROM 原始数据源;

之后查询就不用写OR条件，直接匹配正向对，索引效率会翻倍。

4. 调整PostgreSQL配置，提升并行处理能力 #

针对大表查询，修改postgresql.conf里的参数：

• max_parallel_workers_per_gather = 8（根据CPU核心数调整，4-8都可以）
• work_mem = 64MB（提升排序/哈希操作的内存阈值，避免用磁盘临时表）
• shared_buffers = 8GB（设为系统内存的25%-50%，比如32GB内存的机器设8GB）
  修改后重启服务生效。

5. 超大量点查询用临时表 #

如果点数量过万，把查询点插入临时表再关联，比VALUES列表更高效：

CREATE TEMP TABLE temp_query (
    source_point TEXT,
    target_point TEXT,
    PRIMARY KEY (source_point, target_point)
);

INSERT INTO temp_query VALUES 
    ('8829abb139fffff', '8829abb555fffff'), ...;

-- 给临时表加索引
CREATE INDEX idx_temp_query ON temp_query (source_point, target_point);

-- 关联查询
SELECT tq.source_point, MIN(dm.distances) AS min_distance
FROM temp_query tq
JOIN dist_mx dm 
    ON (dm.point_id_0 = tq.source_point AND dm.point_id_1 = tq.target_point)
    OR (dm.point_id_1 = tq.source_point AND dm.point_id_0 = tq.target_point)
GROUP BY tq.source_point;

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