pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Indexes with different datatypes:Correction
2+ messages / 2 participants
[nested] [flat]

* Indexes with different datatypes:Correction
@ 2003-04-25 22:36 Cecilia Alvarez <cecilia_ag@hotmail.com>
  2003-04-25 22:40 ` Re: Indexes with different datatypes:Correction scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Cecilia Alvarez @ 2003-04-25 22:36 UTC (permalink / raw)
  To: 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>




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Indexes with different datatypes:Correction
  2003-04-25 22:36 Indexes with different datatypes:Correction Cecilia Alvarez <cecilia_ag@hotmail.com>
@ 2003-04-25 22:40 ` scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 2+ messages in thread

From: scott.marlowe @ 2003-04-25 22:40 UTC (permalink / raw)
  To: Cecilia Alvarez <cecilia_ag@hotmail.com>; +Cc: pgsql-performance

On Fri, 25 Apr 2003, Cecilia Alvarez wrote:

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

Hi Cecilia.  It looks like the problem is that Postgresql assumes that a 
non-quoted number is generally an int4, and since the id_company is int2, 
it isn't automatically converted.  You can either change your app to force 
coercion (which the '' quotes are doing) or like:

where id_company = 120::int2 
OR
where id = cast(120 as int2)

OR you can recreate your table with id_company being int4.  If you NEED to 
restrict it to int2 range, then you can use a constraint to make it act 
like an int2 without actually being one.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-04-25 22:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-25 22:36 Indexes with different datatypes:Correction Cecilia Alvarez <cecilia_ag@hotmail.com>
2003-04-25 22:40 ` scott.marlowe <scott.marlowe@ihs.com>

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