Message-ID: <00cd01c40597$58d3e9a0$7201a8c0@mst1x5r347kymb>
From: "Iain" <iain@mst.co.jp>
To: <email_daniel_h@yahoo.com.br>, <pgsql-sql@postgresql.org>
References: <404C7EE3.8010006@yahoo.com.br>
	<20040308184440.GG588@64.81.242.180> <404D4667.10206@yahoo.com.br>
Subject: Re: Simple SQL question
Date: Tue, 9 Mar 2004 14:28:26 +0900
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Just a note based on my experience, if you are going to to use IN processing
then there is a good chance that the index isn't going to be used. In some
recent tests I did, the index stopped being used after I put 3 or more items
in the IN list. You should build some representatve examples of the select
and test them using "ANALYSE SELECT ..." .I'm using 7.4.1.

The result from the select is the same, even if you don't make an index on
the function result.It's just a performance consideration. If the index
isn't used, then you don't need to create it.

regards
Iain
----- Original Message ----- 
From: "Daniel Henrique Alves Lima" <email_daniel_h@yahoo.com.br>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question


> Thank you, Jeremy.
>
> I've built a function that returns a string from (cd_teacher, cd_course)
> and i've create a functional index over this function, like :
>
> create index teacher_course_idx on teacher_course
> (build_unique_2p(cd_teacher,cd_course));
> select * from teacher_course where build_unique_2p(cd_teacher,cd_course)
> in ('1:2','23:11','563','341');
>
> Is it possible to use "array cast" over cd_teacher and cd_course (just
> an idea, i don't known the sintaxe), like:
>
> select * from teachar_course where cast((cd_teacher,cd_course) as array)
> in ('{1,2}','{23,11}','{563,341}');
>
> ?
>
> I'm using postgreSQL 7.34
>
> Thanks !!!
>
> Jeremy Semeiks wrote:
>
> >You could use a subselect of unions:
> >
> >select * from teacher_course where (cd_course, cd_teacher) in
> >(select 1, 2 union select 23, 11 union select 563, 341)
> >
> >Maybe there's a more concise way, though.
> >
> >- Jeremy
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match