Re: Problem with indexes

Guillaume =?ISO-8859-1?Q?L=E9mery?= <glemery(at)comclick(dot)com> writes:
> CREATE TABLE accord_editeur
> (
> id_regie int8 not null,
> num_campagne int8 not null,
> num_publicite int8 not null,
> num_editeur int8 not null,
> num_site int8 not null,
> num_emplacement int8 not null,
> num_periode int8,
> ...

> CREATE INDEX ae_tracking_idx ON accord_editeur(id_regie, num_editeur,
> num_site, num_emplacement);

> 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

> I get :

> Seq Scan on accord_editeur ae (cost=0.00..19349.71 rows=1 width=40)

The problem is that the system is not very smart about converting
cross-datatype comparisons into indexscans, and what you have written
is comparisons between int8 fields and int4 constants. If you write
the query as

WHERE ae.id_regie = 1::int8
AND ae.num_editeur = 1494::int8
AND ae.num_site = 1::int8
AND ae.num_emplacement = 1::int8
...

then you will get an indexscan. My advice, however, would be to think
carefully about whether you really *need* int8 fields, or could save
space and notational hassle by using int4 fields instead. (You should
also think twice about whether a four-component index really makes
sense, but that's a different discussion.)

At some point we will figure out how to get the system to assign types
to constants more intelligently --- this same issue causes problems for
people who write "numericfield = 12.34", for example, since 12.34 is
taken as a float8 constant by default. It's not easy to do that without
doing severe damage to the notion of datatype extensibility, however :-(

regards, tom lane