How can I improve the speed of materialized view refreshes and reduce CPU usage

69 views Asked by At

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$;
0

There are 0 answers