Very bad performance on a query - Mailing list pgsql-general

Hi,

I've already posted something about this trouble, and all I tried didn't
worked very well...

Here is my tables :

CREATE TABLE accord_editeur
(
id_regie int4 not null,
num_editeur int4 not null,
num_site int4 not null,
num_emplacement int4 not null,
num_campagne int4 not null,
num_publicite int4 not null,
num_periode int4,
par_id_technologie int4 not null,
affichage_possible int4 default 0,
ponderation_calculee int4,
date_pilotage timestamp NULL,
id_ct1 int4,
PRIMARY
KEY(id_regie,num_editeur,num_site,num_emplacement,num_campagne,num_publicite)
);

(I've got a primary key on multiple fields because of the complexity of
my database.)

CREATE TABLE parametre
(
id_parametre int4 not null primary key,
id_regie int4 NULL ,
par_id_parametre int4 NULL ,
type INT4 not null,
valeur_str varchar null,
valeur_int int4 null,
valeur_fl float8 null,
valeur_txt varchar,
date_pilotage timestamp NULL,
id_ct1 int4
);
CREATE INDEX parametre_tracking_idx ON parametre(type, valeur_int);

Here is my query :

SELECT ae.id_regie,
ae.num_campagne,
ae.num_publicite,
ae.ponderation_calculee * random(),
ae.num_periode
FROM accord_editeur ae,
parametre par
WHERE ae.id_regie=1
AND ae.num_editeur = 1494
AND ae.num_site = 1
AND ae.num_emplacement = 1
AND ae.affichage_possible = 1
AND ae.par_id_technologie = par.id_parametre
AND par.type = 10
AND par.valeur_int = 1


If I do an Explain I got this :

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..15422.73 rows=1 width=56)
   ->  Index Scan using accord_editeur_pkey on accord_editeur ae
(cost=0.00..15420.71 rows=1 width=48)
   ->  Index Scan using parametre_tracking_idx on parametre par
(cost=0.00..2.02 rows=1 width=8)

EXPLAIN

Table accord_editeur only have about 500 000 records.
Table parametre about 200.

So, with Index Scan it is supposed to run fast.

To access to the base, I have a connection pooling tool which maintains
always 5 connections to the base (to have more doesn't increase
performance).
But if I launch 100 queries at a single time (which is what my base is
going to face), the execution of the queries is too too slow...
The server is a bi-PIII 660 with 1GB RAM.
During the execution, a top reveal that CPU usage is 100% - about 20%
per postmaster !
This server is dedicated to database... I've separated the web server to
see what happens...

I can't load my base into memory because its data always change and I
have to always have the last data to set my parameters for other queries...

Has anybody an idea of what is wrong with this query ?
I don't have any ideas anymore.

Thanks a lot...

Guillaume.