Re: Update table is much faster after copying it

От:

Andreas Kretschmer <akretschmer@spamfence.net>

Дата:

21 января 2015 г. в 06:14:14

Miles Jordan  wrote:

> 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

there are 2 possible reasons: caching and maybe a lot of dead tuples
within the original table. Please check the on-disk-size of table a and
b.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Update table is much faster after copying it

От:

Miles Jordan <miles.jordan@rea-group.com>

Дата:

21 января 2015 г. в 03:56:30

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