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'
VACUUM ANALYZE <table>
on both tables. a table that large can also benefit from clusteringEXPLAIN (ANALYZE, BUFFERS) sqlquery
, it might reveal other issues (memory etc)