pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mike Mascari <mascarm@mascari.com>
To: Rick Gigger <rick@alpinenetworking.com>
Cc: PgSQL General ML <pgsql-general@postgresql.org>
Subject: Re: performance problem
Date: Tue, 18 Nov 2003 16:03:13 -0500
Message-ID: <3FBA8911.5060805@mascari.com> (raw)
In-Reply-To: <01b201c3ae14$92bd3c00$0700a8c0@trogdor>
References: <1069164075.986.239.camel@bbking.uio.no>
	<20031118142919.GI4953@libertyrms.info>
	<1069180780.2769.91.camel@haggis.homelan>
	<019301c3ae07$d714e220$0700a8c0@trogdor>
	<87llqdqy37.fsf@asmodeus.mcnaught.org>
	<20031118205057.K606@hermes.hilbert.loc>
	<01b201c3ae14$92bd3c00$0700a8c0@trogdor>

Rick Gigger wrote:

> I am currently trying to import a text data file without about 45,000
> records.  At the end of the import it does an update on each of the 45,000
> records.  Doing all of the inserts completes in a fairly short amount of
> time (about 2 1/2 minutes).  Once it gets to the the updates though it slows
> to a craw.  After about 10 minutes it's only done about 3000 records.
> 
> Is that normal?  Is it because it's inside such a large transaction?  Is
> there anything I can do to speed that up.  It seems awfully slow to me.
> 
> I didn't think that giving it more shared buffers would help but I tried
> anyway.  It didn't help.
> 
> I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot
> of stuff but it didn't speed up the updates at all.
> 
> I am using a dual 800mhz xeon box with 2 gb of ram.  I've tried anywhere
> from about 16,000 to 65000 shared buffers.
> 
> What other factors are involved here?

It is difficult to say without knowing either the definition of the
relation(s) or the update queries involved. Are there indexes being
created after the import that would allow PostgreSQL to locate the
rows being updated quickly, or is the update an unqualified update (no
WHERE clause) that affects all tuples?

EXPLAIN ANALYZE is your friend...

Mike Mascari
mascarm@mascari.com





view thread (37+ messages)  latest in thread

Message-ID: <3FBA8911.5060805@mascari.com>
Permalink:  ../3FBA8911.5060805@mascari.com/
Also on:    postgresql.org/message-id/3FBA8911.5060805@mascari.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mascarm@mascari.com, rick@alpinenetworking.com, pgsql-general@postgresql.org
  Subject: Re: performance problem
  In-Reply-To: <3FBA8911.5060805@mascari.com>
  References: <1069164075.986.239.camel@bbking.uio.no> <20031118142919.GI4953@libertyrms.info> <1069180780.2769.91.camel@haggis.homelan> <019301c3ae07$d714e220$0700a8c0@trogdor> <87llqdqy37.fsf@asmodeus.mcnaught.org> <20031118205057.K606@hermes.hilbert.loc> <01b201c3ae14$92bd3c00$0700a8c0@trogdor> <3FBA8911.5060805@mascari.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