3

PostgreSQL 11.8, AWS hosted RDS instance, SSD, 8GB RAM, 3.0 GHz Intel Scalable Processor. Plenty of storage space.

I'm finding running updates on a large table using spatial queries is taking a very long time (>24 hours). How can I speed it up? I looked into HOT updates, but I can't use that as I have an indexed column (the geometry column, which is essential for fast spatial processing)- as I understand it, HOT updates don't work if any column is indexed.

Here is my process:

I create a new table of 50 million points as a copy of another table (I don't want to alter the original).

CREATE TABLE schema.table AS
SELECT id, geom 
FROM schema.orig_table;

I add about 40 new columns which will store '1's where a spatial condition is true. This doesn't have to be a 1 though, I may need the number to be a 2,3,4 etc.

ALTER TABLE schema.table 
ADD COLUMN col1 integer DEFAULT 0,
ADD COLUMN col2 integer DEFAULT 0,
ADD COLUMN col3 integer DEFAULT 0, etc etc

I set the id column as the primary key.

ALTER TABLE schema.table ADD PRIMARY KEY (id);

I assign a geometry type to the geometry column.

ALTER TABLE schema.table 
ALTER COLUMN geom TYPE geometry(POINT,27700) USING ST_SetSRID(geom,27700);

I add a spatial index on the geometry column.

CREATE INDEX table_gix ON schema.table USING GIST(geom);

From here on, I run multiple UPDATE queries on the table, setting one of the 40 columns with the value 1 (or 2/3/4... etc I decide on a query by query basis) where the point intersects another table (I have a load of geospatial tables representing various polygons, points, line features. All spatially indexed and working correctly).

UPDATE schema.table a
SET col1 = 1
FROM schema.polygons b
WHERE ST_INTERSECTS(a.geom, b.geom);

...

UPDATE schema.table a
SET col29 = 1
FROM schema.another_polygon_table b
WHERE ST_INTERSECTS(a.geom, b.geom);

etc etc.... x200

Another 200 of these UPDATE queries occur. All the other geospatial tables used in the query are clean, fast, and without geometry errors. And most of the time, these ST_INTERSECTS queries don't find anything, so the value in the table stays at 0. The problem is, some of these queries can take up to 1 hour each. So 200 of these? Causes a headache.

The time taken for the whole script to run can take 24 hours. But putting a LIMIT 1000000 as a test, shows that it can run quickly on smaller sample sizes. It also shows that the overall script time grows exponentially with the amount of rows updated in the table.

How can I speed up this whole process? What's the most efficient way of running 100s of spatial UPDATE queries on a table of 50 million rows?

15
  • What is the database set up on? Linux/Windows, solid-state disks? When your table changes are you using ANALYZE my_table? gis.stackexchange.com/questions/197773/… – Mapperz Feb 9 at 21:32
  • Maybe a dumb question, but is there a spatial index on the polygon table too? – DPSSpatial Feb 9 at 21:50
  • 1
    I had quite a similar case. What helped me slightly speed up the updates were some changes in PostgreSQL parameters regarding WAL. I changed checkpoint timeout, max_wal_size, checkpoint_completion_target. The increase in performance was noticeable but not drastic and I just accepted that a sequential full update of a big table just has to take some time. – Leon Powałka Feb 9 at 22:03
  • 1
    Every parameter is a bit different: postgresql.org/docs/9.5/wal-configuration.html. You will probably want to increase max_wal_size, checkpoint_timeout, checkpoint_completion_target. By how much - read more about them first not to mess something up. info.crunchydata.com/blog/… – Leon Powałka Feb 10 at 7:07
  • 1
    That is actually a good metric to know without running an EXPLAIN ANALYZE: if dropping the index from the base table results in slower updates, the planer chooses to create a full hash join table between both participating relations. Run a SELECT COUNT(*) FROM ... JOIN ... ON ST_Intersects to see the row count; you want as much memory available as the size of that hash table, plus the index size on the base table, to avoid costly temporary disk storage. This also means a forced sequential update (as per Leons suggestion) will be slower. This may also change with different LIMITs. – geozelot Feb 10 at 9:38
1

Since you are not writing to any indexed column, you can allow for HOT updates, which indeed may have a significant effect: positive for UPDATE performance, negative for reserved disk space for the relation.

Rewrite your table with a modified fillfactor to leave room per page for the transition row tuples:

ALTER TABLE schema.table
  SET (fillfactor = 49)
;

VACUUM FULL;    -- physically rewrites data on disc

I use 49 (% of page size) here to make sure each row can be duplicated on the same page during an UPDATE; this low a value may or may not be necessary, but should allow to get HOT updates in most cases.

You can rewrite the table to the default fillfactor = 100 after that if you do not plan to UPDATE often...but a disk rewrite will take quite some time.

2
  • thanks for the suggestion, I actually did try using fillfactor after posting the question and I ran some tests. When using a LIMIT 2000000 in the CREATE TABLE statement, I then found that ALTER TABLE schema.table SET (fillfactor = 75) reduces the non-fillfactored time by -130%! (38mins down to 16mins). I need to increase the LIMIT to see if this works on more rows... fyi fillfactor of 50 isn't quite a big reduction as fillfactor 75 from my tests. – Theo F Feb 10 at 0:12
  • 1
    @TheoF fillfactor defines the percentage up to which a page will get filled when INSERTing (100 is the default); 75 (%) will leave 25% free to then get used by an UPDATE, while 49 (%) leaves 51% free per page. Since you know that every row gets updated in the process, you need to make sure every one of them can temporarily get duplicated on the same page -> you need as much free space as there is data per page -> fillfactor <= 50. – geozelot Feb 10 at 8:05
0

If you have all the necessary indexes (and not more than necessary) then you can also try to modify PostgreSQL WAL parameters. wal_buffers, max_wal_size, checkpoint_timeout, checkpoint_completion_target could be significant when it comes to performance of big updates.

I also have another idea, honestly don't know if it will be faster. You have to test it yourself. Maybe try writing the update like that:

UPDATE schema.table a
SET col1 = exists(select null from schema.polygon_table b where st_intersects(a.geom, b.geom))::int,
    col2 = exists(select null from schema.another_polygon_table b where st_intersects(a.geom, b.geom))::int
--etc...

This way there is only one sequential iteration of the entire 50 million record table as opposed to N iterations.

3
  • thanks I will try that single iteration statement. Would that populate the columns with 1s in that example? What if I wanted to set some custom integers for some of the UPDATEs, like '50', or '95' for instance...? – Theo F Feb 10 at 0:16
  • The boolean result is casted to int so it matches the logic you explained in the post. 1 for true and 0 for false. – Leon Powałka Feb 10 at 7:13
  • 1
    If you want a custom value then add case when exists (...) then – Leon Powałka Feb 10 at 7:14

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.