Postgres/PostGIS Spatial Query Performance Problem

Question

I have a query in Postgres/PostGIS that I am a bit confused about. The spatial intersection happens very fast, ~ 2 seconds for 10,000, but the update takes > 15 seconds for the same number of points.

a_table contains ~ 66,000,000 points

b_table contains ~ 3,000 polygons

Both are spatially indexed (gist and clustered) and the b.wanted_value column is indexed as well.

Any suggestions for improving the speed of this query would be sincerely appreciated.

UPDATE a_table a
SET a.needed_value = (
  SELECT b.wanted_value 
  FROM b_table b 
  WHERE ST_Intersects(a.the_geom, b.the_geom)
);

Answer 1

• The more rows that are being updated, the longer it will take to run the update.
• Each time you update it creates a new row. Over time, with lots of updates, things will get slower. Be sure to VACUUM on occasion. This may speed things up a bunch if you've been doing lot of updates with no vacuuming.
• Is a.needed_value indexed as well? If so, it will take a little longer since it may have to recreate the index after the update.

You can run some quick tests on all this: First try update all rows with something like update a_table set a.needed_value = 'foo'; and then create a temp table with just a 10k subset of rows from a_table and try the same thing on it. Note the time difference (probably linear based on # of rows being updated)

Assuming you have a serial primary key on there, you could also then test your spatial query is not the bottleneck, by trying something like update a_table set a.needed_value = 'foo' where serial_id < 10000; and not see much of a difference between that and your spatial query.

Answer 2

Hmm, this is not supposed to work at all in PostgreSQL as I understand it. The inner sub- select part of the query doesn't have access to the outer a_table. You can read about that on the bottom of this page. So if you don't get any error messages I guess the database tries to update in some strange way which might mean updating all rows.

What you should do here instead is:

UPDATE a_table
SET a.needed_value = b.wanted_value FROM b_table b 
  WHERE ST_Intersects(table.the_geom, b.the_geom);

You can not have an alias on a_table here. I think it sounds strange that your question works at all so I might very well have missed something here.

But try my suggestion if that helps.

HTH Nicklas

Update:

Ok Peter, you are of course right. I have actually avoided that construction because I have believed it would fail.

But I still think I have a point according to the issue it was all about. When I tried the query construction in the question it seems to behave a little odd. It seems to do a right join and update all records, not affected by the intersection test, with null-values. Maybe it is not a right join but the result seems to be the same. The output from the query is that all rows are affected.

That compared with "my" construction that does nothing with the point-records that doesn't intersect.

In this case when the point-table carries over 60 million rows I guess that might mean a significant difference in performance. Can that be the case?