Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow update query

Slow update query

From
"elias ghanem"
Date:

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.

 

Re: Slow update query

From
"Kevin Grittner"
Date:
"elias ghanem" <e.ghanem@acteos.com> wrote:

> I'm not sure this is the right place to ask my question

Yes it is.  You gave a lot of good information, but we'd have a
better shot at diagnosing the issue with a bit more.  Please read
the following and resubmit with as much of the requested information
as you can.  Note that you might need to break out the problem query
from the function to run EXPLAIN ANALYZE (the output of which is one
of the more useful diagnostic tools we have).

http://wiki.postgresql.org/wiki/SlowQueryQuestions

-Kevin