Search code examples
postgis

Postgis ST_Intersects between two tables really (really really) slow

I'm trying to do a spatial join between two tables :

  1. Table 1 : 397265 features (with gist index on geom field)
  2. Table 2 : 73 features (with gist index on geom field)

Table 1 and 2 have the same SRID.

I did many tests and a spatial join between these tables take several days (after 5 days I've killed the query). I've tried to understand why it is so slow with explain analyze :

EXPLAIN ANALYZE 
SELECT 
    table1.id,  table2.id
FROM table1
INNER JOIN table2 ON ST_Intersects(
        ST_Buffer(table1.geom,0),
        ST_Buffer(table2.geom,0)
)

But for now I'm still waiting for the result as EXPLAIN ANALYZE really execute the query.

If I only do an EXPLAIN here is the result :

"Gather  (cost=1000.00..3820127.08 rows=9667 width=40)"
"  Workers Planned: 3"
"  ->  Nested Loop  (cost=0.00..3818111.26 rows=3118 width=40)"
"        Join Filter: ((st_buffer(table2.geom, '0'::double precision) && st_buffer(table1.geom, '0'::double precision)) AND _st_intersects(st_buffer(table2.geom, '0'::double precision), st_buffer(table1.geom, '0'::double precision)))"
"        ->  Parallel Seq Scan on table1  (cost=0.00..21964.50 rows=128150 width=344)"
"        ->  Seq Scan on table2  (cost=0.00..9.73 rows=73 width=714516)"

I've also updated my postgresql configuration file regarding my computer's configurarion :

shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 7
max_parallel_workers_per_gather = 4
max_parallel_workers = 7

Could you please tell me what it is so long ?

Solution

  • The spatial indexes are not used.

    You can either drop the buffer thing (maybe fix your geometries first..)

    INNER JOIN table2 ON ST_Intersects(table1.geom,table2.geom)

    or you can create indexes on the buffered geometries

    CREATE INDEX geom_idx ON table1 USING gist (ST_Buffer(table1.geom,0));