Thread: why update is slower on my pc?

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?

On 2010/12/26, at 21:35, sunpeng <bluevaley@gmail.com> wrote:
> 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? 

Are those PostgreSQL versions and/or configurations completely same?
How about shared_buffers, or things like that?

--
NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>

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.

On 2010-12-26, sunpeng <bluevaley@gmail.com> wrote:

> First I wondered whether the write speed on pc is lower than laptop, so i
> use a cp command to test a write speed:

that is often a test of read speed only.
as the writes will be cached.

--
⚂⚃ 100% natural