Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 80825475BD7
	for <pgsql-sql@postgresql.org>; Sun,  6 Oct 2002 12:18:18 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 79661475A71
	for <pgsql-sql@postgresql.org>; Sun,  6 Oct 2002 12:18:14 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 8842FD602; Sun,  6 Oct 2002 09:18:19 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 7DFF25C03; Sun,  6 Oct 2002 09:18:19 -0700 (PDT)
Date: Sun, 6 Oct 2002 09:18:19 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: <pete@phillipsfamily.freeserve.co.uk>
Cc: <pgsql-sql@postgresql.org>, <tgl@sss.pgh.pa.us>,
 <kenzo@kennethambrose.com>
Subject: Re: problem with subqueries 
In-Reply-To: <200210061113.g96BDfG10468@phillipsfamily.freeserve.co.uk>
Message-ID: <20021006090327.Q65664-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/78
X-Sequence-Number: 9796


On Sun, 6 Oct 2002 pete@phillipsfamily.freeserve.co.uk wrote:

> Hi guys. Thanks for the rapid replies so far.
>
> To answer some of the questions:
>
> >you did not indicate an explicit join - or even a "from" clause for that
> >matter- in the example of your create view statement.
>
> My original post was a simplified version. Here is the actual view
> creating statement:
>
> 	create view monthord as select ord_date, extract (month from ord_date)
> 	as month, extract (year from ord_date) as year,r_region,
> 	number_of_items from orders,customer where ccode = codenum;
>
>
> >But it appears to me that you are reinventing the wheel.  Isn't this
> >query the equivalent of a grouped aggregation
>
> Yes - but again I was simplifying - I want to run a sub query for each
> region, so I get output like this:
>
> year   month	 Reg1	Reg2   Reg3   Reg4
> -----  -----	 ----	----   -----  ----
> 1999   Jan	 20	45     10     27
> 1999   Feb	 30	43     18     37
> ...
> 2002   Oct	 7	89     60     17
>
> The subquery I have tried to run is actually this (there is probably a
> way to do this all in SQL, but at present I would like to just
> understand why my subqueries take so long).

Well, you're running <n> subqueries for each row in monthcustomer
because the distinct happens afterwards in your query.  So if you've
got 4 regions and 1 total and 100,000 rows in monthcustomer, you're
looking at something on the order of 500,000 subqueries.  Doing the
distinct before that step should lower the number to
((#year/month combinations) * (#regions+1)).

In any case, you may be better off with one of:

a) Doing something programatic to turn a result set like:
 year|month|region|value
 1999|Jan  |1     |20
 1999|Jan  |2     |45
 ...
into the form you want.  The above can be gotten by group by
probably and would require no subqueries.

b) Keeping a summary table that you update via triggers.  This
 requires a bit of work to get the triggers, but it probably
 makes the query faster.