From: Pierre-Frédéric Caillaud
Subject: Re: Poor Query
Date: ,
Msg-id: opsilr02qxcq72hf@musicbox
(view: Whole thread, Raw)
In response to: Re: Poor Query  (Pallav Kalva)
Responses: Re: Poor Query  (Pallav Kalva)
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, )

    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')





pgsql-performance by date:

From: "Rosny"
Date:
Subject: TableSpace Design issues on Postgres 8.0 beta 5
From: Hasnul Fadhly bin Hasan
Date:
Subject: Re: Config Check