Message-ID: <3E9EF2C3.2000705@kencast.com>
Date: Thu, 17 Apr 2003 14:30:27 -0400
From: Wei Weng <wweng@kencast.com>
User-Agent: Mozilla/5.0 (Windows; U; WinNT4.0; en-US; rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: maggon@newgen.co.in
Cc: pgsql-sql@postgresql.org
Subject: Re: IN Qeury Problem
References: <PIEELGOGLBEAPIHJAMIDEEMHCHAA.maggon@newgen.co.in>
In-Reply-To: <PIEELGOGLBEAPIHJAMIDEEMHCHAA.maggon@newgen.co.in>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

What about this?

Put all those integers into a table called TempData

CREATE TABLE TempData
(
    datavalue    INTEGER;
);

CREATE INDEX ind_tempdata ON TEMPDATA (datavalue);

Then you can use query

SELECT * FROM FolderTable as ft, TempData as td
WHERE ft.ParentFolderIndex = td.datavalue;

IN query's performance has been notoriously bad in PostgreSQL 
implementation, try to avoid it.

Regards,

Wei

Sameer Maggon wrote:

>Hello,
>
>  I am facing a problem regarding the performance of postgres. I am
>concerned about the speed of the execution of a query when large number of
>entries is given in the IN clause.
>
>I have a Table with columns:
>
>FolderName (Indexed)
>FolderIndex (Indexed)
>ParentFolderIndex (Indexed)
>...
>..
>
>I have a query where i do a SELECT
>
>SELECT * FROM FolderTable WHERE PARENTFOLDERINDEX IN (38::int8, ......);
>
>If the number of values in IN clause is small, its working fine, but as and
>when the number of values increases the speed of the query is detoriating
>with a steep curve.
>
>How can this problem be solved ?
>
>Thanks and Regards
>Sameer Maggon
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>  
>