On Sun, Dec 26, 2010 at 5:35 AM, sunpeng <bluevaley@gmail.com> wrote:
> I use my laptop to execute the following sql, it's:
> mydb=# update _mcir_2597431_clusterid2 set clusterid = 3;
> UPDATE 104770
> Time: 8666.447 ms
>
> and on my pc:
> mydb=# update _mcir_2597431_clusterid2 set clusterid = 3;
> UPDATE 104770
> Time: 27171.203 ms
>
> First I wondered whether the write speed on pc is lower than laptop, so i
> use a cp command to test a write speed:
> on my laptop:
> postgres@postgres-laptop:~$ time cp backup/soft/data\ mining/SPSS\
> Clementine\ v11.rar develop/
>
> real 0m19.403s
> user 0m0.032s
> sys 0m3.472s
>
> on my pc:
> postgres@postgres-laptop:~$ time cp backup/soft/data\ mining/SPSS\
> Clementine\ v11.rar develop/
>
> real 0m9.192s
> user 0m0.008s
> sys 0m1.708s
>
> so the writing speed on disk of pc is much faster than laptop, why the
> update sql command is much slower than my laptop? what's the reason causing
> such decrease?
Each time you run this update you increase the number of dead tuples
by the number of rows updated. If you do it faster than vacuum can
keep up you'll have a bloated data store. Try clustering that table
on your PC and see if the updates are faster then.