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.