From: | Miles Jordan <miles(dot)jordan(at)rea-group(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Update table is much faster after copying it |
Date: | 2015-01-21 03:56:09 |
Message-ID: | 6A9D5F7D-393A-431E-A719-E1DA11E97320@rea-group.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2015-01-21 06:14:03 | Re: Update table is much faster after copying it |
Previous Message | deans | 2015-01-21 01:54:13 | Re: BDR Error restarted |