Re: Extremely slow query - Mailing list pgsql-sql

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