pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: IN Qeury Problem
3+ messages / 3 participants
[nested] [flat]
* IN Qeury Problem
@ 2003-04-17 19:27 Sameer Maggon <maggon@newgen.co.in>
2003-04-17 10:00 ` Re: IN Qeury Problem Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 18:30 ` Re: IN Qeury Problem Wei Weng <wweng@kencast.com>
0 siblings, 2 replies; 3+ messages in thread
From: Sameer Maggon @ 2003-04-17 19:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: IN Qeury Problem
2003-04-17 19:27 IN Qeury Problem Sameer Maggon <maggon@newgen.co.in>
@ 2003-04-17 10:00 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-17 10:00 UTC (permalink / raw)
To: maggon@newgen.co.in; pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: IN Qeury Problem
2003-04-17 19:27 IN Qeury Problem Sameer Maggon <maggon@newgen.co.in>
@ 2003-04-17 18:30 ` Wei Weng <wweng@kencast.com>
1 sibling, 0 replies; 3+ messages in thread
From: Wei Weng @ 2003-04-17 18:30 UTC (permalink / raw)
To: maggon@newgen.co.in; +Cc: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-17 19:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-17 19:27 IN Qeury Problem Sameer Maggon <maggon@newgen.co.in>
2003-04-17 10:00 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 18:30 ` Wei Weng <wweng@kencast.com>
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