Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 47736475B19 for ; Thu, 2 May 2002 19:32:52 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 7B47947612F for ; Thu, 2 May 2002 18:38:21 -0400 (EDT) Received: from [209.122.114.82] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1392296; Thu, 02 May 2002 15:38:22 -0700 From: "Josh Berkus" Subject: Re: Subselect performance question To: Manfred Koizar , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Thu, 02 May 2002 15:38:22 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200205/17 X-Sequence-Number: 7653 Manfred, > I don't know, if it matters, but your queries are not equivalent. If > for a client_id there is no row with case_status = 'ACT', > active_count > is ... Thanks. Would have noticed that, and done a COALESCE. > should match your first query and it doesn't use sub-selects at all. > I haven't tested it though, so watch out for typos ... > My personal hard-and-fast rule is "avoid sub-selects, use joins", > but > YMMV. Yes, usually. There's other reasons in how I'm going to use the view, though, that make me want to use a subselect. Actually, somebody reminded me that if I have a fixed list of categories, the fastest appoach performance-wise is to use a crosstab identity martix. What I did is to create and populate a table as follows: case_status_crosstab status_group act_count stl_count dis_count oth_count ---------------- --------- --------- --------- --------- Closed\ Cases 0 0 0 0 Active\ Cases 1 0 0 0 Settled\ Cases 0 1 0 0 Dismissed\ Cases 0 0 1 0 Other\ Cases 0 0 0 1 Special\ Cases 0 0 0 1 Then I can count them as follows: CREATE VIEW bv_clients AS SELECT client_id, client_name, short_name, ljo.description as office_location, contact_name, phone_no, fax_no, status_label, address_1, address_2, address_3, address_4, address_5, address_6, city, state, zip_code, comments, active_count, settled_count, dismissed_count, other_count FROM clients JOIN status ON (clients.status = status.status AND status.relation = 'clients') LEFT OUTER JOIN text_list_values ljo ON (clients.jw_office = ljo.list_value and ljo.list_name ILIKE 'office location') LEFT OUTER JOIN (SELECT client_id, sum(act_count) as active_count, sum(stl_count) as settled_count, sum(dis_count) as dismissed_count, sum(oth_count) as other_count FROM case_clients cc JOIN text_list_values tvl ON (cc.case_status = tvl.list_value and list_name ilike 'case status') JOIN case_status_crosstab cst ON tvl.rollup1 = cst.status_group GROUP BY client_id) as c_count USING (client_id); This actually runs pretty fast, once everything is indexed. -Josh Berkus