pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexes 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 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>
^ 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