Greenplum: How to reset the Priority of Running Statement or Transaction?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing one of the important articles on how to reset the priority of running or active statement of Greenplum.

The Work Load management of Greenplum means – define the different Resource Queues with options like priority, memory, CPU.
RQ associates with the user and all statement of this user will execute basis on specified priority setting in that RQ.

Let me give you one scenario, where I used this.

Recently, I found one long running query in our Greenplum server.
I checked the priority of that running query which was LOW, and that only one query was running at that time.

So now, I wanted to complete that query as soon as possible, so the solution was to change the priority from LOW to HIGH. – For only that running query.

I used below script and changed the priority and query got completed in next six minutes. We can use gp_adjust_priority() to change the priority of running statements.

Be careful – Below query will alter the priority of queries which are running more than 20 minutes.
So if you want to analyze the queries, you should use * and comment — gp_adjust_priority(psa.sess_id,rpb.rqpcommand,’HIGH’)