Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Michael Glaesmann <grzm@myrealbox.com>,
	pgsql-novice@postgresql.org
Subject: Re: Many joins: monthly summaries S-L--O--W
Date: Tue, 21 Oct 2003 09:48:19 -0700
User-Agent: KMail/1.4.3
References: <7C513675-03BF-11D8-B460-0005029FC1A7@myrealbox.com>
In-Reply-To: <7C513675-03BF-11D8-B460-0005029FC1A7@myrealbox.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200310210948.19592.josh@agliodbs.com>

Micheal,

> where product_name is products(name), jan03_qty is the quantity of
> sales in January, 2003, half1_qty is quantity of sales from January
> through June, 2003, inv_qty is the latest inventory data we have,
> inv_date is the date of that inventory data, est_inv_qty is the
> estimated current inventory based on inv_qty and sales since inv_date,
> and months_remaining is an estimate of how many months the estimated
> inventory will last at average sales rates (calculated using the
> average monthly sales for the previous months).

Well, I wouldn't recommend your left outer join approach.   You're making the 
query do far too much work.   There are a number of different ways to solve 
the "crosstab" problem, and the outer join method is only really good for 
small data sets.

I'd suggest instead that you use the "aggregate grid" method:

Construct a table like this, called month_xtab:

month_no	jan_ct  feb_ct  mar_ct ....       half_ct       half2_ct 
1		1		0		0		1		0
2		0		1		0		1		0
3		0		0		1		1		0
...
12		0		0		0		0		1

Then you can do monthly crosstabs like:

SELECT item, SUM(no_sold * jan_ct) as jan_qty, SUM(no_sold * feb_ct) as 
feb_qty .... SUM (no_sold * half_ct) as half_qty, SUM(no_sold) as tot_qty
FROM sales, month_xtab 
WHERE (extract(month from sales_date) = month_no and extract(year from 
sales_date) = 2003)

This is much, much faster than the outer join method for large numbers of 
columns.  For better performance, make an index on extract(month from 
sales_date).

This doesn't solve your whole query problem, but it's a good start.

This solution, and other tips, can be found in Joe Celko's "SQL for Smarties"

-- 
Josh Berkus
Aglio Database Solutions
San Francisco