On Sun, 6 Oct 2002 [EMAIL PROTECTED] 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. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
