Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 804284768D8 for ; Thu, 2 May 2002 17:48:41 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 4F69A476493 for ; Thu, 2 May 2002 16:56:23 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g42KuDv18066; Thu, 2 May 2002 16:56:13 -0400 (EDT) To: "Josh Berkus" Cc: pgsql-sql@postgresql.org Subject: Re: Subselect performance question In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Thu, 02 May 2002 09:37:56 -0700" Date: Thu, 02 May 2002 16:56:13 -0400 Message-ID: <18063.1020372973@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200205/16 X-Sequence-Number: 7652 "Josh Berkus" writes: > Under those circumstances, is there a hard-and-fast rule for which > query I should use? Most of the time, users will only look at one > record at a time from the main data set. > SELECT client_id, client_name, > (SELECT count(*) FROM case_clients WHERE client_id = clients.client_id > AND case_status = 'ACT') as active_count, > (SELECT count(*) FROM case_clients WHERE client_id = clients.client_id > AND case_status = 'STL') as settled_count, > FROM clients; > OR: > SELECT client_id, client_name, active_count, settled_count, > FROM clients > LEFT OUTER JOIN (SELECT client_id, count(*) as active_count FROM > case_clients WHERE case_status = 'ACT' GROUP BY client_id) ac ON > ac.client_id = clients.client_id > LEFT OUTER JOIN (SELECT client_id, count(*) as settled_count FROM > case_clients WHERE case_status = 'STL' GROUP BY client_id) sc ON > sc.client_id = clients.client_id; The second one looks like a sure loser to me, because of the GROUP BYs. If you were expecting queries to retrieve many different client_ids, it *might* be better to use the second form. But I think for a small number of client_ids the first will be quicker. regards, tom lane