PostgreSQL 16排序性能优化咨询：如何将40+秒排序耗时降至个位数？

问题现象 #

原查询不排序时耗时约2秒，添加ORDER BY distance ASC NULLS LAST排序后耗时超40秒，已将work_mem从4GB调至40GB，无明显改善。需将排序耗时优化至个位数秒级。

数据库环境 #

• PostgreSQL版本：16.0 (Debian 16.0-1.pgdg120+1)，x86_64-pc-linux-gnu平台，gcc 12.2.0编译
• 存储：2TB NVMe Gen3磁盘
• 服务器配置：双Xeon 2630 V2 CPU，64GB内存
• 表数据规模：
  • search表：约1.6亿条记录，其中locale='en'的约300万条
  • products_locations表：约1500万条记录，数据持续增长

查询语句 #

SELECT ppl.id, ppl.location_id, 
  calculate_distance(ppl.latitude, ppl.longitude, 45.4754304,
    -73.4756864, 'K') AS distance 
FROM search sh
LEFT JOIN (
  SELECT p.id, p.category, p.rating, p.health_score, p.efficiency_score, 
    p.eco_score, pl.id AS location_id, pl.base_price, pl.price_100,
    pl.quantity, pl.latitude, pl.longitude, pl.currency, pl.aisle,
    pl.store_id, pl.is_online 
  FROM products p 
  LEFT JOIN products_locations pl ON pl.product_id = p.id 
    AND ((pl.latitude <= 45.5204 AND pl.latitude >= 45.43047
     AND pl.longitude <= -73.41156 AND pl.longitude >= -73.53981) 
    OR covered_regions ILIKE '%=CA-QC=%') 
) ppl ON sh.product_id = ppl.id 
WHERE 1 = 1 AND ppl.category IN ( 1 ) AND sh.text &@* 'salt' 
  AND sh.locale = 'en' 
ORDER BY distance ASC NULLS LAST 
LIMIT 10 OFFSET 0;

注意：distance必须实时计算，依赖每次查询的输入坐标，无法提前预计算。

EXPLAIN ANALYZE VERBOSE输出 #

Limit  (cost=5865583.61..5865583.63 rows=10 width=40) (actual time=41015.372..41015.381 rows=10 loops=1)
  Output: p.id, pl.id, (calculate_distance((pl.latitude)::double precision, (pl.longitude)::double precision, '45.4754304'::double precision, '-73.4756864'::double precision, 'K'::character varying))
  ->  Sort  (cost=5865583.61..5865592.51 rows=3561 width=40) (actual time=40634.188..40634.196 rows=10 loops=1)
        Output: p.id, pl.id, (calculate_distance((pl.latitude)::double precision, (pl.longitude)::double precision, '45.4754304'::double precision, '-73.4756864'::double precision, 'K'::character varying))
        Sort Key: (calculate_distance((pl.latitude)::double precision, (pl.longitude)::double precision, '45.4754304'::double precision, '-73.4756864'::double precision, 'K'::character varying))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Hash Left Join  (cost=285685.60..5865506.66 rows=3561 width=40) (actual time=3736.395..39893.634 rows=2019651 loops=1)
              Output: p.id, pl.id, calculate_distance((pl.latitude)::double precision, (pl.longitude)::double precision, '45.4754304'::double precision, '-73.4756864'::double precision, 'K'::character varying)
              Hash Cond: (p.id = pl.product_id)
              ->  Nested Loop  (cost=0.43..5555191.45 rows=3561 width=16) (actual time=2077.389..29232.771 rows=2012603 loops=1)
                    Output: p.id
                    Inner Unique: true
                    ->  Index Scan using search_product_id_lang_text_idx on public.search sh  (cost=0.00..5527417.00 rows=3561 width=16) (actual time=2077.276..17470.331 rows=2014112 loops=1)
                          Output: sh.id, sh.product_id, sh.locale, sh.text, sh.username, sh.creation_time, sh.update_time
                          Index Cond: (((sh.locale)::text = 'en'::text) AND (sh.text &@* 'salt'::text))
                    ->  Index Scan using products_id_idx on public.products p  (cost=0.43..7.80 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=2014112)
                          Output: p.id
                          Index Cond: (p.id = sh.product_id)
                          Filter: (p.category = 1)
                          Rows Removed by Filter: 0
              ->  Hash  (cost=284349.47..284349.47 rows=106856 width=48) (actual time=1658.568..1658.572 rows=181222 loops=1)
                    Output: pl.id, pl.latitude, pl.longitude, pl.product_id
                    Buckets: 262144 (originally 131072)  Batches: 1 (originally 1)  Memory Usage: 16560kB
                    ->  Bitmap Heap Scan on public.products_locations pl  (cost=48272.92..284349.47 rows=106856 width=48) (actual time=662.642..1583.833 rows=181222 loops=1)
                          Output: pl.id, pl.latitude, pl.longitude, pl.product_id
                          Recheck Cond: (((pl.longitude <= '-73.41156'::numeric) AND (pl.longitude >= '-73.53981'::numeric) AND (pl.latitude <= 45.5204) AND (pl.latitude >= 45.43047)) OR (pl.covered_regions ~~* '%=CA-QC=%'::text))
                          Heap Blocks: exact=119626
                          ->  BitmapOr  (cost=48272.92..48272.92 rows=132641 width=0) (actual time=630.308..630.311 rows=0 loops=1)
                                ->  BitmapAnd  (cost=48246.21..48246.21 rows=53446 width=0) (actual time=527.770..527.772 rows=0 loops=1)
                                      ->  Bitmap Index Scan on products_locations_longitude_idx  (cost=0.00..15542.24 rows=606968 width=0) (actual time=159.704..159.705 rows=585171 loops=1)
                                            Index Cond: ((pl.longitude <= '-73.41156'::numeric) AND (pl.longitude >= '-73.53981'::numeric))
                                      ->  Bitmap Index Scan on products_locations_latitude_idx  (cost=0.00..32650.29 rows=1275773 width=0) (actual time=357.549..357.549 rows=1290797 loops=1)
                                            Index Cond: ((pl.latitude <= 45.5204) AND (pl.latitude >= 45.43047))
                                ->  Bitmap Index Scan on product_location_covered_regions_idx  (cost=0.00..0.00 rows=79194 width=0) (actual time=102.535..102.535 rows=0 loops=1)
                                      Index Cond: (pl.covered_regions ~~* '%=CA-QC=%'::text)
