Skip site navigation (1) Skip section navigation (2)

Indexes with different datatypes:Correction

From: "Cecilia Alvarez" <cecilia_ag(at)hotmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Indexes with different datatypes:Correction
Date: 2003-04-25 22:36:48
Message-ID: Law12-F28Ua8d9Ee4za00004f04@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
<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&nbsp;that id_company must be a char.</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>If a use&nbsp;the value for the id_company eg.&nbsp;&nbsp; select * from movimiento where id_company = <FONT color=#ff0000>120</FONT></FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and id_status = 'X' and id_docum = 10000056789 and&nbsp;id_origen_mov = 12345</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>---- it doesn´t use the index&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>If a use&nbsp;the value for the id_company eg.&nbsp;&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and id_status = 'X' and id_docum = 10000056789 and&nbsp;id_origen_mov = 12345</FONT></P>
<DIV></DIV>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>---- it&nbsp; uses the index</FONT></P>
<DIV></DIV>
<P>&nbsp;</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&nbsp;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>&nbsp;</P>
<DIV></DIV>
<DIV>&nbsp;</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>



Attachment: unknown_filename
Description: text/html (3.9 KB)

Responses

pgsql-performance by date

Next:From: scott.marloweDate: 2003-04-25 22:40:54
Subject: Re: Indexes with different datatypes:Correction
Previous:From: Cecilia AlvarezDate: 2003-04-25 22:33:13
Subject: Indexes with different datatypes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group