为何重复添加ST_Intersects条件至WHERE子句可大幅提升PostGIS空间JOIN查询性能？

嘿，这个问题真的很有代表性！我来帮你拆解下为什么重复写ST_Intersects(a.geom, b.geom)会带来这么夸张的性能提升——核心原因在于PostgreSQL查询优化器对JOIN子句中的空间条件和WHERE子句中的空间条件的处理逻辑差异，再结合你的表结构（分区表、索引配置）放大了这个效果。

你的查询场景回顾 #

慢查询（仅JOIN ON含空间条件）

EXPLAIN SELECT DISTINCT b.gid, b.geom, l.other_id 
FROM link_table as l 
JOIN geom_table_1 as a ON a.gid = l.gid 
JOIN geom_table_2 AS b ON ST_Intersects(a.geom, b.geom)

快查询（重复添加空间条件至WHERE）

EXPLAIN SELECT DISTINCT b.gid, b.geom, l.other_id 
FROM link_table as l 
JOIN geom_table_1 as a ON a.gid = l.gid 
JOIN geom_table_2 AS b ON ST_Intersects(a.geom, b.geom) 
WHERE ST_Intersects(a.geom, b.geom)

一、优化器对JOIN ON vs WHERE条件的执行策略差异 #

PostgreSQL的查询优化器在处理JOIN连接条件和WHERE过滤条件时，优先级和执行顺序是不同的：

• 当你把ST_Intersects(a.geom, b.geom)只写在JOIN的ON子句中时，优化器可能会先执行link_table和geom_table_1的连接，然后再尝试将结果与geom_table_2做笛卡尔积式的连接，之后才用空间条件过滤不相交的行。这个过程会产生海量中间数据，从你的慢查询执行计划就能看出来：预估行数高达924万，实际处理了近18.5万行，最后还要做全局排序去重，成本直接飙升到3e11。
• 而当你把同样的空间条件重复写到WHERE子句中时，相当于给优化器一个明确的信号：先过滤掉geom_table_2中不与geom_table_1相交的行，再做连接。此时优化器会优先触发geom_table_2（你的geo_parcelle表）上的GIST空间索引geo_parcelle_the_geom_idx，提前过滤掉绝大多数不满足条件的空间数据，大幅减少后续连接的数据量。

二、分区表的分区裁剪效果被激活 #

你的geom_table_1（bati_indifferencie）和geom_table_2（geo_parcelle）都是按法国区域拆分的分区父表，这一点是关键：

• 当空间条件在WHERE子句中时，优化器能够识别出可以进行分区裁剪——也就是说，它会针对geom_table_1的每个子分区（比如a_74），只扫描geom_table_2中对应区域的子分区，而不是遍历所有50多个区域子分区。
• 但如果空间条件只在JOIN ON子句中，优化器往往无法有效推导分区裁剪的规则，只能扫描geom_table_2的所有子分区，这会导致扫描的数据量暴增，直接拖慢整个查询。

三、关于你看到的"UNIQUE"元素 #

你提到的"UNIQUE"其实是执行计划中的Unique步骤——这个步骤是用来处理你查询中的DISTINCT关键字，去除重复行。

• 慢查询中，Unique需要处理的是连接后海量的中间数据（预估924万行），所以排序和去重的成本极高；
• 快查询中，由于WHERE条件提前过滤了大部分数据，Unique只需要处理少量符合条件的行（实际仅18万多行），成本自然大幅降低。

涉及表的DDL参考 #

link_table（passage_bati_uf）

CREATE TABLE IF NOT EXISTS staski.passage_bati_uf (
 site_id smallint,
 dist double precision,
 bat_isole boolean,
 gid character varying COLLATE pg_catalog."default"
);

