pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Pallav Kalva <pkalva@deg.cc>
To: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor Query
Date: Mon, 06 Dec 2004 13:00:27 -0500
Message-ID: <41B49E3B.7030407@deg.cc> (raw)
In-Reply-To: <opsilkfkrucq72hf@musicbox>
References: <41B47A97.1090708@deg.cc>
	<opsilfifducq72hf@musicbox>
	<41B48053.7090805@deg.cc>
	<opsilgs8b4cq72hf@musicbox>
	<41B48ADB.7000100@deg.cc>
	<opsilkfkrucq72hf@musicbox>

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 .





view thread (9+ messages)  latest in thread

Message-ID: <41B49E3B.7030407@deg.cc>
Permalink:  ../41B49E3B.7030407@deg.cc/
Also on:    postgresql.org/message-id/41B49E3B.7030407@deg.cc

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pkalva@deg.cc, lists@boutiquenumerique.com
  Subject: Re: Poor Query
  In-Reply-To: <41B49E3B.7030407@deg.cc>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox