X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C6AF23A414A for ; Mon, 6 Dec 2004 20:15:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68244-05 for ; Mon, 6 Dec 2004 20:15:20 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 23F543A6014 for ; Mon, 6 Dec 2004 20:15:20 +0000 (GMT) Received: (qmail 24886 invoked from network); 6 Dec 2004 21:15:57 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 6 Dec 2004 21:15:57 +0100 To: "Pallav Kalva" Cc: pgsql-performance@postgresql.org Subject: Re: Poor Query References: <41B47A97.1090708@deg.cc> <41B48053.7090805@deg.cc> <41B48ADB.7000100@deg.cc> <41B49E3B.7030407@deg.cc> Message-ID: Date: Mon, 06 Dec 2004 21:18:28 +0100 From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <41B49E3B.7030407@deg.cc> User-Agent: Opera M2/7.54 (Linux, build 751) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/120 X-Sequence-Number: 9495 Your suffering comes from the "where ba.bankaccountID = u.bankaccountID" in the subselect. It means postgres has to run the subselect once for each row in Users. You want the subselect to run only once, and return one (or more?) bankaccountid's, then fetch the users from Users. Just remove the "where ba.bankaccountID = u.bankaccountID" ! > select userID, fname, lname, email, phone, dateEntered, dateCanceled, > dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as > searches > from Users u > where bankaccountid in (select bankaccountid > from bankaccount ba > where ba.bankaccountID = u.bankaccountID > and ba.accountnumber = '12345678' > and ba.routingNumber = '12345678') > order by UserID desc > limit 500 New version : select userID, fname, lname, email, phone, dateEntered, dateCanceled, dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as searches from Users u where bankaccountid in (select bankaccountid from bankaccount ba WHERE ba.accountnumber = '12345678' and ba.routingNumber = '12345678') You could also do this : select u.* from Users u, bankaccount ba where u.bankaccountid = ba.bankaccountid and ba.accountnumber = '12345678' and ba.routingNumber = '12345678')