From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: pete@phillipsfamily.freeserve.co.uk
Cc: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Cc: kenzo@kennethambrose.com
Subject: Re: problem with subqueries
Date: Sun, 6 Oct 2002 09:18:19 -0700 (PDT)
Message-ID: <20021006090327.Q65664-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200210061113.g96BDfG10468@phillipsfamily.freeserve.co.uk>
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.
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: sszabo@megazone23.bigpanda.com, pete@phillipsfamily.freeserve.co.uk, tgl@sss.pgh.pa.us, kenzo@kennethambrose.com
Subject: Re: problem with subqueries
In-Reply-To: <20021006090327.Q65664-100000@megazone23.bigpanda.com>
* 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