From: | Vinay Gupta <vdadagupta(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Concat bigint Array Values in a Function |
Date: | 2014-11-11 00:03:40 |
Message-ID: | CAEzDN6xVUn5VP6rP+U2ZVrcw-39uME0dLLJjZDTEyfzRzdG++Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robin Ranjit Singh Chauhan | 2014-11-11 00:10:16 | repmgr |
Previous Message | Keith Fiske | 2014-11-10 22:04:35 | Updating timezone setting |