Re: Load a database into memory - Mailing list pgsql-general

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

Should not change too much...

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

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

> OK, I dropped the large index and only set one on num_editeur.
>
> 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.33s/1.74u sec.
> NOTICE:  Index accord_editeur_tracking_idx: Pages 1330; Tuples 447032:
> Deleted 0. CPU 0.05s/0.93u sec.
> VACUUM
>
> Here is the new Explain (always whithout the random()) :
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=0.00..512.65 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_tracking_idx on accord_editeur ae
>   (cost=0.00..509.88 rows=60 width=48)
>
> EXPLAIN
>
> So, the large index seems to be better...
> Or maybe I should try with a hash index ?
>
> If I hash the num_editeur in another field and I set my index on these 2
> fields, it gives me better cost, but performance is always slow :-(

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;

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

SELECT count(id_regie) FROM accord_editeur WHERE
id_regie=1
AND num_editeur = 1494
AND num_site = 1;

SELECT count(id_regie) FROM accord_editeur WHERE
id_regie=1
AND num_editeur = 1494;

SELECT count(id_regie) FROM accord_editeur WHERE
id_regie=1;

SELECT count(id_regie) FROM accord_editeur WHERE
num_editeur = 1494;

Actually it is some sort of statistics to choose the fields to create index
on.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------