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 92EF33A5F9E for ; Mon, 6 Dec 2004 17:31:43 +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 12965-04 for ; Mon, 6 Dec 2004 17:31:17 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 6881F3A5EC7 for ; Mon, 6 Dec 2004 17:31:16 +0000 (GMT) Received: (qmail 9810 invoked from network); 6 Dec 2004 18:31:52 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 6 Dec 2004 18:31:52 +0100 Date: Mon, 06 Dec 2004 18:34:22 +0100 To: "Pallav Kalva" Subject: Re: Poor Query Cc: pgsql-performance@postgresql.org References: <41B47A97.1090708@deg.cc> <41B48053.7090805@deg.cc> <41B48ADB.7000100@deg.cc> 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 Message-ID: In-Reply-To: <41B48ADB.7000100@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/117 X-Sequence-Number: 9492 > Just One, user can i have only one bankaccount. Ah well, in that case : This is your query : select userID, fname, lname, email, phone, dateEntered, dateCanceled, dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as searches from Users u where 1=1 AND exists (select userID from bankaccount ba where ba.bankaccountID = u.bankaccountID and ba.accountnumber = '12345678') AND exists (select userID from bankaccount ba where ba.bankaccountID = u.bankaccountID and ba.routingNumber = '12345678') order by UserID desc limit 500 What it does is scan all users, and for each user, test if it has the accountnumber or the routingNumber you seek. You're reversing the problem : you should first look for accountnumber and routingNumber, THEN look for the user : SELECT * FROM Users WHERE bankaccountID IN (SELECT bankaccountID FROM bankaccount WHERE accountnumber = '12345678' OR/AND routingNumber = '12345678') or : SELECT * FROM Users WHERE userID IN (SELECT userID FROM bankaccount WHERE accountnumber = '12345678' OR/AND routingNumber = '12345678') There is something very strange in your query, it seems that bankaccount and Users both have a UserID column and a bankaccountID column. Is this normal ? It looks denormalized to me...