-- 索引
CREATE INDEX IF NOT EXISTS passage_bati_uf_dist_idx 
ON staski.passage_bati_uf USING btree (dist ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS passage_bati_uf_gid_idx 
ON staski.passage_bati_uf USING btree (gid COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS passage_bati_uf_gid_site_id_midx 
ON staski.passage_bati_uf USING btree (gid COLLATE pg_catalog."default" ASC NULLS LAST, site_id ASC NULLS LAST);

geom_table_1（bati_indifferencie，分区父表）

CREATE TABLE IF NOT EXISTS bd_topo.bati_indifferencie (
 gid character varying COLLATE pg_catalog."default" NOT NULL,
 id character varying(24) COLLATE pg_catalog."default" NOT NULL,
 prec_plani numeric(6,1) NOT NULL,
 prec_alti numeric(7,1) NOT NULL,
 origin_bat character varying(8) COLLATE pg_catalog."default" DEFAULT 'NR'::character varying,
 hauteur integer NOT NULL,
 z_min double precision,
 z_max double precision,
 the_geom geometry,
 CONSTRAINT bati_indifferencie_pkey PRIMARY KEY (gid),
 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 3),
 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2154)
);

geom_table_2（geo_parcelle，分区父表）

CREATE TABLE IF NOT EXISTS bd_parcellaire.geo_parcelle (
 gid integer NOT NULL DEFAULT nextval('bd_parcellaire.geo_parcelle_gid_seq'::regclass),
 numero character varying(4) COLLATE pg_catalog."default",
 feuille smallint,
 section character varying(2) COLLATE pg_catalog."default",
 code_dep character varying(2) COLLATE pg_catalog."default",
 nom_com character varying(45) COLLATE pg_catalog."default",
 code_com character varying(3) COLLATE pg_catalog."default",
 com_abs character varying(3) COLLATE pg_catalog."default",
 code_arr character varying(3) COLLATE pg_catalog."default",
 the_geom geometry(MultiPolygon,2154),
 CONSTRAINT geo_parcelle_pkey PRIMARY KEY (gid)
);

-- 空间索引
CREATE INDEX IF NOT EXISTS geo_parcelle_the_geom_idx 
ON bd_parcellaire.geo_parcelle USING gist (the_geom);

慢查询执行计划（部分） #

Unique (cost=315279149407.35..315281444157.89 rows=9240000 width=291) (actual time=487573.943..488067.948 rows=184743 loops=1)
Output: dp.gid, dp.the_geom, p.site_id
Buffers: shared hit=515186515 read=388231, temp read=18219 written=34371
-> Gather Merge (cost=315279149407.35..315281305557.89 rows=18480000 width=291) (actual time=487573.941..487982.308 rows=184900 loops=1)
      Output: dp.gid, dp.the_geom, p.site_id
      Workers Planned: 2
      Workers Launched: 2
      Buffers: shared hit=515186366 read=388231, temp read=18219 written=34371
      -> Sort (cost=315279148407.33..315279171507.33 rows=9240000 width=291) (actual time=487455.200..487473.956 rows=61633 loops=3)
            Output: dp.gid, dp.the_geom, p.site_id
            Sort Key: dp.gid, dp.the_geom, p.site_id
            Sort Method: external merge Disk: 27952kB
            Buffers: shared hit=515186366 read=388231, temp read=18219 written=34371
            Worker 0: actual time=487484.393..487510.658 rows=79737 loops=1
                  Sort Method: external merge Disk: 26536kB
                  JIT: Functions: 432
                  Options: Inlining true, Optimization true, Expressions true, Deforming true
                  Timing: Generation 32.410 ms (Deform 23.525 ms), Inlining 94.116 ms, Optimization 4892.988 ms, Emission 3141.103 ms, Total 8160.616 ms
                  Buffers: shared hit=165994321 read=90495, temp read=8195 written=14450
            Worker 1: actual time=487346.270..487352.166 rows=13700

验证建议 #

你可以对比快查询的执行计划，应该能看到以下变化：

• 优化器先对geo_parcelle执行Index Scan using geo_parcelle_the_geom_idx（利用空间索引过滤）；
• 执行计划中的分区扫描行数大幅减少（只扫描相关区域的子分区）；
• 中间连接的行数和排序成本远低于慢查询。

内容来源于stack exchange