pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMany joins: monthly summaries S-L--O--W
3+ messages / 3 participants
[nested] [flat]
* Many joins: monthly summaries S-L--O--W
@ 2003-10-21 12:09 Michael Glaesmann <grzm@myrealbox.com>
0 siblings, 2 replies; 3+ messages in thread
From: Michael Glaesmann @ 2003-10-21 12:09 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
Hello all!
I've been working with PostgreSQL now for about a year and have really
enjoyed it. And I've been able to impress the boss with very simple
PHP/PostgreSQL web applications, so that's been great too!
Nearly all I've done has been very straightforward and basic: simple
selects from a couple of different tables joined by where clauses. But
now I'd like to do something a bit more complex: show sales per month,
current inventory, and expected duration of inventory at present sales
rates for each item we have. All in one relation :)
Here are the relevant relations
products (code TEXT PK, name TEXT)
orders (product_code TEXT FK products(id), date DATE, branch_id INTEGER
FK branches(id), qty INTEGER)
inventory (product_code INTEGER FK products(id), date DATE, qty INTEGER)
What I'm trying to make is a table with columns
product_code,
product_name,
jan03_qty,...,
half1_qty,
jul03_qty, ...
sep03_qty,
half2_qty,
total_qty,
inv_qty,
inv_date,
est_inv_qty,
months_remaining
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).
I've got something that works, but it's *extremely* slow. It takes
about 10 minutes running on a 500MHz G4 Mac with 1GB RAM, running OS
10.2.8 and PostgreSQL 7.3.3. Here's what I've done:
select
products.code as product_code,
products.name as product_name,
jan03.qty as jan03_qty,
feb03.qty as feb03_qty ...
inv.qty as inv_qty,
est_inv.qty as est_inv_qty,
months_remaining::numeric(8,1)
from products
left join (
select products.code as product_code, sum(qty) as qty
from orders, products
where products.code = orders.id and
date between '2003-01-01' and '2003-01-31'
group by product_code) as
jan03 on (jan03.product_code = products.code)
left join (
select products.code as product_code, sum(qty) as qty
from orders, products
where products.code = orders.id and
date between '2003-02-01' and '2003-02-28'
group by product_code) as
feb03 on (feb03.product_code = products.code)
left join
-- repeat through total_qty
total on (total.product_code = products.code)
left join (
-- this is where it get's hairy
select
est_inventory.product_code as product_code,
est_inventory.qty,
monthly_averages.monthly_average,
(est_inventory.qty/monthly_average)::numeric(10,1)
as months_remaining
from (
select
inventory.product_code as product_code,
inventory.qty - coalesce(orders.qty,0) as qty
from (
select product_code, date, qty
from current_inventory_view
) as
inventory
left outer join (
select orders.product_code as product_code,
sum(orders.qty) as qty
from (
select product_code, date, qty
from current_inventory_view
) as
inventory,
orders
where
orders.date > inventory.date and
orders.product_code = inventory.product_code
group by orders.product_code
) as
orders on (inventory.product_code = orders.product_code)
) as
est_inventory
left outer join (
select
product_code as product_code,
sum(qty)/ageinmonths(timestamp '9/30/2003',
timestamp '1/1/2003') as monthly_average
from orders
where date between '1/1/2003' and '9/30/2003'
group by product_code
) as
monthly_averages on
(est_inventory.product_code = monthly_averages.product_code)
where monthly_average > 0
) as
remaining on (remaining.product_code = products.code)
left join (
select distinct product_code, date, qty
from current_inventory_view order by date desc
) as
inventory on (inventory.product_code = products.code)
; -- finally
ageinmonths is an sql function that returns a double precision float,
the number of months between $1 and $2, defined as follows
select
12 * date_part ('year',age($1, $2)) +
date_part('month',age($1,$2)) +
date_part('day'), age($1, $2))/30;
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));
orders is indexed on date and product_code (and branch_id, but that
shouldn't matter)
inventory is indexed on date
products is unindexed besides the order_pkey index that's
auto-generated.
I'm using left joins because not all items sell each month. Items that
have zero sales would fall out if I used WHERE clauses.
Now, I've done an EXPLAIN on the monstrosity and it comes to just over
3 pages printed at 8 pt on A3 landscape. It's much more than I can take
in. I've looked through the EXPLAIN documentation and am willing to
learn (which is good, because I definitely need to).
I tried a version of this, truncating the 'hairy' part. It definitely
ran faster, taking less than 5 minutes.
Also, this query requires more upkeep than I'd like. I modify it every
time I want to add a new month. It seems like I should be able to use a
custom function so I can just put in the date span I'd like to cover.
I've started to work on a function and have run into some problems (see
separate post: Custom function problems if you're interested )
Any suggestion would be appreciated. It seems like this should be
something that's easy, and I'm approaching it wrong. If I'm completely
on the wrong track, I'd love to know! Also, places I should/could look
for ways to accomplish this, that'd be great.
If you've made it to here, thanks for your perseverance! : )
Regards,
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Many joins: monthly summaries S-L--O--W
@ 2003-10-21 16:48 Josh Berkus <josh@agliodbs.com>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2003-10-21 16:48 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Many joins: monthly summaries S-L--O--W
@ 2003-10-21 17:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Glaesmann <grzm@myrealbox.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-10-21 17:27 UTC (permalink / raw)
To: Michael Glaesmann <grzm@myrealbox.com>; +Cc: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-10-21 17:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-21 12:09 Many joins: monthly summaries S-L--O--W Michael Glaesmann <grzm@myrealbox.com>
2003-10-21 16:48 ` Josh Berkus <josh@agliodbs.com>
2003-10-21 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
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