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 20:44:04
Message-ID: 41B4C494.5090304@deg.cc (view raw or flat)
Thread:
Lists: pgsql-performance
Pierre-Frédéric Caillaud wrote:

>
>     Your suffering comes from the "where ba.bankaccountID = 
> u.bankaccountID"  in the subselect. It means postgres has to run the 
> subselect once for each  row in Users. You want the subselect to run 
> only once, and return one (or  more?) bankaccountid's, then fetch the 
> users from Users.
>
>     Just remove the "where ba.bankaccountID = u.bankaccountID" !
>
>> 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
>
>
> New version :
>
>  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.accountnumber = '12345678'
>             and ba.routingNumber = '12345678')
>
> You could also do this :
>
>  select u.* from Users u, bankaccount ba
>     where u.bankaccountid = ba.bankaccountid
>     and   ba.accountnumber = '12345678'
>             and ba.routingNumber = '12345678')
>
>
>
Thanks! a lot that was it , it is way much better now.


In response to

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

pgsql-performance by date

Next:From: voglerDate: 2004-12-06 22:18:14
Subject: scaling beyond 4 processors
Previous:From: RosnyDate: 2004-12-06 20:28:54
Subject: Re: TableSpace Design issues on Postgres 8.0 beta 5

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