Postgis ST_Intersects between two tables really (really really) slow

user_2654121 0
Viewed 2657

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 ?

postgis
3 Answers

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));
user_76355690

As the other comment suggests it's better to fix geometries. I guess you use buffer with zero distance to rebuild invalid geometries. So, what is better to do is

UPDATE table1 SET geom = st_buffer(geom,0);
UPDATE table2 SET geom = st_buffer(geom,0);

then reindex the table (rebuild its original gist index by geometry)

Also, depending on the geometries nature the index might be inefficient. An index itself is basically a set of bounding boxes for every geometry (rectangles around polygonal or linear shapes), and it's much faster to determine which rectangles intersect first and then out of the pairs define exact geometry intersections. However, if you have too large polygons where most of rectangles are intersecting and/or too complex shapes it might slow down the join.

Also, you might have data that is heavy in terms of point density which also slows down the algorithms and can be removed without hurting the result much, especially if this data is not very spatially accurate. You can try to simplify your data, for example

ALTER table1 ADD COLUMN geom_simplified geom(<your geom type and srid>);
UPDATE table1 SET geom_simplified = st_snaptogrid(geom,<relevant rounding number depending on your srid and desired accuracy>);

check this out

user_35485190

Thank you @jgh for your answer, you've found the problem. The index was not used. I would have never found it as it was obvious for me that even with the buffer the index was used.

I've tried to clean the data but there are to many errors. So I've decided to use your solution (index on ST_Buffer())

My query takes now 14 hours. It is still a lot of time regarding the number of features in the table 2 but at least the query ends...

"Gather  (cost=1000.14..116847.28 rows=397265 width=8) (actual time=70.548..51214359.966 rows=415151 loops=1)"
"  Workers Planned: 3"
"  Workers Launched: 3"
"  ->  Nested Loop Left Join  (cost=0.14..76120.78 rows=128150 width=8) (actual time=1312.989..51210248.284 rows=103788 loops=4)"
"        ->  Parallel Seq Scan on table1  (cost=0.00..21964.50 rows=128150 width=338) (actual time=0.009..3485.290 rows=99316 loops=4)"
"        ->  Index Scan using table2_idx_buffer on table2  (cost=0.14..0.41 rows=1 width=714516) (actual time=460.535..515.510 rows=0 loops=397265)"
"              Index Cond: (st_buffer(table1.geom, '0'::double precision) && st_buffer(geom, '0'::double precision))"
"              Filter: _st_intersects(st_buffer(table1.geom, '0'::double precision), st_buffer(geom, '0'::double precision))"
"              Rows Removed by Filter: 3"
"Planning time: 0.227 ms"
"Execution time: 51214434.490 ms"
user_26541210
Related