From: Cecilia Alvarez <cecilia_ag@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Indexes with different datatypes:Correction
Date: Fri, 25 Apr 2003 17:36:48 -0500
Message-ID: <Law12-F28Ua8d9Ee4za00004f04@hotmail.com> (raw)
<html><div style='background-color:'><DIV>
<P><BR><BR></P>
<DIV>
<P><FONT face=Arial size=2>Sorry, this is the good one:</FONT></P></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>I´ve already created an concatenated index in Postgres V3.0 with different datatypes:</FONT></P></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></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_company int2</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_status char(1)</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_docum numeric(15,0)</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_origen_mov int4</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>and after several tests the query doesn´t use the index because it seems that id_company must be a char.</FONT></P>
<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=#ff0000>120</FONT></FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2> and id_status = 'X' and id_docum = 10000056789 and id_origen_mov = 12345</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>---- it doesn´t use the index </FONT></P>
<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></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2> and id_status = 'X' and id_docum = 10000056789 and id_origen_mov = 12345</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>---- it uses the index</FONT></P>
<DIV></DIV>
<P> </P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>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.</FONT></P>
<DIV></DIV>
<P><FONT face=Arial size=2>Could you suggest something to resolve this?</FONT></P>
<DIV></DIV>
<P><FONT face=Arial size=2>Thank you very much.</FONT></P>
<DIV></DIV>
<P><FONT face=Arial size=2>Regards,</FONT></P>
<DIV></DIV>
<P><FONT face=Arial size=2>Cecilia</FONT></P>
<DIV></DIV>
<P> </P>
<DIV></DIV>
<DIV> </DIV>
<DIV></DIV></div><br clear=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* </html>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: cecilia_ag@hotmail.com
Subject: Re: Indexes with different datatypes:Correction
In-Reply-To: <Law12-F28Ua8d9Ee4za00004f04@hotmail.com>
References: <Law12-F28Ua8d9Ee4za00004f04@hotmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox