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?


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.

| improve this answer | |
  • Thanks Nicklas for this great reply. Sorry I missed to mention that I have more than one polygons and they are already stored in a table with index. But I tought provide the geom data directly would be faster. However, I try the way you suggest but it's still take very long time! any other suggestion? – Sara Feb 6 '12 at 12:23
  • @Sara . Ok, so you did try to split the multigeoemtries into single geometries as I suggest with ST_Dump? – Nicklas Avén Feb 6 '12 at 12:41
  • How many user positions are we talking about? How many big polygons? What do you get from SELECT ST_npoints(geom) from big_polygons_table;? – Nicklas Avén Feb 6 '12 at 12:42
  • Sorry my bad,let me explain more about my tables to make it more clear for you: I have table1 which includes a geom column that have about 230 rows and in each row there is a multipolygon(they represent countries so they varies in size) ,and have index in the_geom col . Table2 which includes position column (points) ,timestamp ,userid and id(pk) and 3 indexs created using(position, timestamp, userid) .this table is very large about 103496003 rows Tha max number of ST_npoints is 1440430 and the min number is 16. I'm sorry if I made you confuse but I really need your help! Thanks – Sara Feb 7 '12 at 6:56

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.

| improve this answer | |
  • Thanks Martin for your quick response. My problem is I need the result to be very accurate so I think this function will not help me here! but Thanks for the suggestion – Sara Feb 6 '12 at 12:27

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  
    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:


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.

| improve this answer | |
  • suggestion 2 should not help because that is exactly what the index is doing. So that construct will be just doing the same once again. – Nicklas Avén Feb 6 '12 at 13:54
  • @NicklasAvén you're right, I amended the answer – unicoletti Feb 6 '12 at 14:29

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.

SELECT ST_SubDivide(bigmultipolygon,10)::geometery AS t(geom);

CREATE INDEX divided_idx ON divided USING gist(geom);


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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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