postgresql - ST_DWITHIN 不使用 GIST 或 BRIN 索引
问题描述
我正在使用 postgis 函数 ST_DWithin(geography gg1, geography gg2, double precision distance_meters) 来查找一个点是否在距多边形的指定距离内。我正在运行测试以查看查询需要多长时间,并且解释表明它正在对表运行顺序扫描,而不是使用 BRIN 或 GIST 索引。有人可以建议一种优化它的方法。
这是表格 -
带有多边形的 table1(incident_geog)
CREATE TABLE public.incident_geog
(
incident_id integer NOT NULL DEFAULT nextval('incident_geog_incident_id_seq'::regclass),
incident_name character varying(20) COLLATE pg_catalog."default",
incident_span geography(Polygon,4326),
CONSTRAINT incident_geog_pkey PRIMARY KEY (incident_id)
)
CREATE INDEX incident_geog_gix
ON public.incident_geog USING gist
(incident_span)
带有点和距离的表2(watchzones_geog)
CREATE TABLE public.watchzones_geog
(
id integer NOT NULL DEFAULT nextval('watchzones_geog_id_seq'::regclass),
date_created timestamp with time zone DEFAULT now(),
latitude numeric(10,7) DEFAULT NULL::numeric,
longitude numeric(10,7) DEFAULT NULL::numeric,
radius integer,
"position" geography(Point,4326),
CONSTRAINT watchzones_geog_pkey PRIMARY KEY (id)
)
CREATE INDEX watchzones_geog_gix
ON public.watchzones_geog USING gist
("position")
带有 st_dwithin 的 Sql
explain select i.incident_id,wz.id from watchzones_geog wz, incident_geog i where ST_DWithin(position,incident_span,wz.radius * 1000);
解释的输出:
Nested Loop (cost=0.26..418436.69 rows=1 width=8)
-> Seq Scan on watchzones_geog wz (cost=0.00..13408.01 rows=600001 width=40)
-> Index Scan using incident_geog_gix on incident_geog i (cost=0.26..0.67 rows=1 width=292)
Index Cond: (incident_span && _st_expand(wz."position", ((wz.radius * 1000))::double precision))
Filter: ((wz."position" && _st_expand(incident_span, ((wz.radius * 1000))::double precision)) AND _st_dwithin(wz."position", incident_span, ((wz.radius * 1000))::double precision, true))
解决方案
您的 SQL 实际执行的是在每个点的指定距离内找到一些多边形。incident_geog.incident_id
结果和之间一一对应watchzones_geog.id
。因为你对每个点进行操作,所以它使用顺序扫描。
我猜你想从多边形开始找点。因此,您的 SQL 需要更改表。
explain select i.incident_id,wz.id from incident_geog i, watchzones_geog wz where ST_DWithin(position,incident_span,50);
我们可以看到:
Nested Loop (cost=0.27..876.00 rows=1 width=16)
-> Seq Scan on incident_geog i (cost=0.00..22.00 rows=1200 width=40)
-> Index Scan using watchzones_geog_gix on watchzones_geog wz (cost=0.27..0.70 rows=1 width=40)
Index Cond: ("position" && _st_expand(i.incident_span, '50'::double precision))
Filter: ((i.incident_span && _st_expand("position", '50'::double precision)) AND _st_dwithin("position", i.incident_span, '50'::double precision, true))
因为你操作每一个订单,所以总会有一张表,通过顺序扫描来遍历所有的记录。这两个 SQL 的结果并没有什么不同。关键是你从哪个表开始寻找另一个表的顺序。
也许你可以试试Parallel Query
。不要使用Parallel Query
:
SET parallel_tuple_cost TO 0;
explain analyze select i.incident_id,wz.id from incident_geog i, watchzones_geog wz where ST_DWithin(position,incident_span,50);
Nested Loop (cost=0.27..876.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on incident_geog i (cost=0.00..22.00 rows=1200 width=40) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using watchzones_geog_gix on watchzones_geog wz (cost=0.27..0.70 rows=1 width=40) (never executed)
Index Cond: ("position" && _st_expand(i.incident_span, '50'::double precision))
Filter: ((i.incident_span && _st_expand("position", '50'::double precision)) AND _st_dwithin("position", i.incident_span, '50'::double precision, true))
Planning time: 0.125 ms
Execution time: 0.028 ms
尝试Parallel Query
设置parallel_tuple_cost
为 2:
SET parallel_tuple_cost TO 2;
explain analyze select i.incident_id,wz.id from incident_geog i, watchzones_geog wz where ST_DWithin(position,incident_span,50);
Nested Loop (cost=0.27..876.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on incident_geog i (cost=0.00..22.00 rows=1200 width=40) (actual time=0.001..0.001 rows=0 loops=1)
-> Index Scan using watchzones_geog_gix on watchzones_geog wz (cost=0.27..0.70 rows=1 width=40) (never executed)
Index Cond: ("position" && _st_expand(i.incident_span, '50'::double precision))
Filter: ((i.incident_span && _st_expand("position", '50'::double precision)) AND _st_dwithin("position", i.incident_span, '50'::double precision, true))
Planning time: 0.103 ms
Execution time: 0.013 ms
推荐阅读
- spring-boot - Spring Boot request body validation
- apache-spark - Spark SQL:为什么我在 Spark UI 中看到 3 个作业而不是一个作业?
- javascript - 如何将 vuex getter 传递给不是 vuejs 组件的模块,但保持它的反应性?
- .net-core - dotnet Core 中的 PDF 实现 - DinkTOpdf
- visual-studio - 如何在 Visual Studio Blazor Wasm 应用程序调试期间停止 Chrome 缓存
- html - 如何通过向下滚动页面使线性渐变一致
- python - 在 linux 上导入 PIL 的预编译版本时遇到问题
- macos - 使用 ls 命令时,有没有办法在 Mac OS 中隐藏备份文件(即以“~”结尾的文件)?
- html - 使用 jquery 中的 css() 方法检查样式
- excel - 从一行中删除重复项