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.
