我记得删除并重新创建索引,以消除任何变化的可能性
gidrop index if exists incidents;
create index idx_geo on incidents using gist(geo);
然后是这个:
vacuum analyze incidents;
以下查询仍然是 O(n^2),而且速度慢得令人难以置信。请注意,我在使用和不使用 enable_seqscan 的情况下运行了该查询。没有区别。
SET enable_seqscan TO off;
drop table if exists BSC;
create table BSC(
most_recent_id int not null,
incident_id int not null
);
insert into BSC(most_recent_id, incident_id)
select *
from (
select
(select max(id)
from incidents i2
where i2.geo_mesh && i.geo_mesh
and ST_DWithin(i2.geo_mesh, i.geo_mesh, 0)
and i2.id in (select most_recent_id from temp_unique)
) as most_recent_id,
id as incident_id
from incidents i
where i.id in (select most_recent_id from temp_unique)
) t
where t.most_recent_id <> t.incident_id;
SET enable_seqscan TO on
答案1
您正在使用相关查询,这意味着查询必须扫描一个表并在其中使用另一个表。
您还在第一个子句中创建了一个临时表from
,该临时表本质上没有索引并且无法优化。
这个查询运行得极其缓慢是很自然的。
我建议重新措辞 select 以避免使用这些缓慢的 SQL 结构。除了相当简单的情况外,SQL 在查询优化方面并不是很强大。保持非常简单并确保有索引可以直接访问,而不是导致对表进行顺序扫描。
(注意:请不要要求我们制定查询。)