From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: about multiprocessingmassdata
Date: Wed, 04 Apr 2012 18:34:47 +0200
Message-ID: <4F7C7827.7080705@fuzzy.cz> (raw)
In-Reply-To: <2012040423524938620013@gmail.com>
References: <2012040423524938620013@gmail.com>
On 4.4.2012 17:52, superman0920 wrote:
> Hi list!
>
> i have a table which has 8500000 rows records. i write a java program to
> update these records.
> i use 100 threads to update the records. For example, thread-1 update
> 1~85000 records; thread-2 update 85001~170000 and so on.
> The update sql's aim is remove the space in the column and it is simple:
> update poi set py=replace(py,' ','') where id=?;
That's a very naive approach. It's very likely each thread will do an
index scan for each update (to evaluate the 'id=?' condition. And that's
going to cost you much more than you gain because index scans are quite
CPU and I/O intensive.
Simply update the whole table by
UPDATE poi SET py = replace(py, ' ','');
Have you actually tried how this performs or did you guess 'it's
definitely going to be very slow so I'll use multiple threads to make
that faster'?
If you really need to parallelize this, you need to do that differently
- e.g. use 'ctid' to skip to update a whole page like this:
UPDATE poi SET py = replace(py, ' ','')
WHERE ctid >= '(n,0)'::tid AND ctid < '(n+1,0)'::tid AND;
where 'n' ranges between 0 and number of pages the table (e.g. in pg_class).
But try the simple UPDATE first, my guess is it's going to be much
faster than you expect.
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: about multiprocessingmassdata
In-Reply-To: <4F7C7827.7080705@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox