PostgreSQL中ST_Contains关联查询性能优化求助

我需要将100米网格表tb_grid_4326_100m（共75,769条记录）与点表tb_points（总2,434,536条记录）通过ST_Contains函数做左关联聚合查询。指定hour='23'条件后，tb_points返回约100,000条记录，最终为约75,000条网格记录关联约100,000条点记录。

已为网格表创建gist(geom)索引，为点表创建gist(hour, geom)索引，但当前查询耗时30秒。以下是查询SQL及执行计划，寻求性能优化方案：

SELECT
  a.geom, 'tk' category,
  ROUND(avg(tk), 1) tk
FROM
  tb_grid_4326_100m a left outer join 
(
  SELECT
    tk-273.15 tk, geom
  FROM
    tb_points
  WHERE
    hour = '23'
) b ON st_contains(a.geom, b.geom)
GROUP BY
  a.geom

QUERY PLAN                                                                                                                                                          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize GroupAggregate  (cost=54632324.85..54648025.25 rows=50698 width=184) (actual time=8522.042..8665.129 rows=50698 loops=1)                                   |
  Group Key: a.geom                                                                                                                                                 |
  ->  Gather Merge  (cost=54632324.85..54646504.31 rows=101396 width=152) (actual time=8522.032..8598.567 rows=50698 loops=1)                                       |
        Workers Planned: 2                                                                                                                                          |
        Workers Launched: 2                                                                                                                                         |
        ->  Partial GroupAggregate  (cost=54631324.83..54633800.68 rows=50698 width=152) (actual time=8490.577..8512.725 rows=16899 loops=3)                        |
              Group Key: a.geom                                                                                                                                     |
              ->  Sort  (cost=54631324.83..54631785.36 rows=184212 width=130) (actual time=8490.557..8495.249 rows=16996 loops=3)                                   |
                    Sort Key: a.geom                                                                                                                                |
                    Sort Method: external merge  Disk: 2296kB                                                                                                       |
                    Worker 0:  Sort Method: external merge  Disk: 2304kB                                                                                            |
                    Worker 1:  Sort Method: external merge  Disk: 2296kB                                                                                            |
                    ->  Nested Loop Left Join  (cost=0.41..54602621.56 rows=184212 width=130) (actual time=1.729..8475.942 rows=16996 loops=3)                      |
                          ->  Parallel Seq Scan on tb_grid_4326_100m a  (cost=0.00..5866.24 rows=21124 width=120) (actual time=0.724..2.846 rows=16899 loops=3)     |
                          ->  Index Scan using sidx_tb_points on tb_points  (cost=0.41..2584.48 rows=10 width=42) (actual time=0.351..0.501 rows=1 loops=50698)|
                                Index Cond: (((hour)::text = '23'::text) AND (geom @ a.geom))                                                                       |
                                Filter: st_contains(a.geom, geom)                                                                                                   |
                                Rows Removed by Filter: 0                                                                                                           |
Planning Time: 1.372 ms                                                                                                                                             |
Execution Time: 8667.418 ms                                                                                                                                         |

优化方案 #

1. 反转关联方向，减少循环次数 #

原查询用网格表驱动点表，需循环7.5万次；改为用过滤后的点表驱动网格表，仅需循环10万次，且可利用网格表的空间索引快速匹配：

SELECT
  a.geom, 'tk' category,
  ROUND(avg(b.tk - 273.15), 1) tk
FROM (
  SELECT tk, geom FROM tb_points WHERE hour = '23'
) b
RIGHT JOIN tb_grid_4326_100m a ON st_contains(a.geom, b.geom)
GROUP BY a.geom

2. 优化点表索引 #

原gist(hour, geom)索引对hour等值+空间查询的效率不如BTREE+GiST组合索引，因为文本等值条件用BTREE更高效：

-- 创建组合索引：先通过BTREE过滤hour，再用GiST查空间
CREATE INDEX idx_tb_points_hour_btree ON tb_points USING btree(hour) INCLUDE (tk, geom);
CREATE INDEX idx_tb_points_geom_gist ON tb_points USING gist(geom);

-- 若仅针对hour='23'的查询，可创建部分索引缩小范围
CREATE INDEX idx_tb_points_hour23_geom ON tb_points USING gist(geom) WHERE hour = '23';

3. 避免几何类型排序开销 #

执行计划中出现外部磁盘排序，原因是GROUP BY a.geom（几何类型排序成本极高）。若网格表有唯一主键（如grid_id），改用主键分组：

SELECT
  a.geom, 'tk' category,
  ROUND(avg(b.tk - 273.15), 1) tk
FROM
  tb_grid_4326_100m a
LEFT JOIN (
  SELECT tk, geom FROM tb_points WHERE hour = '23'
) b ON st_contains(a.geom, b.geom)
GROUP BY a.grid_id, a.geom -- 若主键与geom一一对应，可仅GROUP BY a.grid_id

4. 切换为哈希连接算法 #

原查询用嵌套循环，当前数据量下哈希连接更高效，可临时调整参数或用查询提示：

-- 临时关闭嵌套循环，执行后恢复
SET enable_nestloop = off;
-- 执行查询
SET enable_nestloop = on;

-- 或用PostgreSQL 12+支持的查询提示
SELECT /*+ HashJoin(a b) */
  a.geom, 'tk' category,
  ROUND(avg(b.tk - 273.15), 1) tk
FROM
  tb_grid_4326_100m a
LEFT JOIN (
  SELECT tk, geom FROM tb_points WHERE hour = '23'
) b ON st_contains(a.geom, b.geom)
GROUP BY a.geom

5. 更新表统计信息 #

确保PostgreSQL拥有最新统计信息，以便生成最优执行计划：

ANALYZE tb_grid_4326_100m;
ANALYZE tb_points;

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