Индексация запроса PostgreSQL с использованием геометрического поля (gist), но, похоже, он не работает

Question

Я вспомнил, чтобы удалить и воссоздать индекс, чтобы исключить вероятность того, что что-то изменилось

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

Answer 1

Вы используете коррелированный запрос, то есть запрос, который должен сканировать таблицу, а внутри нее использовать другую таблицу.

Вы также создаете временную таблицу внутри первого предложения from , которая по своей природе не имеет индекса и не может быть оптимизирована.

Вполне естественно, что этот запрос будет выполняться очень медленно.

Я предлагаю переписать select, чтобы избежать использования этих медленных конструкций SQL. SQL не очень силен в оптимизации запросов, за исключением довольно простых случаев. Сделайте это очень просто и убедитесь, что у вас есть индексы для прямого доступа, а не для последовательного сканирования таблицы.

(Примечание: пожалуйста, не просите нас сформулировать запрос.)