Re: Index not being used ?

On Tue, 2 Sep 2003, Carlos G Mendioroz wrote:

> Hi,
> I'm trying to understand why a perfect match index is not being used,
> and a sequence scan is done in place:
>
> PostgreSQL 7.3.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2
> 20020927 (prerelease)
>
> I've a table with 7M records, and an index on 3 fields:
>
> 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

Try one of
  lid='457' and puerta='10'
  lid=457::int2 and puerta=10::int2
  lid=CAST(457 as int2) and puerta=CAST(10 as int2)

The constants get typed as int4 and so it doesn't realize it can use the
index, you can check the archives for more information.