Re: Subqueries - performance and use question

"Demel, Jeff" <Jeff(dot)Demel(at)JavelinDirect(dot)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