Fixing performance problem in PostGIS ST_Intersects

Question

I am new to PostGIS and I have a problem in query performance.

This my query:

SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1 
WHERE ST_Intersects ( ST_GeomFromText('a multiypolygon geom goes here',4326),position) 
ORDER BY userid, timestamp desc

and the problem is my multipolygon include VERY large polygons (600 pages long in word doc!) and it took more than 2 hours to execute!

Is there a way to optimize my query or use another way?

Answer 1

What you should do is put your big multipolygon in a table as single polygons (with ST_Dump) and put an index on it. Something like:

CREATE TABLE big_polygon as
SELECT (ST_Dump( ST_GeomFromText('a multiypolygon geom goes here',4326))).geom as geom;

-- It is always great to put a primary key on the table
ALTER table big_polygon ADD Column gid serial PRIMARY KEY;

-- Create the index
CREATE INDEX idx_big_polygon_geom
on big_polygon
USING gist(geom);

-- To give the database some information about how the index looks
analyze big_polygon;

-- Then you go:
SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1, big polygon WHERE ST_Intersects ( big_polygon.geom,position) 
ORDER BY userid, timestamp desc;

That should be way faster for several reasons.

Answer 2

It depends what kind of quality - precision you need. You can obviously simplify the polygons by using: http://postgis.net/docs/ST_Simplify.html

What I did often during developement of my GIS application was to think about the best way to minimize data. Eg. preselect the polygons within the boundary box for example. Depending on zoomlevel you don't need ultra precise results (st_simplify) so on.

Answer 3

Depending on your postgres and/or sql expertise you have several options:

1. analyze the query through the EXPLAIN command to find out if you're hitting a particular bottleneck. Warning: sometimes the output of EXPLAIN can be difficult to understand

2. if you expect that most or a significant portion of the geometries in table1 do NOT intersect the multipolygon you could try to apply a preliminary condition against a simpler polygon (i.e. by breaking the multiploygon in smaller pieces) and then run the heavier multipolygon intersection only on those results. See below for an example.

3. if and only if CPU is the bottleneck (i.e. the server is stuck computing intersections) I dully suggest you get a bigger, faster, more powerful CPU or rent a one-time High-CPU Instance off Amazon's EC2 and destroy it when you're done

Example query for item 2:

SELECT DISTINCT ON (st1.userid) st1.userid ,ST_AsText(st1.position), st1.timestamp  
FROM (
    select userid, position, timestamp from table1 
    WHERE ST_Intersects ( YOUR_MULTIPOL_BOUNDS_HERE,position)
) as st1 
WHERE ST_Intersects ( ST_GeomFromText('a multiypolygon geom goes     here',4326),st1.position) 
ORDER BY st1.userid, st1.timestamp desc

To improve performance you could also temporarily materialize subselect st1 as a table so that you can index it.

@Nicklas is right to point out in the comments that example for suggestion 2 should not help. He is right, but I think I'm (partly) right too.

In fact it seems a very similar question was asked (and answered) just last November on the postgis ML:

http://postgis.refractions.net/pipermail/postgis-users/2011-November/031344.html

and turns out the suggestion is to actually break up the polygon so that the index can most effectively filter out false intersections that would be otherwise triggered by a simple boundary check.

Answer 4

Using ST_SubDivide()

For version 2.2 of Postgis, you can use ST_SubDivide.

ST_Subdivide — Returns a set of geometry where no geometry in the set has more than the specified number of vertices.

setof geometry ST_Subdivide(geometry geom, integer max_vertices=256);

You can also

• use a temp table
• an index

Here we use ST_SubDivide to break down the polygon into subpolygons with 10 or fewer vertices.

CREATE TEMP TABLE divided AS
SELECT ST_SubDivide(bigmultipolygon,10)::geometery AS t(geom);

CREATE INDEX divided_idx ON divided USING gist(geom);

Then

SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1
JOIN divided AS d
  ON ST_Intersects( d.geom, position )
ORDER BY userid, timestamp desc;

Do not do the above, it introduces rounding errors

General Tuning

Also look at the section entitled Performance Tips in the docs. Make sure you're tuned appropriately. Consider raising max_parallel_workers_per_gather to take advantage of parallelization (currently defaults to off).