pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Manfred Koizar <mkoi-pg@aon.at>
To: pgsql-sql@postgresql.org
Subject: Re: Subselect performance question
Date: Thu, 02 May 2002 15:38:22 -0700
Message-ID: <web-1392296@davinci.ethosmedia.com> (raw)
In-Reply-To: <n083du4g2eue067lrc3eqqdq01aiod5dqu@4ax.com>

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



view thread (4+ messages)

Message-ID: <web-1392296@davinci.ethosmedia.com>
Permalink:  ../web-1392296@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-1392296@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, mkoi-pg@aon.at
  Subject: Re: Subselect performance question
  In-Reply-To: <web-1392296@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox