CONVOLUTED CROSS-TAB ? UNION ? INTERSECT ? 16.9 second performance.... Is there a better way ??? - Mailing list pgsql-general

Hey all....

I need to join two tables and retrieve a 75 X 15 column matrix for all the
records in table 1 across a 13 week range so the output should look like
this. I would like the missing records in Table 2 to populate the structure
with 0.00...




     Week �

      Type Code
     Description
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26

      AC1
     Activity 1
     0.0
     0.0
     1.35
     2.10
     2.56
     3.12
     4.00
     5.00
     12.10
     17.50
     21.90
     25.00
     45.50

      AC1
     Activity 2
     2.00
     1.34
     1.35
     2.10
     2.56
     8.00
     8.00
     12.11
     0.00
     0.00
     0.00
     5.00
     4.00

      .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .

      .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .
     .

      GROUP TOTALS
     2.00
     1.34
     2.70
     4.20
     5.12
     11.12
     12.00
     17.11
     12.10
     17.50
     21.90
     30.00
     49.5

















      B1
     Billing 1














      B2
     Billing 2














      GROUP TOTALS
















The two table definitions:

CREATE TABLE l_activity_type
(
  id serial NOT NULL PRIMARY KEY,
  activity_group int4 NOT NULL DEFAULT 0,
  type_code varchar(5) NOT NULL DEFAULT ''::character varying,
  type_desc varchar(50) NOT NULL DEFAULT ''::character varying,
  display_order int4 NOT NULL DEFAULT 0,
  calc_order int4 NOT NULL DEFAULT 0,
  create_dt timestamp NOT NULL DEFAULT now(),
  change_dt timestamp NOT NULL DEFAULT now(),
  active_flag bool DEFAULT true

) WITH OIDS;

l_activity_type has 75 rows

CREATE TABLE l_store_hours
(
  id serial NOT NULL PRIMARY KEY,
  l_store_id int4 NOT NULL DEFAULT 0,
  l_activity_type int4 NOT NULL REFERENCES l_activity_type(id),
  week_code int4 NOT NULL DEFAULT 0,
  year_code int4 NOT NULL DEFAULT 0,
  amount numeric(10,2) DEFAULT 0.00,
  create_dt timestamp NOT NULL DEFAULT now(),
  change_dt timestamp NOT NULL DEFAULT now(),
  change_id int4 DEFAULT 0,
  active_flag bool DEFAULT true

)  WITH OIDS;

l_store_hours has 2,131,250 rows approximately...


So far -- I have written the following function which returns a type of
"storehours":

DROP TYPE storehours CASCADE;
CREATE TYPE storehours AS (

 activity_group INTEGER,
 type_code VARCHAR(50),
 display_order INTEGER,
 type_desc VARCHAR(50),
 week_code INTEGER,
 amount NUMERIC(10,2)

);

-- DROP FUNCTION f_storehours();
CREATE OR REPLACE FUNCTION f_storehours(integer, integer, integer, integer)
RETURNS SETOF storehours AS '

DECLARE STORE ALIAS FOR $1;
DECLARE START ALIAS FOR $2;
DECLARE STOP ALIAS FOR $3;
DECLARE YEAR ALIAS FOR $4;
DECLARE ROW RECORD;
DECLARE WEEK INTEGER;

BEGIN

 FOR WEEK IN START .. STOP LOOP

  FOR ROW IN SELECT * FROM (SELECT LT.activity_group, LT.type_code,
LT.display_order, LT.type_desc,
  WEEK as week_code, 0.00 AS amount FROM l_activity_type LT

   UNION ALL

  SELECT LT.activity_group, LT.type_code, LT.display_order, LT.type_desc,
LH.week_code,
  LH.amount FROM l_activity_type LT
  JOIN l_store_hours LH ON LT.id = LH.l_activity_type AND LH.l_store_id =
STORE
  AND LH.week_code = WEEK AND LH.year_code = YEAR) AS A LOOP

   RETURN NEXT ROW;

  END LOOP;

 END LOOP;
 RETURN;


END;

' LANGUAGE  PLPGSQL;

SELECT * FROM f_storehours(365, 14, 26, 2006) ORDER BY activity_group,
display_order, type_code, week_code;

EXPLAIN ANALYZE yields the following:

 Sort  (cost=62.33..64.83 rows=1000 width=211) (actual
time=16726.014..16726.286 rows=1618 loops=1)
   Sort Key: activity_group, display_order, week_code




Regards,







Greg Patnude - Manager, Dynamic Applications Group

Data Mosaics, Inc.

2406 South Dishman-Mica Road / Suite # 6

Spokane Valley, WA  99206-6429



VOICE: (866) 904-DMSF

FAX: (509) 928-4236




   ->  Function Scan on f_storehours  (cost=0.00..12.50 rows=1000 width=211)
(actual time=16720.839..16722.757 rows=1618 loops=1)
 Total runtime: 16726.841 ms
(4 rows)