pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron St-Pierre <rstpierre@syscor.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Table UPDATE is too slow
Date: Wed, 01 Sep 2004 01:32:08 -0400
Message-ID: <1613.1094016728@sss.pgh.pa.us> (raw)
In-Reply-To: <4134BF36.3000504@syscor.com>
References: <4134BF36.3000504@syscor.com>

Ron St-Pierre <rstpierre@syscor.com> writes:
>  Does anyone have some idea on how we can increase speed, either by 
> changing the updates, designing the database
>  differently, etc, etc? This is currently a big problem for us.
 
>  Other notables:
>    The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP 
> RETURN NEXT rec; UPDATE dataTable.....

One point that I don't think was made before: by doing a collection of
updates in this serial one-at-a-time fashion, you are precluding any
possibility of query optimization over the collection of updates.  It
might win to dump the update data into a temp table and do a single
UPDATE command joining to the temp table.  Or not --- quite possibly not
--- but I think it's something to think about.

			regards, tom lane



view thread (16+ messages)  latest in thread

Message-ID: <1613.1094016728@sss.pgh.pa.us>
Permalink:  ../1613.1094016728@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1613.1094016728@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, rstpierre@syscor.com
  Subject: Re: Table UPDATE is too slow
  In-Reply-To: <1613.1094016728@sss.pgh.pa.us>

* 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