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?