Re: Subselect performance question

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