Indexing a PostgreSQL query using a geometry field (gist) but it doesn’t seem to be working

Question

I remembered to remove and recreate the index to eliminate the possibility that anything changed

gidrop index if exists incidents;
create index idx_geo on incidents using gist(geo);

Then this:

vacuum analyze incidents;

Here's the query that is still O(n^2) and impossibly slow. Note that I ran it with and without enable_seqscan. No difference.

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

You are using a correlated query, meaning a query that must scan a table and within it use another table.

You are also creating a temporary table inside the first from clause, that by its nature has no index and cannot be optimized.

It's only natural that this query would run extremely slowly.

I suggest rewording the select to avoid using these slow SQL constructs. SQL is not very strong on query optimization, except for rather simple cases. Keep it very simple and ensure having indexes for direct access rather than causing sequential scans of the table.

(Note: Please do not ask us to formulate the query.)