pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron St-Pierre <rstpierre@syscor.com>
To: pgsql-performance@postgresql.org
Subject: Table UPDATE is too slow
Date: Tue, 31 Aug 2004 11:11:02 -0700
Message-ID: <4134BF36.3000504@syscor.com> (raw)

 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. Every column is
 queryable (?) by the users through the web interface so we are 
reluctant to remove the indexes (recreating them would
 be time consuming too). The primary key is an INT and the rest of the 
columns are a mix of NUMERIC, TEXT, and DATEs.
 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;

 Also of note is that the update is run about 10 times per day; we get 
blocks of data from 10 different sources, so we pre-process the
 data and then update the table. We also run VACUUM FULL ANALYZE on a 
nightly basis.
 
 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.....
   Postgres 7.4.3
   debian stable
   2 GB RAM
   80 DB IDE drive (we can't change it)
 
   shared_buffers = 2048
   sort_mem = 1024  
   max_fsm_pages = 40000
   checkpoint_segments = 5
   random_page_cost = 3
 
 
 Thanks
 
 Ron





view thread (16+ messages)  latest in thread

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