Timeline for PostGIS- slow spatial UPDATE queries on 50mil rows
Current License: CC BY-SA 4.0
22 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 10, 2021 at 15:00 | history | tweeted | twitter.com/StackGIS/status/1359517487990194179 | ||
| Feb 10, 2021 at 11:07 | history | edited | Theo F | CC BY-SA 4.0 |
added 119 characters in body
|
| Feb 10, 2021 at 9:38 | comment | added | geozelot |
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.
|
|
| Feb 10, 2021 at 7:07 | comment | added | Leon Powałka | 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/… | |
| Feb 10, 2021 at 0:49 | comment | added | Theo F | @LeonPowałka you mention changing some WAL parameters, but did you increase or decrease these settings? | |
| Feb 10, 2021 at 0:05 | comment | added | Theo F | @geozelot I can't work on the fly, as the whole table will eventually be exported whole to a flat file for delivery to a client. re: spatial index, if I skip that part in the process, each update query that could take seconds, takes minutes. | |
| Feb 10, 2021 at 0:02 | comment | added | Theo F | @bugmenot123 Have updated the question with specs. I don't EXPLAIN ANALYZE as that executes the query. I can try BEGIN; EXPLAIN ANALYZE query; ROLLBACK; | |
| Feb 9, 2021 at 23:58 | comment | added | Theo F | @DPSSpatial yes any other table I query in the UPDATE statements will have a spatial index. I make sure every other table used works well. | |
| Feb 9, 2021 at 23:57 | comment | added | Theo F | @Mapperz I've updated the question to show the specs. But it's hosted on and AWS RDS, which gives decent performance under other operations. I don't run ANALYZE after each UPDATE no. | |
| Feb 9, 2021 at 23:55 | history | edited | Theo F | CC BY-SA 4.0 |
added 45 characters in body
|
| Feb 9, 2021 at 23:49 | history | edited | Theo F | CC BY-SA 4.0 |
added 45 characters in body
|
| Feb 9, 2021 at 23:17 | answer | added | geozelot | timeline score: 2 | |
| Feb 9, 2021 at 22:40 | comment | added | geozelot |
It's not uncommon to create a new table and drop the old when it reached a size unfavorable for an UPDATE. On another note: it may be worth to think about making use of the relational model of the DB; rather than updating that table with inherently present information, query the data you are interested in, and do in situ spatial relation checks on-the-fly.
|
|
| Feb 9, 2021 at 22:35 | answer | added | Leon Powałka | timeline score: 2 | |
| Feb 9, 2021 at 22:34 | comment | added | geozelot |
UPDATES are, in comparison, very intense operations, as the (here whole) table will be set into a transitional state (changeset loaded into memory to allow for ROLLBACK). Since you are not writing to any index, or a column used in a later update, ANALYZE in between runs won't have any effect. Tweak the settings as suggested, and make sure your system has enough ressources to keep the table plus index of the currently joined table in memory. Note that you don't need a spatial index on the base table, as only those on the joined tables will be used.
|
|
| Feb 9, 2021 at 22:16 | comment | added | Leon Powałka | @ziggy Cluster by index won't help as there are no rows to fetch in this case. It would help in a select query. | |
| Feb 9, 2021 at 22:15 | comment | added | bugmenot123 | How is your RAM doing? Did you pgtune.leopard.in.ua? What does EXPLAIN ANALYZE say? Are the columns you update free of indexes? | |
| Feb 9, 2021 at 22:07 | comment | added | ziggy | maybe try and cluster the index? | |
| Feb 9, 2021 at 22:03 | comment | added | Leon Powałka | 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. | |
| Feb 9, 2021 at 21:50 | comment | added | Inactivated Account | Maybe a dumb question, but is there a spatial index on the polygon table too? | |
| Feb 9, 2021 at 21:32 | comment | added | Mapperz♦ | 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/… | |
| Feb 9, 2021 at 20:59 | history | asked | Theo F | CC BY-SA 4.0 |