Hi, I have tables a and b each with around 12 million rows. I am running an update query to set the id of table b as an attribute of table a, and have an appropriate index on table b that EXPLAIN tells me should be used.
The query takes 6 hours on an AWS db.r3.4xlarge. That seemed hugely excessive, so I copied table a into a new table using CREATE TABLE b AS SELECT * FROM a, and for completeness also added the same indexes and constraints. Now, when I run the update on table a, it finishes in 3 minutes, and produces the same result. Can anyone shed some light on why this might happen? I’ve played around with using CLUSTER but it doesn’t seem to have any effect, and I don’t really see why it should given that table a needs a sequential scan to update every row anyway. Thanks for your insights. -Miles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
