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?

Thank you very much.

Regards,

Cecilia