Re: Index not being used ?

On Wed, 2003-09-03 at 16:34, Adam Kavan wrote:
> >CREATE TABLE public.base (
> >   nombre varchar(255),
> >   calle varchar(255),
> >   puerta int2,
> >   resto varchar(255),
> >   lid int2,
> >   area varchar(4),
> >   telefono varchar(10)
> >)
> >CREATE INDEX base_dir ON base USING btree (lid, calle, puerta);
> >
> >And trying the following select:
> >
> >select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10
> 
> I think its because lid and puerta are int2's and 457 and 10 are 
> int4's.  Try lid = '457'::int2 and puerta = '10'::int2.  To use an index 
> the variables have to match types exactly.

Quotes and casting together are not necessary.
Either of these will work:
  lid = '457' and puerta = '10'
  lid = 457::int2 and puerta = 10::int2

I'd choose "lid = 457::int2" since it's telling PG what the datatype
is, whereas with lid = '457', PG must figure it out.

-- 
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

After seeing all the viruses, trojan horses, worms and Reply 
mails from stupidly-configured anti-virus software that's been 
hurled upon the internet for the last 3 years, and the 
time/money that is spent proteting against said viruses, trojan 
horses & worms, I can only conclude that Microsoft is dangerous 
to the internet and American commerce, and it's software should 
be banned.