Re: Re: Load a database into memory

Guillaume =?KOI8-R?Q?Le=27mery?= <glemery(at)comclick(dot)com> writes:
> 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(),
> 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..228.27 rows=1 width=56)
> -> Index Scan using accord_editeur_pkey on accord_editeur ae
> (cost=0.00..225.50 rows=1 width=48)
> -> Index Scan using parametre_tracking_idx on parametre par
> (cost=0.00..2.02 rows=1 width=8)

I think the parametre_tracking_idx index is actually counterproductive
here. You'd probably be better off without it, since then the
planner would (presumably) choose to use the primary-key index,
which is unique. I suspect that "WHERE par.type = 10 AND par.valeur_int
= 1" retrieves multiple records which then get thrown away. It'd be
better to use "WHERE ae.par_id_technologie = par.id_parametre" to pull
out the single candidate row --- but the planner is evidently getting
fooled by the availability of two clauses instead of one for the extra
index, and is mistakenly guessing that the extra one is more selective.

Other than that, perhaps you need to readjust your notion of what's
slow. 100 concurrent transactions on a PIII is a pretty stiff workload;
if you're expecting millisecond turnaround then I think you're out of
luck.

regards, tom lane