From: | Denis Perchine <dyp(at)perchine(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Load a database into memory |
Date: | 2001-01-26 08:45:37 |
Message-ID: | 01012614453703.01624@dyp.perchine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Le'mery | 2001-01-26 09:05:27 | Re: Load a database into memory |
Previous Message | Guillaume Le'mery | 2001-01-26 08:25:47 | Re: Load a database into memory |