Re: IN Qeury Problem

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
>
>
>
>