Skip site navigation (1) Skip section navigation (2)

Re: index is not used

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: mila boldareva <pierro(at)dds(dot)nl>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: index is not used
Date: 2002-08-21 14:09:54
Message-ID: 20031.1029938994@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
mila boldareva <pierro(at)dds(dot)nl> writes:
> ... but the index on real field3 is used only when I
> ask ordering, and a query like below remains seq. scan:

>     trc=# explain select * from mytable where field3 = 0.1 limit 1;

Unadorned "0.1" will be taken as a float8 (double precision) constant,
and the system is not currently smart enough to convert a cross-datatype
comparison (viz, float4 vs float8) into an indexscan.  You can either
change the field type to double precision or write an explicit coercion:

	select * from mytable where field3 = 0.1::real limit 1;

A hack that some people use instead is to quote the constant, even
though it's numeric:

	select * from mytable where field3 = '0.1' limit 1;

It turns out that this causes the system to postpone assigning a
specific type to the constant until late enough in processing the query
that it knows float4 is the best choice instead of float8.

This is basically the same story as for int2 and int8 columns.  It's
pretty ugly, and fixing it is on the TODO list.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Robert TreatDate: 2002-08-21 22:23:19
Subject: Re: Event recurrence - in database or in application code ????
Previous:From: Hugh ScullyDate: 2002-08-21 14:07:28
Subject: configure options

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group