pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron St-Pierre <rstpierre@syscor.com>
To: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Table UPDATE is too slow
Date: Tue, 31 Aug 2004 11:35:38 -0700
Message-ID: <4134C4FA.20404@syscor.com> (raw)
In-Reply-To: <20040831181815.GB24253@uio.no>
References: <4134BF36.3000504@syscor.com>
	<20040831181815.GB24253@uio.no>

Steinar H. Gunderson wrote:

>On Tue, Aug 31, 2004 at 11:11:02AM -0700, Ron St-Pierre wrote:
>  
>
>>We have a web based application with data that is updated daily. The 
>>biggest bottleneck occurs when we try to update
>>one of the tables. This table contains 58,000 rows and 62 columns, and 
>>EVERY column is indexed.
>>    
>>
>
>That is usually a very bad idea; for every update, PostgreSQL has to update
>62 indexes. Do you really do queries on all those 62 columns?
>  
>
Yes, I know that it's not a very good idea, however queries are allowed 
against all of those columns. One option is to disable some or all of the
indexes when we update, run the update, and recreate the indexes, 
however it may slow down user queries. Because there are so many indexes,
it is time consuming to recreate them after the update.

>  
>
>>A typical update is:
>>  UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob',
>>    field04='foo', ... , field60='2004-08-30', field61='2004-08-29'
>>    WHERE id = 1234;
>>    
>>
>
>That looks like poor database normalization, really. Are you sure you don't
>want to split this into multiple tables instead of having 62 columns?
>
No, it is properly normalized. The data in this table is stock 
fundamentals, stuff like 52 week high, ex-dividend date, etc, etc.

>
>  
>
>>Other notables:
>>  The UPDATE is run from a within a function: FOR rec IN SELECT ...LOOP 
>>RETURN NEXT rec; UPDATE dataTable.....
>>  Postgres 7.4.3
>>  debian stable
>>  2 GB RAM
>>  80 DB IDE drive (we can't change it)
>>    
>>
>
>Are you doing all this in multiple transactions, or in a sngle one? Wrapping
>the FOR loop in a transaction might help speed.
>
We're doing it in multiple transactions within the function. Could we do 
something like this?:

....
BEGIN
  FOR rec IN SELECT field01, field02, ... FROM otherTable LOOP 
  RETURN NEXT rec;
  UPDATE dataTable SET field01=rec.field01, field02=rec.field02, rec.field03=field03, ...
    WHERE id = rec.id;
COMMIT;
....


If we can do it this way, are there any other gotcha's we should be 
aware of?


Ron




view thread (16+ messages)  latest in thread

Message-ID: <4134C4FA.20404@syscor.com>
Permalink:  ../4134C4FA.20404@syscor.com/
Also on:    postgresql.org/message-id/4134C4FA.20404@syscor.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: rstpierre@syscor.com, sgunderson@bigfoot.com
  Subject: Re: Table UPDATE is too slow
  In-Reply-To: <4134C4FA.20404@syscor.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