pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubselect performance question
4+ messages / 3 participants
[nested] [flat]
* Subselect performance question
@ 2002-05-02 16:37 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 4+ messages in thread
From: Josh Berkus @ 2002-05-02 16:37 UTC (permalink / raw)
To: pgsql-sql
Folks,
I have a view which can be designed with eithher a sub-select in the
SELECT clause or the FROM clause. The main query data set is small
(1000 records) but the table being filtered in the sub-select is large
(110,000 records).
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;
-Josh Berkus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subselect performance question
@ 2002-05-02 20:47 Manfred Koizar <mkoi-pg@aon.at>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 4+ messages in thread
From: Manfred Koizar @ 2002-05-02 20:47 UTC (permalink / raw)
To: pgsql-sql
On Thu, 02 May 2002 09:37:56 -0700, Josh Berkus wrote:
>Folks,
>
>I have a view which can be designed with eithher a sub-select in the
>SELECT clause or the FROM clause. The main query data set is small
>(1000 records) but the table being filtered in the sub-select is large
>(110,000 records).
Josh,
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 ...
>
>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;
... 0 (zero), because the sub-select counts 0 rows
>
>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;
>
NULL, because you do a LEFT JOIN to an empty sub-select.
SELECT c.client_id,
c.client_name,
count(ac.client_id) active_count,
count(sc.client_id) settled_count
FROM clients c
LEFT JOIN case_clients ac ON ac.client_id=c.client_id AND
ac.case_status = 'ACT'
LEFT JOIN case_clients sc ON sc.client_id=c.client_id AND
sc.case_status = 'STL'
GROUP BY c.client_id, c.client_name;
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.
>-Josh Berkus
Servus
Manfred
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subselect performance question
@ 2002-05-02 20:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-05-02 20:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"Josh Berkus" <josh@agliodbs.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subselect performance question
@ 2002-05-02 22:38 Josh Berkus <josh@agliodbs.com>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-05-02 22:38 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-05-02 22:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-02 16:37 Subselect performance question Josh Berkus <josh@agliodbs.com>
2002-05-02 20:47 ` Manfred Koizar <mkoi-pg@aon.at>
2002-05-02 22:38 ` Josh Berkus <josh@agliodbs.com>
2002-05-02 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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