Postgres Pro
Downloads
Home   >   mailing lists

Thread: IN Qeury Problem

IN Qeury Problem

From
"Sameer Maggon"
Date:
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

Re: IN Qeury Problem

From
Rajesh Kumar Mallah
Date:

Yes its a very well known problem and is
already there in the TODO. Its going to 
be fixed in 7.4 release the TODO says.

you can how every rewrite your query using 
EXISTS which is faster.


regds
mallah.

On Friday 18 Apr 2003 12:57 am, 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

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Re: IN Qeury Problem

From
Wei Weng
Date:
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
>
>
>  
>