From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Guillaume Lémery <glemery(at)comclick(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem with indexes |
Date: | 2001-01-17 23:19:13 |
Message-ID: | 21975.979773553@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fork | 2001-01-17 23:51:00 | Re: viewing foreign key constraints |
Previous Message | Andrew Sullivan | 2001-01-17 23:08:24 | Re: postgresql.conf ignored |