Skip site navigation (1) Skip section navigation (2)

Slow update query

From: "elias ghanem" <e(dot)ghanem(at)acteos(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Slow update query
Date: 2010-01-21 15:27:02
Message-ID: 201001211524.o0LFO5ka019827@relay-ext.ornis.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I'm not sure this is the right place to ask my question, so please if it is
not let me know where I can get an answer from.

I'm using postgresql 8.4 on Linux machine with 1.5 GB RAM, and I'm issuing
an update query with a where clause that updates approximately 100 000 rows
in a table containing approximately 3 200 000 rows.

The update query is very simple: UPDATE TABLE1 SET FIELD1 = FIELD1 WHERE
FIELD2 < 0.83 (the where clause is used to limit the affected rows to ~ 100
000, and the "SET FIELD1 = FIELD1" is only on purpose to keep the data of
the table unchanged).

Actually this query is inside a function and this function is called from a
.sh file (the function is called 100 times with a vacuum analyze after each
call for the table).

So the average execution time of the function is around 2.5 mins, meaning
that the update query (+ the vacuum) takes 2.5 mins to execute. So is this a
normal behavior? (The same function in oracle with the same environment
(with our vacuum obviously) is executed in 11 second).

Note that no index is created on FIELD2 (neither in postgresql nor in
oracle)

 

Thanks for your help.

 

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-01-21 15:43:19
Subject: Re: Slow update query
Previous:From: Aidan Van DykDate: 2010-01-21 15:05:10
Subject: Re: ext4 finally doing the right thing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group