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

Indexes with different datatypes

From: "Cecilia Alvarez" <cecilia_ag(at)hotmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Indexes with different datatypes
Date: 2003-04-25 22:33:13
Message-ID: Law12-F42ggtxEx4H1e00004b00@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
<html><div style='background-color:'><P><FONT face="Geneva, Arial, Sans-serif" size=2>Hi,</FONT></P>
<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>
<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>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_company int2</FONT></P>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_status char(1)</FONT></P>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_docum numeric(15,0)</FONT></P>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>id_origen_mov int4</FONT></P>
<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>
<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>
<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_docto = 10000056789 and mount = 12345.56</FONT></P>
<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>
<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>
<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_docto = 10000056789 and mount = 12345.56</FONT></P>
<P><FONT face="Geneva, Arial, Sans-serif" size=2>---- it&nbsp; uses the index</FONT></P>
<P>&nbsp;</P>
<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>
<P><FONT face=Arial size=2>Could you suggest something to resolve this?</FONT></P>
<P><FONT face=Arial size=2>Thank you very much.</FONT></P>
<P><FONT face=Arial size=2>Regards,</FONT></P>
<P><FONT face=Arial size=2>Cecilia</FONT></P>
<P>&nbsp;</P>
<DIV>&nbsp;</DIV></div><br clear=all><hr>Únete al mayor servicio mundial de correo electrónico:  <a href="http://g.msn.com/8HMWESMX/2755">Haz clic aquí </a> </html>



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

pgsql-performance by date

Next:From: Cecilia AlvarezDate: 2003-04-25 22:36:48
Subject: Indexes with different datatypes:Correction
Previous:From: Tom LaneDate: 2003-04-25 20:10:47
Subject: Re: More tablescanning fun

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