Re: Extremely slow query

Did you know that you can probably change your GROUP BY clause to use a
column ref, rather than repeating the CASE statement:

GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend,
vendor_name, masterid, master_desc, pageid, oz_description, 13,
price_original, price_owned_retail, cur_price,
oz_color, oz_size,
pageflag, itemnumber, mkd_status, option4_flag

Doesn't help performance, but does help clarity :)

Chris

> query:
> SELECT gmmid, gmmname, divid, divname, feddept, deptname, fedvend,
> vendor_name, masterid, master_desc, pageid, oz_description, (
> CASE
> WHEN (masterid IS NULL) THEN pageid
> ELSE masterid END)::character varying(15) AS pagemaster,
> CASE
> WHEN (masterid IS NULL) THEN oz_description
> ELSE master_desc
> END AS pagemaster_desc,
> CASE
> WHEN (masterid IS NULL) THEN price_original
> ELSE NULL::float8
> END AS org_price_display,
> CASE
> WHEN (masterid IS NULL) THEN cur_price
> ELSE NULL::float8
> END AS cur_price_display, price_original, price_owned_retail,
> cur_price, oz_color, oz_size, pageflag, itemnumber,
> sum(cur_demandu + cur_returnu) AS cur_net_units,
> sum(cur_demanddol + wtd_returndol) AS cur_net_dollar,
> sum(wtd_demandu + wtd_returnu) AS wtd_net_units,
> sum(wtd_demanddol + wtd_returndol) AS wtd_net_dollar,
> sum(lw_demand + lw_returnu) AS lw_net_units,
> sum(lw_demanddollar + lw_returndollar) AS lw_net_dollar,
> sum(ptd_demanddollar + ptd_returndollar) AS ptd_net_dollar,
> sum(ptd_demand + ptd_returnu) AS ptd_net_units,
> sum(std_demanddollar + std_returndollar) AS std_net_dollar,
> sum(std_demand + std_returnu) AS std_net_units,
> sum(total_curoh) AS total_curoh,
> sum(total_curoo) AS total_curoo,
> sum((float8(total_curoh) * price_owned_retail)) AS curoh_dollar,
> sum((float8(total_curoo) * price_owned_retail)) AS curoo_dollar,
> sum(total_oh) AS total_oh,
> sum(total_oo) AS total_oo,
> sum((float8(total_oh) * price_owned_retail)) AS oh_dollar,
> sum((float8(total_oh) * price_owned_retail)) AS oo_dollar,
> mkd_status,
> option4_flag
> FROM tbldetaillevel_report detaillevel_report_v
> GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend,
> vendor_name, masterid, master_desc, pageid, oz_description,
> CASE
> WHEN (masterid IS NULL) THEN pageid
> ELSE masterid
> END,
> CASE
> WHEN (masterid IS NULL) THEN oz_description
> ELSE master_desc
> END,
> CASE
> WHEN (masterid IS NULL) THEN price_original
> ELSE NULL::float8
> END,
> CASE
> WHEN (masterid IS NULL) THEN cur_price
> ELSE NULL::float8
> END, price_original, price_owned_retail, cur_price,
> oz_color, oz_size,
> pageflag, itemnumber, mkd_status, option4_flag