Re: Re: Load a database into memory

> > 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_publicit
>e) );
>
> (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(),

How much tuples do you usually get from this query? random() can be quite
expensive.

Also please give us output of
vacuum verbose analyze parametre;
vacuum verbose analyze accord_editeur;

But it seems that the problem is in your too large index. It is quite
expensive to traverse it (note that all cost flows from index scan over it).

I do not know the exact statictics for your data, but if you create an index
with small amount of fields, which account vast of the data, query should
became much faster, if optimizer will decide to use this index.
As a first try you can create index on num_editeur. Do not forget to do
vacuum analyze accord_editeur; after this.

> 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.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------