Re: [PERFORM] Poor Query

[Pallav Kalva]

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 .


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html