Slow update query

["elias ghanem" <e.ghanem@xxxxxxxxxx>]

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.