From: Wei Weng <wweng@kencast.com>
To: maggon@newgen.co.in
Cc: pgsql-sql@postgresql.org
Subject: Re: IN Qeury Problem
Date: Thu, 17 Apr 2003 14:30:27 -0400
Message-ID: <3E9EF2C3.2000705@kencast.com> (raw)
In-Reply-To: <PIEELGOGLBEAPIHJAMIDEEMHCHAA.maggon@newgen.co.in>
References: <PIEELGOGLBEAPIHJAMIDEEMHCHAA.maggon@newgen.co.in>
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
>
>
>
>
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-sql@postgresql.org
Cc: wweng@kencast.com, maggon@newgen.co.in
Subject: Re: IN Qeury Problem
In-Reply-To: <3E9EF2C3.2000705@kencast.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