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


> 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...

Yes, very good point I must try this and I will give you the result , thanks a
lot.
Amrit
Thailand




view thread (38+ messages)  latest in thread

Message-ID: <1105029283.41dd68a3ead99@webmail.moph.go.th>
Permalink:  ../1105029283.41dd68a3ead99@webmail.moph.go.th/
Also on:    postgresql.org/message-id/1105029283.41dd68a3ead99@webmail.moph.go.th

 · 

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: amrit@health2.moph.go.th, qnex42@gmail.com
  Subject: Re: Low Performance for big hospital server ..
  In-Reply-To: <1105029283.41dd68a3ead99@webmail.moph.go.th>

* 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