X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 708B6475C3D for ; Fri, 25 Apr 2003 18:33:12 -0400 (EDT) Received: from hotmail.com (f42.law12.hotmail.com [64.4.19.42]) by postgresql.org (Postfix) with ESMTP id B58E4474E42 for ; Fri, 25 Apr 2003 18:33:11 -0400 (EDT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 25 Apr 2003 15:33:14 -0700 Received: from 200.67.81.177 by lw12fd.law12.hotmail.msn.com with HTTP; Fri, 25 Apr 2003 22:33:13 GMT X-Originating-IP: [200.67.81.177] X-Originating-Email: [cecilia_ag@hotmail.com] From: "Cecilia Alvarez" To: pgsql-performance@postgresql.org Subject: Indexes with different datatypes Date: Fri, 25 Apr 2003 17:33:13 -0500 Mime-Version: 1.0 Content-Type: text/html; charset=iso-8859-1 Message-ID: X-OriginalArrivalTime: 25 Apr 2003 22:33:14.0019 (UTC) FILETIME=[A8AAD730:01C30B7A] X-Spam-Status: No, hits=-0.5 required=5.0 tests=BAYES_30,HTML_60_70,HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN,HTML_FONT_COLOR_RED version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/213 X-Sequence-Number: 1719

Hi,

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_docto = 10000056789 and mount = 12345.56

---- 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_docto = 10000056789 and mount = 12345.56

---- 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

 

 


Únete al mayor servicio mundial de correo electrónico: Haz clic aquí