pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Low Performance for big hospital server ..
Date: Thu, 6 Jan 2005 13:15:33 +0100
Message-ID: <758d5e7f050106041510f07dc5@mail.gmail.com> (raw)
In-Reply-To: <1104939342.41dc094ecb1cf@webmail.moph.go.th>
References: <1104831107.41da6283add0c@webmail.moph.go.th>
	<20050104094657.540232d7.frank@wiles.org>
	<1104939342.41dc094ecb1cf@webmail.moph.go.th>

On Wed,  5 Jan 2005 22:35:42 +0700, amrit@health2.moph.go.th
<amrit@health2.moph.go.th> wrote:
> Now I turn hyperthreading off and readjust the conf . I found the bulb query
> that was :
> update one flag of the table [8 million records which I think not too much]

Ahh, the huge update.  Below are my "hints" I've
found while trying to optimize such updates.

First of all, does this update really changes this 'flag'?
Say, you have update:
UPDATE foo SET flag = 4 WHERE [blah];
are you sure, that flag always is different than 4?
If not, then add:
UPDATE foo SET flag = 4 WHERE flag <> 4 AND [blah];
This makes sure only tuples which actually need the change will
receive it.  [ IIRC mySQL does this, while PgSQL will always perform
UPDATE, regardless if it changes or not ];

Divide the update, if possible.  This way query uses
less memory and you may call VACUUM inbetween
updates.  To do this, first SELECT INTO TEMPORARY
table the list of rows to update (their ids or something),
and then loop through it to update the values.

I guess the problem with huge updates is that
until the update is finished, the new tuples are
not visible, so the old cannot be freed...

   Regards,
      Dawid



view thread (38+ messages)  latest in thread

Message-ID: <758d5e7f050106041510f07dc5@mail.gmail.com>
Permalink:  ../758d5e7f050106041510f07dc5@mail.gmail.com/
Also on:    postgresql.org/message-id/758d5e7f050106041510f07dc5@mail.gmail.com

 · 

reply

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: qnex42@gmail.com
  Subject: Re: Low Performance for big hospital server ..
  In-Reply-To: <758d5e7f050106041510f07dc5@mail.gmail.com>

* 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