Re: Poor Query - Mailing list pgsql-performance

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 .