To: Michael Glaesmann <grzm@myrealbox.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: Many joins: monthly summaries S-L--O--W 
In-reply-to: <7C513675-03BF-11D8-B460-0005029FC1A7@myrealbox.com> 
References: <7C513675-03BF-11D8-B460-0005029FC1A7@myrealbox.com>
Comments: In-reply-to Michael Glaesmann <grzm@myrealbox.com>
	message dated "Tue, 21 Oct 2003 21:09:56 +0900"
Date: Tue, 21 Oct 2003 13:27:34 -0400
Message-ID: <4557.1066757254@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Glaesmann <grzm@myrealbox.com> writes:
> [ a very messy query ]

It does seem like that is a big query with a small query struggling to
get out.  I don't have any immediate suggestions, but I wonder whether
you wouldn't profit by reading something about how to do crosstabs and
statistics in SQL.  Joe Celko's book "SQL For Smarties" is an invaluable
resource for hard problems in SQL (be sure to get the 2nd edition).
Also, if you decide that a crosstab would help, take a look at
the crosstab functions in contrib/tablefunc.  (Celko's book only covers
SQL-standard solutions, not methods that depend on nonstandard features,
so he's at a disadvantage when covering crosstab methods.)

Also, I do have a suggestion for this:

> current_inventory_view, showing the most recent inventory qty and date, 
> is defined as
> 	SELECT inv.date, inv.product_code, inv.qty FROM inventory inv, (SELECT 
> max(inventory.date) AS date, inventory.product_code FROM inventory 
> GROUP BY inventory.product_code) curr_inv WHERE ((inv.date = 
> curr_inv.date) AND (inv.product_code = curr_inv.product_code));

If you don't mind using a Postgres-specific feature, you should be able
to make this view faster by using DISTINCT ON.  Look at the "weather
reports" example in the SELECT reference page.

			regards, tom lane