Date: Tue, 05 Jul 2005 13:53:59 -0400
From: David Gagnon <dgagnon@siunik.com>
Subject: Re: Why the planner is not using the INDEX .
In-reply-to: <23492.1120579796@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Message-id: <42CAC937.4080702@siunik.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
References: <20050630222451.GA17628@calvin.surfutopia.net>
	<42C473BA.9090305@commandprompt.com>
	<20050630235821.GA2163@calvin.surfutopia.net>
	<42C994BD.3000909@siunik.com> <23492.1120579796@sss.pgh.pa.us>

Tom Lane wrote:

>David Gagnon <dgagnon@siunik.com> writes:
>  
>
>> explain analyse SELECT IRNUM FROM IR
>>        INNER JOIN IT ON  IT.ITIRNUM = ANY ('{1000, 2000}') AND 
>>IT.ITYPNUM = 'M' AND IR.IRYPNUM = IT.ITYPNUM AND IR.IRNUM = IT.ITIRNUM 
>>        WHERE IRNUM = ANY ('{1000, 2000}') and IRYPNUM = 'M'
>>    
>>
>
>Those =ANY constructs are not currently optimizable at all.  You might
>get better results with "IT.ITIRNUM IN (1000, 2000)" etc.
>
>			regards, tom lane
>
>  
>
I already tried this construct.  But the statement comes from a stored 
procedure where the {1000, 2000} is an array variable (requestIds).  I 
tried to use

IT.ITIRNUM IN (requestIds) or several other variant without success.

Is there a way to make it work?  Here is the statement the statement from the store procedure.  Remenber requestIds is an array of int.


FOR inventoryTransaction IN
        SELECT DISTINCT IRNUM, IRAENUM, IRSTATUT, IRSENS, IRSOURCE, 
IRDATE, IRQTE
        FROM IR
        WHERE IRNUM = ANY (requestIds) and IRYPNUM = companyId
    LOOP

Thank for your help !!!!
/David