Re: Problem with indexes

hi,

glemery(at)comclick(dot)com (Guillaume Lmery) wrote in <3A65C7E4.3020202
@comclick.com>:
[...]
>And an Index :
>
>CREATE INDEX ae_tracking_idx ON accord_editeur(id_regie, num_editeur,
>num_site, num_emplacement);

In this Index creation you specified that *one* index will index *four*
rows, and to do that, postgres can only use btree index. This type of index
is usefull when you search a range of value with those operators : < > <= or
>=

>If I do an EXPLAIN on this :
>
>SELECT ae.id_regie,
>ae.num_campagne,
>ae.num_publicite,
>ae.ponderation_calculee,
>ae.num_periode
>FROM accord_editeur ae
>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

But that's not at all what you do, you search values that match exactly to
one value, and to achieve this result you have to create four individuals
index of type 'hash' using this list of commands :

CREATE INDEX ae_ir_idx ON accord_editeur USING hash (id_regie);
CREATE INDEX ae_ned_idx ON accord_editeur USING hash (num_editeur);
CREATE INDEX ae_ns_idx ON accord_editeur USING hash (num_site);
CREATE INDEX ae_nem_idx ON accord_editeur USING hash (num_emplacement);

But even in this condition index aren't always the most efficient way to
achieve the result especially if you have many rows with the same values. In
this case it's more efficient to do a seq scan.

Yann