Hi,
I need to optimise and aggregate array integer values in a function and i am doing this by below stored function :
-- type def
CREATE TYPE fun_type AS (
g_id integer,
zip_id_list integer[],
city_id_list integer[],
state_id_list integer[],
lat_long_id_list bigint[],
country_id_list bigint[],
ccid_list bigint[],
cr_id_list bigint[],
is_active boolean
);
-- fun def
CREATE OR REPLACE FUNCTION fun_multicountry()
RETURNS SETOF fun_type AS
$BODY$
DECLARE
row1 fun_type%ROWTYPE;
dt1 record;
dt1outer record;
BEGIN
FOR dt1outer IN SELECT DISTINCT g_id FROM tableA
LOOP
row1.country_id_list = '{}';
row1.state_id_list = '{}';
row1.city_id_list = '{}';
row1.lat_long_id_list = '{}';
row1.zip_id_list = '{}';
row1.ccid_list = '{}';
row1.cr_id_list = '{}';
row1.g_id = dt1outer.g_id;
row1.is_active = false;
FOR dt1 IN SELECT * FROM tableA LEFT OUTER JOIN tableB
ON (pr_id = tableB.id) where
g_id = dt1outer.g_id AND tableA.is_active = true
LOOP
row1.is_active = true;
IF(dt1.geot_type_id =1 and dt1.pr_id is not NULL)
THEN
row1.cr_id_list = row1.cr_id_list|| dt1.targeting_ids;
ELSIF( (dt1.state_id_list is null or dt1.state_id_list = '{}') AND
(dt1.city_id_list is null or dt1.city_id_list = '{}') AND
(dt1.lat_long_id_list is null or dt1.lat_long_id_list = '{}') AND
(dt1.zip_id_list is null or dt1.zip_id_list = '{}'))
THEN
row1.country_id_list = row1.country_id_list || dt1.country_id;
ELSE
row1.state_id_list = row1.state_id_list || dt1.state_id_list;
row1.city_id_list = row1.city_id_list || dt1.city_id_list;
row1.lat_long_id_list = row1.lat_long_id_list || dt1.lat_long_id_list;
row1.zip_id_list = row1.zip_id_list || dt1.zip_id_list;
END IF;
IF(dt1.ccid_list is null or dt1.ccid_list = '{}')
THEN
row1.ccid_list = row1.ccid_list || -dt1.country_id;
ELSE
row1.ccid_list = row1.ccid_list || dt1.ccid_list;
END IF;
END LOOP;
return NEXT row1;
END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
Above function is taking ~ 30 -40 sec to finish where as table rows are :
explain analyze select fun_multicountry();
QUERY PLAN
----------------------------------------------------------------------------------------------------
Result (cost=0.00..5.25 rows=1000 width=0) (actual time=21855.881..21959.683 rows=420286 loops=1)
Total runtime: 31977.712 ms
(2 rows)
Time: 21978.103 ms
select count(*) from tableA;
count
--------
629439
(1 row)
Time: 135.858 ms
select count(*) from tableB;
count
-------
841
select count(distinct g_id) from tableA;
count
--------
420287
I need to aggregate all different array values in table depending upon above if conditions and return them. Is dere any optimal approach to do this as i am calling this function in many other functions and its just adding to slowness. I am trying to rewrite using CASE statements. Will this help ?
Please let me know if there is any optimal way to finish this function in ~ 2-3 secs
Thanks
