Re: Subqueries - performance and use question

"Demel, Jeff" <Jeff.Demel@JavelinDirect.com> writes:
> Here's what I came up with:

> SELECT customers.id, customers.firstname,
>   customers.lastname, customers.phone number,
>   (SELECT ar.billdate FROM ar
>      WHERE customers.customerid = ar.customerid
>      ORDER BY ar.billdate LIMIT 1)
>      AS lastarbilldate
> FROM customers
> WHERE customers.status = 'new';

Are you expecting a whole lot of answer rows from this query, or just a
few?  If just a few, this way is fine, but if a lot you probably want to
try to recast it as a join.  As is, it's pretty much like a nestloop
join of the two tables, which is good for a few result rows and not so
good for a lot.

The thing you need to make it fast as a subselect is an index on
(customerid, billdate) in that order.  Oh, you also need PG 8.1 or
later, but I see you have that.

Personally I'd just write (SELECT max(billdate) FROM ar WHERE
customers.customerid = ar.customerid) rather than trying to get cute
with ORDER BY/LIMIT --- the planner versions that are able to handle
this case decently will deal with either one about as well.

            regards, tom lane