Using st_intersects between tables in PostGIS?

Question

I have two tables:

filter_polygon

and

myGeometries

In both tables I have geometry columns. myGeometries contains > 100 mln of rows.

In both tables I have geo indexes like:

CREATE INDEX filter_polygon_ix2
    ON filter_polygon.filter_polygon USING gist
    (geom)
    TABLESPACE pg_default;

Let's say that I have id of one polygon from filter_polygon table and would like to take all records from myGeometries table that intersect with it. My query looks like that so far:

select * from 
filter_polygon.filter_polygon AS Filter,
myGeometries AS myGeoms
where
Filter.id = '0101' 
AND
ST_Intersects(myGeoms.geometri, Filter.geom);

And the quey plan for it is:

'Nested Loop  (cost=261.66..25164.63 rows=1934865 width=251228)'
'  ->  Seq Scan on filter_polygon filter  (cost=0.00..5.24 rows=1 width=248951)'
'        Filter: ((origin_id_fieldvalue)::text = '0101'::text)'
'  ->  Bitmap Heap Scan on myGeometries  (cost=261.66..25139.51 rows=1989 width=2277)'
'        Recheck Cond: (geometri && filter.geom)'
'        Filter: (st_intersects(geometri, filter.geom))'
'        ->  Bitmap Index Scan on myGeometries_idx1  (cost=0.00..261.17 rows=5966 width=0)'
'              Index Cond: (geometri && filter.geom)'

The problem is that it takes like 20 min to get a result, sometimes even more when filter_polygon is complex and covers relatively big area.

Is there a way to improve that performance?

postgisVersion:

'POSTGIS="2.1.8 r13780" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER'

PostgreSQL Version:

'PostgreSQL 9.4.18 on x86_64-unknown-linux-gnu (Ubuntu 9.4.18-2.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit'

Answer 1

It looks like you need to index your id, its doing a sequential scan which can be very slow. Try indexing us the B-tree index type:

CREATE INDEX filter_polygon_id
ON filter_polygon.filter_polygon(id)

Also, I was running Postgresql 9.5 as you are and I recently upgraded to 10.5, the execution plans that the PostgreSQL 10.5 planner generates are superior to 9.5. So it might also be worth upgrading to improve performance.

Answer 2

The GIST spatial indexes use bounding boxes to do a first screening of the intersection. Thus, if filter_polygon is complex and covers a large area, this means that its bounding box is likely to intersect many of the geometries in myGeometries, even if the actual geometries do not intersect.

Splitting your polygons into more regular (i.e. close to rectangular) shapes will improve the performance of your spatial index. If you have multipolygons, use ST_Dump to convert them to polygons.

A secondary issue: your SELECT * query forces postgres to return the geometries as well as all the other columns, which can be slow. If you need a subset of columns, then only ask for those specifically. You could test how much this is slowing you down by changing the first line of your query to SELECT COUNT(*) FROM.