From: Manfred Koizar <mkoi-pg@aon.at>
To: pgsql-sql@postgresql.org
Subject: Re: Subselect performance question
Date: Thu, 02 May 2002 22:47:13 +0200
Message-ID: <n083du4g2eue067lrc3eqqdq01aiod5dqu@4ax.com> (raw)
In-Reply-To: <web-1391672@davinci.ethosmedia.com>
References: <web-1391672@davinci.ethosmedia.com>
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
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: mkoi-pg@aon.at
Subject: Re: Subselect performance question
In-Reply-To: <n083du4g2eue067lrc3eqqdq01aiod5dqu@4ax.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