How to assign lowest priority to any long-running query

posted Apr 28, 2017, 4:08 PM by Sachchida Ojha

Assign lowest priority to any long-running query (any query running longer than say 30 minutes)

select psa.sess_id as session_id,
rpb.rqpcommand as command_id,
current_timestamp-psa.query_start as query_runtime
from gp_resq_priority_backend rpb
join pg_stat_activity psa ON rpb.rqpsession = psa.sess_id
where extract(epoch from current_timestamp-psa.query_start)/60 > 30
order by 1 desc;

then use gp_adjust_priority() command to change the priority.

Or even in one query:

select gp_adjust_priority(psa.sess_id, rpb.rqpcommand, 'LOW')
from gp_resq_priority_backend rpb
join pg_stat_activity psa ON rpb.rqpsession = psa.sess_id
where extract(epoch from current_timestamp-psa.query_start)/60 > 30
order by 1 desc;

Comments