Re: Load a database into memory

>> Explain :
>> NOTICE:  QUERY PLAN:
>>
>> Nested Loop  (cost=0.00..228.27 rows=1 width=56)
>>    ->  Index Scan using parametre_tracking_idx on parametre par
>> (cost=0.00..2.02 rows=1 width=8)
>>    ->  Index Scan using accord_editeur_pkey on accord_editeur ae
>> (cost=0.00..225.50 rows=60 width=48)
>>
>> EXPLAIN
>
>
> But why do you have now cost for index scan 225.50, when in your last mail it
> was 15420.71???
Cause I'm dumb...
In the first mail, I didn't have the good index set...
Sorry.

>
>>> vacuum verbose analyze accord_editeur;
>>
>> NOTICE:  --Relation accord_editeur--
>> NOTICE:  Pages 7096: Changed 0, reaped 6790, Empty 0, New 0; Tup 447032:
>> Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 23768, MinLen 124, MaxLen 124;
>> Re-using: Free/Avail. Space 721776/0; EndEmpty/Avail. Pages 0/0. CPU
>> 0.21s/1.85u sec.
>> NOTICE:  Index accord_editeur_pkey: Pages 3339; Tuples 447032: Deleted
>> 0. CPU 0.19s/1.01u sec.
>> VACUUM
>
>
> As far as you can see, your index is almost half of table size. This is quite
> huge crap.

I've seen that :-/

> Please give the output of:
>
> SELECT count(id_regie) FROM accord_editeur WHERE
> id_regie=1
> AND num_editeur = 1494
> AND num_site = 1
> AND num_emplacement = 1
> AND affichage_possible = 1;
  count
-------
    103
(1 row)

> SELECT count(id_regie) FROM accord_editeur WHERE
> id_regie=1
> AND num_editeur = 1494
> AND num_site = 1
> AND num_emplacement = 1;
  count
-------
    103
(1 row)

(because for a previous test, I've deleted the record where
affichage_possible = 0, because I don't need them...)

> SELECT count(id_regie) FROM accord_editeur WHERE
> id_regie=1
> AND num_editeur = 1494
> AND num_site = 1;
  count
-------
    179
(1 row)

> SELECT count(id_regie) FROM accord_editeur WHERE
> id_regie=1
> AND num_editeur = 1494;
  count
-------
    352
(1 row)

> SELECT count(id_regie) FROM accord_editeur WHERE
> id_regie=1;
  count
--------
  447032
(1 row)
(the complete table, it is normal...)

> SELECT count(id_regie) FROM accord_editeur WHERE
> num_editeur = 1494;
  count
-------
    352
(1 row)

Maybe I should create an index on (num_editeur, num_site, num_emplacement) ?