Extremely slow query - Mailing list pgsql-sql

System:
OS: RedHat 7.2
Dual PIII XEON
Mem 512 mg
PG: 7.2


I have what I think is a fairly simple summary query, but it takes 1:55 to
run on just 155k records.  The query hits against a single table that I use
for reporting purposes.  This table is truncated, refreshed, reindexed, and
vacuum analysed each night.  Other than the initial table update, no other
data is added during the day.

Any help would be appreciated.  My little Win2k with a PIII 500 and 256mgs
is out performing this monster machine.

query:
SELECT  gmmid, gmmname, divid, divname, feddept, deptname, fedvend,
vendor_name, masterid, master_desc, pageid, oz_description, (    CASE       WHEN (masterid IS NULL) THEN pageid
ELSEmasterid END)::character varying(15) AS pagemaster,    CASE       WHEN (masterid IS NULL) THEN oz_description
ELSEmaster_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
ELSEmasterid    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)
THENcur_price       ELSE NULL::float8    END, price_original, price_owned_retail, cur_price, oz_color, oz_size,
 
pageflag, itemnumber, mkd_status, option4_flag

EXPLAIN ANALYSE results:
Aggregate  (cost=56487.32..72899.02 rows=15267 width=356) ->  Group  (cost=56487.32..66029.01 rows=152667 width=356)
  ->  Sort  (cost=56487.32..56487.32 rows=152667 width=356)             ->  Seq Scan on tbldetaillevel_report
detaillevel_report_v
(cost=0.00..9932.67 rows=152667 width=356)


Table Def:
CREATE TABLE tbldetaillevel_report ( pageid int4, feddept int4, fedvend int4, oz_description varchar(254),
price_owned_retailfloat8, oz_color varchar(50), oz_size varchar(50), lw_demanddollar float8, ptd_demanddollar float8,
std_demanddollarfloat8, lw_returndollar float8, ptd_returndollar float8, std_returndollar float8, lw_demand int4,
ptd_demandint4, std_demand int4, lw_returnu int4, ptd_returnu int4, std_returnu int4, divid int4, divname varchar(35),
gmmidint4, gmmname varchar(35), deptname varchar(35), total_oh int4, total_oo int4, vendorname varchar(40), dunsnumber
varchar(9),current_week int4, current_period int4, week_end date, varweek int4, varperiod int4, upc int8, pageflag
int2,upcflag int2, pid varchar(30), cur_price float8, vendor_name varchar(40), ly_lw_demanddollar float8,
ly_ptd_demanddollarfloat8, ly_std_demanddollar float8, itemnumber varchar(15), mkd_status int2, lw_1_demanddollar
float8,lw_2_demanddollar float8, lw_3_demanddollar float8, lw_4_demanddollar float8, masterid int4, master_desc
varchar(254),cur_demandu int4, cur_demanddol float8, cur_returnu int4, cur_returndol float8, wtd_demandu int4,
wtd_demanddolfloat8, wtd_returnu int4, wtd_returndol float8, total_curoh int4, total_curoo int4, curr_date date,
lw_1_demandint4, lw_2_demand int4, lw_3_demand int4, lw_4_demand int4, option4_flag int2, option3_flag int2,
price_originalfloat8, price_ticket float8
 
)





Patrick Hatcher