Re: Re: Load a database into memory

Steve Wolfe wrote:

>>> If you have enough RAM, the database will already be in memory, in a
>>> manner of speaking - your kernel will have all of the files held in disk
>>> cache.
>>
>> Ok, but if so, why 10 simultaneous same queries on a well-indexed table
>
> with only 500 000 records take a so long time ? :-/
>
> Good question. My first guess is that it's CPU-bound, or that the indexes
> aren't working for some reason. We'd need to see the table structure, and
> the query. And a "vacuum analyze" never hurts. : )

Here are the 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. It's the only way to have unique record.)

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);

The 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

And the Explain :
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

In fact it is a CPU-Bound... But why ?
One query takes up to 14% of CPU usage ! The server is a bi-proc PIII 660.
And Id'like to run more than 100 at the same time...

Maybe if I set hash indices on single fields instead of one index on
multiple columns ?

Thanks,

Guillaume.