From: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>
Subject: Suggestion to optimize performance of the PLSQL procedure.
Date: Thu, 12 Jul 2018 03:18:49 +0000
Message-ID: <107339794f794e2781b8ac1298ade76c@cyient.com> (raw)
Dear expert,
Could you please review and suggest to optimize performance of the PLSQL procedure in PostgreSQL?
I have attached the same.
Thanks in advance
Regards,
Dinesh Chandra
________________________________
DISCLAIMER:
This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Check all attachments for viruses before opening them. All views or opinions presented in this e-mail are those of the author and may not reflect the opinion of Cyient or those of our affiliates.
-----Function to get weather forcast based on threshold value of weather parameters
CREATE OR REPLACE FUNCTION dome.geteffectiveforcast4(
coldmaxtemp double precision,
coldmintemp double precision,
hotmaxtemp double precision,
hotmintemp double precision,
windmax double precision,
windmin double precision,
rainmax double precision,
rainmin double precision,
humiditymax double precision,
humiditymin double precision,
snowmax double precision,
snowmin double precision)
RETURNS SETOF "TABLE(lockey text, from_time timestamp without time zone, to_time timestamp without time zone, weathertype text, flood_height text, severity text)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature"<0
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature">=coldmintemp and "Temperature"<= coldmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Hot Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature" > hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature" between hotmintemp and hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" >windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" between windmin and windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Rain' as WeatherType,
s."Flood_height",s."Severity" from (select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
,fc."Flood_height",fc."Severity" from dome."AccuWeatherForcastHourly",dome.flood_config2 fc
where "Rain" >rainmax and "Rain" between fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Rain' as WeatherType,s."Flood_height",s."Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
,fc."Flood_height",fc."Severity" from dome."AccuWeatherForcastHourly",dome.flood_config2 fc
where "Rain" between rainmin and rainmax and "Rain" between fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Snow" >snowmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Snow" between snowmin and snowmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Humidity' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Humidity" >humiditymax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Humidity' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Humidity" between humiditymin and humiditymax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp;
END;
$function$;
-----Query executing the above Weather Forcast function
Select GA.*,forcast.from_time::date as fromdate,forcast.from_time::time as fromtime,forcast.to_time::date as todate,forcast.to_time::time as totime,forcast.weathertype,forcast.flood_height,forcast.severity as flood_severity from dome.gridassetinfo3 GA ,dome.geteffectiveforcast4({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}) forcast where forcast.lockey = cast(GA.lockey as text)
---Function to get lightning impacted asset information
CREATE OR REPLACE FUNCTION dome.getlightningimpactedassets(
asset_table text,
buffer double precision)
RETURNS SETOF "TABLE(lightningdate date, lightningtime time without time zone, latitude double precision, longitude double precision, amplitude double precision, distance double precision, feedername character varying, assetid bigint, assetcategory character varying, asset_xy character varying, customer_xy character varying)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY Select light.*,ga.feedername,ga.assetid,ga.assetcategory,ga.asset_xy,ga.customer_xy from dome.lightning light join dome.gridassetinfo3 ga
on st_intersects(st_buffer(st_point(light.latitude,light.longitude),buffer),ST_GeomFromText('MULTIPOINT('||ga.asset_xy||')'))
where ga.assettable=asset_table;
END;
$function$;
Attachments:
[text/plain] plsql procedure-funcion performance..txt (9.6K, ../107339794f794e2781b8ac1298ade76c@cyient.com/3-plsql%20procedure-funcion%20performance..txt)
download | inline:
-----Function to get weather forcast based on threshold value of weather parameters
CREATE OR REPLACE FUNCTION dome.geteffectiveforcast4(
coldmaxtemp double precision,
coldmintemp double precision,
hotmaxtemp double precision,
hotmintemp double precision,
windmax double precision,
windmin double precision,
rainmax double precision,
rainmin double precision,
humiditymax double precision,
humiditymin double precision,
snowmax double precision,
snowmin double precision)
RETURNS SETOF "TABLE(lockey text, from_time timestamp without time zone, to_time timestamp without time zone, weathertype text, flood_height text, severity text)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature"<0
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature">=coldmintemp and "Temperature"<= coldmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Hot Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature" > hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature" between hotmintemp and hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" >windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" between windmin and windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Rain' as WeatherType,
s."Flood_height",s."Severity" from (select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
,fc."Flood_height",fc."Severity" from dome."AccuWeatherForcastHourly",dome.flood_config2 fc
where "Rain" >rainmax and "Rain" between fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Rain' as WeatherType,s."Flood_height",s."Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
,fc."Flood_height",fc."Severity" from dome."AccuWeatherForcastHourly",dome.flood_config2 fc
where "Rain" between rainmin and rainmax and "Rain" between fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Snow" >snowmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Snow" between snowmin and snowmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Humidity' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Humidity" >humiditymax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Humidity' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Humidity" between humiditymin and humiditymax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp;
END;
$function$;
-----Query executing the above Weather Forcast function
Select GA.*,forcast.from_time::date as fromdate,forcast.from_time::time as fromtime,forcast.to_time::date as todate,forcast.to_time::time as totime,forcast.weathertype,forcast.flood_height,forcast.severity as flood_severity from dome.gridassetinfo3 GA ,dome.geteffectiveforcast4({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}) forcast where forcast.lockey = cast(GA.lockey as text)
---Function to get lightning impacted asset information
CREATE OR REPLACE FUNCTION dome.getlightningimpactedassets(
asset_table text,
buffer double precision)
RETURNS SETOF "TABLE(lightningdate date, lightningtime time without time zone, latitude double precision, longitude double precision, amplitude double precision, distance double precision, feedername character varying, assetid bigint, assetcategory character varying, asset_xy character varying, customer_xy character varying)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY Select light.*,ga.feedername,ga.assetid,ga.assetcategory,ga.asset_xy,ga.customer_xy from dome.lightning light join dome.gridassetinfo3 ga
on st_intersects(st_buffer(st_point(light.latitude,light.longitude),buffer),ST_GeomFromText('MULTIPOINT('||ga.asset_xy||')'))
where ga.assettable=asset_table;
END;
$function$;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Dinesh.Chandra@cyient.com, pgsql-performance-owner@postgresql.org
Subject: Re: Suggestion to optimize performance of the PLSQL procedure.
In-Reply-To: <107339794f794e2781b8ac1298ade76c@cyient.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox