From: Pallav Kalva
Subject: Re: Poor Query
Date: ,
Msg-id: 41B4C494.5090304@deg.cc
(view: Whole thread, Raw)
In response to: Re: Poor Query  (Pierre-Frédéric Caillaud<>)
List: pgsql-performance

Tree view

Poor Query  (Pallav Kalva, )
 Re: Poor Query  (Pierre-Frédéric Caillaud<>, )
  Re: Poor Query  (Pallav Kalva, )
   Re: Poor Query  (Pierre-Frédéric Caillaud<>, )
    Re: Poor Query  (Pallav Kalva, )
     Re: Poor Query  (Pierre-Frédéric Caillaud<>, )
      Re: Poor Query  (Pallav Kalva, )
       Re: Poor Query  (Pierre-Frédéric Caillaud<>, )
        Re: Poor Query  (Pallav Kalva, )

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.



pgsql-performance by date:

From: Hasnul Fadhly bin Hasan
Date:
Subject: Re: Config Check
From: Jeff
Date:
Subject: Re: scaling beyond 4 processors