I have two tables:
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?
'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 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'