Re: Poor Query

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 .