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 0337F3A5F37 for ; Mon, 6 Dec 2004 15:28:39 +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 65157-08 for ; Mon, 6 Dec 2004 15:28:22 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id 4A6013A5E84 for ; Mon, 6 Dec 2004 15:28:24 +0000 (GMT) Received: from deg.cc (kyle.deg.cc [198.70.16.205]) by mail.deg.cc (Postfix) with ESMTP id EA9B3562D4 for ; Mon, 6 Dec 2004 10:28:21 -0500 (EST) Message-ID: <41B47A97.1090708@deg.cc> Date: Mon, 06 Dec 2004 10:28:23 -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: pgsql-performance@postgresql.org Subject: Poor Query Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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/110 X-Sequence-Number: 9485 Hi Everybody, I have a performance problem with this query , it takes lot of time on the production database. is there a way to improve it ? i do vacuumdb on this database and do anlyze on the users table separately daily 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 QUERY PLAN ----------------------------------------------------------------------------------------------------- Limit (cost=0.00..12752.61 rows=500 width=120) -> Index Scan Backward using users_pkey on users u (cost=0.00..2460462.79 rows=96469 width=120) Filter: ((subplan) AND (subplan)) SubPlan -> Index Scan using bankaccount_pkey on bankaccount ba (cost=0.00..3.07 rows=1 width=0) Index Cond: (bankaccountid = $1) Filter: (routingnumber = '12345678'::text) -> Index Scan using bankaccount_pkey on bankaccount ba (cost=0.00..3.07 rows=1 width=0) Index Cond: (bankaccountid = $1) Filter: (accountnumber = '12345678'::text) I tried changing it but it still takes lot of time 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 ba.routingNumber = '12345678') order by UserID desc limit 500 QUERY PLAN ------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3309.62 rows=500 width=120) -> Index Scan Backward using users_pkey on users u (cost=0.00..1277101.86 rows=192938 width=120) Filter: (subplan) SubPlan -> Index Scan using bankaccount_pkey on bankaccount ba (cost=0.00..3.07 rows=1 width=0) Index Cond: (bankaccountid = $1) Filter: ((accountnumber = '12345678'::text) AND (routingnumber = '12345678'::text)) the users_pkey index on the primary key userid is on users table. it seems to be using index but it still takes lot of time. here is the output from the pg_class for the users and bankaccount table . Table doesnt have lot of records but this query take anywhere from 3 to 5 min to run which is really bad for us. Can we improve the performance on this query ? relname | relpages | reltuples ---------+----------+----------- users | 39967 | 385875 bankaccount | 242 | 16453 Thanks! Pallav