From: Tom Lane <tgl@sss.pgh.pa.us>
To: pete@phillipsfamily.freeserve.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: problem with subqueries
Date: Sat, 05 Oct 2002 21:01:33 -0400
Message-ID: <6763.1033866093@sss.pgh.pa.us> (raw)
In-Reply-To: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk>
References: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk>
pete@phillipsfamily.freeserve.co.uk writes:
> select distinct year,month,
> (select sum(monthcustomer.number_of_items) from monthcustomer where
> monthcustomer.year=m.year and monthcustomer.month=m.month) as NumPots
> from monthcustomer m;
> This goes off and never comes back -
No surprise, considering the sub-select is going to be evaluated
separately for every row of monthcustomer --- and then most of those
evaluations will be thrown away by the DISTINCT :-(
A straightforward way of reducing the redundant computations would be
to do the DISTINCT first:
select year,month,
(select sum(monthcustomer.number_of_items) from monthcustomer where
monthcustomer.year=m.year and monthcustomer.month=m.month) as NumPots
from
(select distinct year, month from monthcustomer) as m;
But it appears to me that you are reinventing the wheel. Isn't this
query the equivalent of a grouped aggregation --- viz,
select year, month, sum(number_of_items) as NumPots
from monthcustomer
group by year, month
regards, tom lane
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: tgl@sss.pgh.pa.us, pete@phillipsfamily.freeserve.co.uk
Subject: Re: problem with subqueries
In-Reply-To: <6763.1033866093@sss.pgh.pa.us>
* 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