3

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'
3
  • 2
    run VACUUM ANALYZE <table> on both tables. a table that large can also benefit from clustering
    – geozelot
    Oct 20, 2018 at 10:58
  • 2
    Clustering using a geohash could work well. Here is a guide to this: postgis.net/workshops/postgis-intro/clusterindex.html
    – Trashmonk
    Oct 20, 2018 at 11:10
  • try running EXPLAIN (ANALYZE, BUFFERS) sqlquery, it might reveal other issues (memory etc)
    – JGH
    Oct 22, 2018 at 12:23

2 Answers 2

2

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.

1
  • Unfortunately it didn't help. filter_polygon table contains only 90 rows.
    – Witos
    Oct 20, 2018 at 10:49
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.