Re: Indexes with different datatypes:Correction

On Fri, 25 Apr 2003, Cecilia Alvarez wrote:

>
>
>
> Sorry, this is the good one:
>
> I´ve already created an concatenated index in Postgres V3.0 with different datatypes:
>
> CREATE INDEX mov_i4 ON movimiento USING btree (id_company, id_status, id_docum,
> id_origen_mov);
>
> id_company int2
>
> id_status char(1)
>
> id_docum numeric(15,0)
>
> id_origen_mov int4
>
> and after several tests the query doesn´t use the index because it seems that id_company must
> be a char.
>
> If a use the value for the id_company eg.   select * from movimiento where id_company = 120
>
>                                                           and id_status = 'X' and id_docum =
> 10000056789 and id_origen_mov = 12345
>
> ---- it doesn´t use the
> index                                                                               
>
> If a use the value for the id_company eg.   select * from movimiento where id_company = '120'
> and
>
>                                                      and id_status = 'X' and id_docum =
> 10000056789 and id_origen_mov = 12345
>
> ---- it  uses the index
>
>  
>
> The problem is that I can´t change the datatypes in the hole application and the table has
> 240,000 rows and we need to use concatenated indexes, because we access the table in
> different ways, the table has another five concatenated indexes.
>
> Could you suggest something to resolve this?

Hi Cecilia.  It looks like the problem is that Postgresql assumes that a
non-quoted number is generally an int4, and since the id_company is int2,
it isn't automatically converted.  You can either change your app to force
coercion (which the '' quotes are doing) or like:

where id_company = 120::int2
OR
where id = cast(120 as int2)

OR you can recreate your table with id_company being int4.  If you NEED to
restrict it to int2 range, then you can use a constraint to make it act
like an int2 without actually being one.