From: David Gagnon <dgagnon@siunik.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why the planner is not using the INDEX .
Date: Tue, 05 Jul 2005 13:53:59 -0400
Message-ID: <42CAC937.4080702@siunik.com> (raw)
In-Reply-To: <23492.1120579796@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dgagnon@siunik.com, tgl@sss.pgh.pa.us
Subject: Re: Why the planner is not using the INDEX .
In-Reply-To: <42CAC937.4080702@siunik.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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