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
site_id character varying(100),
fiche_produit_id character varying(100),
numero_commande character varying(100),
client_ref character varying(100),
type_mouvement character varying(100),
valide character varying(1)
-- Index: idx_in_sortie
-- DROP INDEX idx_in_sortie;
CREATE INDEX idx_in_sortie
-- Index: idx_in_sortie_fp
-- DROP INDEX idx_in_sortie_fp;
CREATE INDEX idx_in_sortie_fp
-- Index: idx_in_sortie_site
-- DROP INDEX idx_in_sortie_site;
CREATE INDEX idx_in_sortie_site
-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
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
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.
pgsql-performance by date
|Next:||From: Florian Weimer||Date: 2010-01-21 16:24:12|
|Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?|
|Previous:||From: Kevin Grittner||Date: 2010-01-21 15:54:26|
|Subject: Re: ext4 finally doing the right thing|