X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0C548475C3D for ; Fri, 25 Apr 2003 18:36:47 -0400 (EDT) Received: from hotmail.com (f28.law12.hotmail.com [64.4.19.28]) by postgresql.org (Postfix) with ESMTP id 4526C474E42 for ; Fri, 25 Apr 2003 18:36:46 -0400 (EDT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 25 Apr 2003 15:36:48 -0700 Received: from 200.67.81.177 by lw12fd.law12.hotmail.msn.com with HTTP; Fri, 25 Apr 2003 22:36:48 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:Correction Date: Fri, 25 Apr 2003 17:36:48 -0500 Mime-Version: 1.0 Content-Type: text/html; charset=iso-8859-1 Message-ID: X-OriginalArrivalTime: 25 Apr 2003 22:36:48.0719 (UTC) FILETIME=[28A375F0:01C30B7B] 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/214 X-Sequence-Number: 1720



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?

Thank you very much.

Regards,

Cecilia

 

 


MSN. Más Útil Cada Día Haz clic aquí smart spam protection and 2 months FREE*