Skip site navigation (1) Skip section navigation (2)

Re: Poor Query

From: Pallav Kalva <pkalva(at)deg(dot)cc>
To: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor Query
Date: 2004-12-06 18:00:27
Message-ID: 41B49E3B.7030407@deg.cc (view raw or flat)
Thread:
Lists: 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 .



In response to

  • Re: Poor Query at 2004-12-06 17:34:22 from Pierre-Frédéric Caillaud

Responses

  • Re: Poor Query at 2004-12-06 20:18:28 from Pierre-Frédéric Caillaud

pgsql-performance by date

Next:From: Andrew SullivanDate: 2004-12-06 18:57:38
Subject: Re: Alternatives to Dell?
Previous:From: Pierre-Frédéric CaillaudDate: 2004-12-06 17:34:22
Subject: Re: Poor Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group