I have a crone job that runs every 10 min to sync data. For that, I am using a function to refresh the materialized view. Everything works fine but sometimes the cpu usage hits max. When I run the query below, I get the refresh materialized view function using 23 percent of cpu. How can I improve the refresh materialized view process?
query used to find the cpu %
SELECT
pss.userid,
pss.dbid,
pd.datname as db_name,
round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time,
pss.calls,
round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean,
round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg,
pss.query
FROM pg_stat_statements pss, pg_database pd
WHERE pd.oid=pss.dbid
ORDER BY 7 desc
Here is my refresh function
-- FUNCTION: refresh_mat_view()
-- DROP FUNCTION IF EXISTS refresh_mat_view();
CREATE OR REPLACE FUNCTION refresh_mat_view(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
BEGIN
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_1;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_2;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_3;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_4;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_5;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_6;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_7;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_8;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_9;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_10;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_11;
REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_12;
END;
$BODY$;