Planning Time: 16.027 ms
JIT:
  Functions: 22
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 4.399 ms, Inlining 48.738 ms, Optimization 213.331 ms, Emission 119.286 ms, Total 385.754 ms
Execution Time: 41115.276 ms

优化建议 #

1. 调整查询逻辑，减少排序基数 #

当前执行计划先关联所有符合条件的200多万行数据，再计算距离排序。可以改为先筛选地理范围记录，再关联其他表，同时将LEFT JOIN改为INNER JOIN（WHERE条件过滤ppl.category，LEFT JOIN实际等价于INNER JOIN）：

SELECT p.id, pl.id AS location_id,
       calculate_distance(pl.latitude, pl.longitude, 45.4754304, -73.4756864, 'K') AS distance
FROM search sh
JOIN products p ON sh.product_id = p.id
JOIN products_locations pl ON p.id = pl.product_id
WHERE sh.locale = 'en'
  AND sh.text &@* 'salt'
  AND p.category = 1
  AND ((pl.latitude BETWEEN 45.43047 AND 45.5204)
       AND (pl.longitude BETWEEN -73.53981 AND -73.41156)
       OR pl.covered_regions ILIKE '%=CA-QC=%')
ORDER BY distance ASC NULLS LAST
LIMIT 10;

2. 优化地理查询索引 #

当前使用单独的纬度、经度索引，建议创建复合地理索引提升范围查询效率：

CREATE INDEX idx_products_locations_lat_lon ON products_locations(latitude, longitude);

如果使用PostGIS扩展，可将经纬度转为地理类型并创建空间索引，查询效率会更高：

-- 添加地理字段
ALTER TABLE products_locations ADD COLUMN geog geography(POINT, 4326);
UPDATE products_locations SET geog = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
-- 创建空间索引
CREATE INDEX idx_products_locations_geog ON products_locations USING GIST(geog);

查询时用ST_DWithin替代范围过滤：

AND ST_DWithin(pl.geog, ST_SetSRID(ST_MakePoint(-73.4756864, 45.4754304), 4326), 10000) -- 10公里范围，按需调整

3. 优化search表索引 #

当前search表的索引覆盖locale和text，但查询后需关联products的category，可创建包含product_id的复合索引：

CREATE INDEX idx_search_locale_text_product_id ON search(locale, text) INCLUDE (product_id);

4. 关闭不必要的JIT编译 #

执行计划显示JIT耗时约385ms，可临时关闭JIT减少编译开销：

SET jit = off;

5. 提前筛选近距记录 #

先获取products_locations中距离最近的一批记录，再关联其他表，大幅减少排序数据量：

WITH nearby_locations AS (
  SELECT pl.id AS location_id, pl.product_id,
         calculate_distance(pl.latitude, pl.longitude, 45.4754304, -73.4756864, 'K') AS distance
  FROM products_locations pl
  WHERE ((pl.latitude BETWEEN 45.43047 AND 45.5204)
         AND (pl.longitude BETWEEN -73.53981 AND -73.41156)
         OR pl.covered_regions ILIKE '%=CA-QC=%')
  ORDER BY distance ASC NULLS LAST
  LIMIT 1000 -- 先取1000条近距记录，再关联过滤
)
SELECT p.id, nl.location_id, nl.distance
FROM nearby_locations nl
JOIN products p ON nl.product_id = p.id
JOIN search sh ON p.id = sh.product_id
WHERE sh.locale = 'en'
  AND sh.text &@* 'salt'
  AND p.category = 1
ORDER BY nl.distance ASC NULLS LAST
LIMIT 10;

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