Thread: Indexes with different datatypes:Correction

<div style="background-color:"><div><p><br /><br /><div><p><font face="Arial" size="2">Sorry, this is the good
one:</font></div><p><fontface="Geneva, Arial, Sans-serif" size="2">I´ve already created an concatenated index in
PostgresV3.0 with different datatypes:</font></div><p><font face="Geneva, Arial, Sans-serif"><font size="2"><font
color="#0000ff">CREATE</font><font color="#ff0000">INDEX</font> mov_i4 <font color="#008000">ON</font> movimiento <font
color="#008000">USING</font>btree (id_company, id_status, id_docum, id_origen_mov);</font></font><div></div><p><font
face="Geneva,Arial, Sans-serif" size="2">id_company int2</font><div></div><p><font face="Geneva, Arial, Sans-serif"
size="2">id_statuschar(1)</font><div></div><p><font face="Geneva, Arial, Sans-serif" size="2">id_docum
numeric(15,0)</font><div></div><p><fontface="Geneva, Arial, Sans-serif" size="2">id_origen_mov
int4</font><div></div><p><fontface="Geneva, Arial, Sans-serif" size="2">and after several tests the query doesn´t use
theindex because it seems that id_company must be a char.</font><div></div><p><font face="Geneva, Arial, Sans-serif"
size="2">Ifa use the value for the id_company eg.   select * from movimiento where id_company = <font
color="#ff0000">120</font></font><div></div><p><fontface="Geneva, Arial, Sans-serif"
size="2">                                                          andid_status = 'X' and id_docum = 10000056789
and id_origen_mov= 12345</font><div></div><p><font face="Geneva, Arial, Sans-serif" size="2">---- it doesn´t use the
index                                                                               </font><div></div><p><font
face="Geneva,Arial, Sans-serif" size="2">If a use the value for the id_company eg.   select * from movimiento where
id_company= <font color="#ff0066">'120' </font>and</font><div></div><p><font face="Geneva, Arial, Sans-serif"
size="2">                                                     andid_status = 'X' and id_docum = 10000056789
and id_origen_mov= 12345</font><div></div><p><font face="Geneva, Arial, Sans-serif" size="2">---- it  uses the
index</font><div></div><p> <div></div><p><fontface="Geneva, Arial, Sans-serif" size="2">The problem is that I can´t
changethe datatypes in the hole application and the table has 240,000 rows and we need to use concatenated indexes,
becausewe access the table in different ways, the table has another five concatenated
indexes.</font><div></div><p><fontface="Arial" size="2">Could you suggest something to resolve
this?</font><div></div><p><fontface="Arial" size="2">Thank you very much.</font><div></div><p><font face="Arial"
size="2">Regards,</font><div></div><p><fontface="Arial"
size="2">Cecilia</font><div></div><p> <div></div><div> </div><div></div></div><brclear="all" /><hr />MSN. Más Útil Cada
Día<a href="http://g.msn.com/8HMWESMX/2737">Haz clic aquí </a> smart spam protection and 2 months FREE*  

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.