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

Re: Indexes with different datatypes:Correction

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Cecilia Alvarez <cecilia_ag(at)hotmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Indexes with different datatypes:Correction
Date: 2003-04-25 22:40:54
Message-ID: Pine.LNX.4.33.0304251635246.2484-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2003-04-26 15:15:37
Subject: Automatic analyze on select into
Previous:From: Cecilia AlvarezDate: 2003-04-25 22:36:48
Subject: Indexes with different datatypes:Correction

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