Slow update query

Hi,

Thanks for your help, here's more details as you requested:

-The version of postgres is 8.4 (by the way select pg_version() is not
working but let's concentrate on the query issue)

Here's the full definition of the table with it's indices:

-- Table: in_sortie

-- DROP TABLE in_sortie;

CREATE TABLE in_sortie

(

"type" character(1),

site_id character varying(100),

fiche_produit_id character varying(100),

numero_commande character varying(100),

ligne_commande integer,

date_sortie date,

quantite_sortie numeric(15,2),

date_livraison_souhaitee date,

quantite_souhaitee numeric(15,2),

client_ref character varying(100),

valeur numeric(15,2),

type_mouvement character varying(100),

etat_sortie_annulation integer,

etat_sortie_prevision integer,

etat_sortie_taux_service integer,

date_commande date,

valide character varying(1)

)

WITH (

OIDS=FALSE

)

TABLESPACE "AG_INTERFACE";

-- Index: idx_in_sortie

-- DROP INDEX idx_in_sortie;

CREATE INDEX idx_in_sortie

ON in_sortie

USING btree

(site_id, fiche_produit_id);

-- Index: idx_in_sortie_fp

-- DROP INDEX idx_in_sortie_fp;

CREATE INDEX idx_in_sortie_fp

ON in_sortie

USING btree

(fiche_produit_id);

-- Index: idx_in_sortie_site

-- DROP INDEX idx_in_sortie_site;

CREATE INDEX idx_in_sortie_site

ON in_sortie

USING btree

(site_id);

-Concerning the postgresql.conf file I've tried to changed the default
values such as: shared_buffers and effective_cache_size. but this did not
change the result.

-The WAL IS NOT ON DIFFERENT DISK, THEY ARE ON THE SAME DISK WHER THE DB IS
(for the moment I don't have the possibility of moving them to another disk
but maybe "just for testing" you can tell me how I can totally disable WAL
if possible).

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 IN_SORTIE SET VALIDE = VALIDE WHERE
VALEUR < 0.83 (the where clause is used to limit the affected rows to ~ 100
000, and the "SET VALIDE = VALIDE" 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 using the following syntax: psql -h $DB_HOST -p $DB_PORT -d
$DB_NAME -U $DB_USER -c "SELECT testupdate()"

(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).

Thanks for your help.