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 BDD983A5F1D for ; Mon, 6 Dec 2004 18:00:41 +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 23932-02 for ; Mon, 6 Dec 2004 18:00:35 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id 714BB3A5FC4 for ; Mon, 6 Dec 2004 18:00:29 +0000 (GMT) Received: from deg.cc (kyle.deg.cc [198.70.16.205]) by mail.deg.cc (Postfix) with ESMTP id EEC0B56369; Mon, 6 Dec 2004 13:00:24 -0500 (EST) Message-ID: <41B49E3B.7030407@deg.cc> Date: Mon, 06 Dec 2004 13:00:27 -0500 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= Cc: pgsql-performance@postgresql.org Subject: Re: Poor Query References: <41B47A97.1090708@deg.cc> <41B48053.7090805@deg.cc> <41B48ADB.7000100@deg.cc> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200412/118 X-Sequence-Number: 9493 Pierre-Frédéric Caillaud wrote: > > >> 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... > Userid column is only in users table not in bankaccounts table , based on your suggestion i made changes to the query and here are the explain plans : 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 bankaccountid in (select bankaccountid from bankaccount ba where ba.bankaccountID = u.bankaccountID and ba.routingNumber = '12345678') order by UserID desc limit 500 QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..6642.59 rows=500 width=121) (actual time=40180.116..93650.837 rows=1 loops=1) -> Index Scan Backward using users_pkey on users u (cost=0.00..1087936.69 rows=81891 width=121) (actual time=40180.112..93650.829 rows=1 loops=1) Filter: ((subplan) AND (subplan)) SubPlan -> Index Scan using bankaccount_pkey on bankaccount ba (cost=0.00..3.08 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=3) Index Cond: (bankaccountid = $0) Filter: (routingnumber = '12345678'::text) -> Index Scan using bankaccount_pkey on bankaccount ba (cost=0.00..3.08 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=385914) Index Cond: (bankaccountid = $0) Filter: (accountnumber = '12345678'::text) Total runtime: 93684.307 ms 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 QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..1777.53 rows=500 width=121) (actual time=18479.669..63584.437 rows=1 loops=1) -> Index Scan Backward using users_pkey on users u (cost=0.00..582250.93 rows=163781 width=121) (actual time=18479.663..63584.428 rows=1 loops=1) Filter: (subplan) SubPlan -> Index Scan using bankaccount_pkey on bankaccount ba (cost=0.00..3.09 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=385914) Index Cond: (bankaccountid = $0) Filter: ((accountnumber = '12345678'::text) AND (routingnumber = '12345678'::text)) Total runtime: 63596.222 ms What's wierd is even though there is a index on bankaccountid table it doesnt use that index, it uses the index on the userid table and the execution time is little better but it still takes over a minute to execute .