pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff <threshar@torgo.978.org>
To: Ivar Zarans <iff@alcaron.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow UPADTE, compared to INSERT
Date: Thu, 4 Dec 2003 14:23:20 -0500
Message-ID: <20031204142320.3c021100.threshar@torgo.978.org> (raw)
In-Reply-To: <20031204185751.GA15893@alcaron.ee>
References: <20031204185751.GA15893@alcaron.ee>

On Thu, 4 Dec 2003 20:57:51 +0200
Ivar Zarans <iff@alcaron.ee> wrote:
.

> table1 is updated with new value (done). Update statement itself is
> extremely simple: "update table1 set status = 'done' where recid =
> ..."
> 
> Most interesting is, that insert takes 0.004 seconds in average, but
> update takes 0.255 seconds in average. Processing of 24000 records
> took around 1 hour 20 minutes.

Do you have an index on recid?

and did you vacuum analyze after you loaded up the data?

> 
> Then i changed processing logic not to update every record in table1
> after processing. Instead i did insert recid value into temporary
> table and updated records in table1 after all records were processed
> and inserted into table2:
> UPDATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM
> temptable)
> 

"IN" queries are terribly slow on versions before 7.4

> Why is UPDATE so slow compared to INSERT? I would expect more or less
> similar performance, or slower on insert since table2 has four indexes
> in addition to primary key, table1 has only primary key, which is used
> on update. Am i doing something wrong or is this normal?
> 

Remember, UPDATE has to do all the work of select and more. 

And if you have 4 indexes those will also add to the time (Since it has
to update/add them to the tree)

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



view thread (24+ messages)  latest in thread

Message-ID: <20031204142320.3c021100.threshar@torgo.978.org>
Permalink:  ../20031204142320.3c021100.threshar@torgo.978.org/
Also on:    postgresql.org/message-id/20031204142320.3c021100.threshar@torgo.978.org

 · 

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: threshar@torgo.978.org, iff@alcaron.ee
  Subject: Re: Slow UPADTE, compared to INSERT
  In-Reply-To: <20031204142320.3c021100.threshar@torgo.978.org>

* 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