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 16:14:38
Message-ID: 201001211611.o0LGBdka014189@relay-ext.ornis.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

 

Responses

pgsql-performance by date

Next:From: Florian WeimerDate: 2010-01-21 16:24:12
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Previous:From: Kevin GrittnerDate: 2010-01-21 15:54:26
Subject: Re: ext4 finally doing the right thing

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