CREATE RESOURCE QUEUE

A resource queue must have either an ACTIVE_STATEMENTS or a MAX_COST value (or it can have both). Only a superuser can create a resource queue.

CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ... ]) 

where queue_attribute is:

ACTIVE_STATEMENTS=integer

[ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}] ]

[ MIN_COST=float ]

[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]

[ MEMORY_LIMIT='memory_units' ]

| MAX_COST=float [ COST_OVERCOMMIT={TRUE|FALSE} ]

[ ACTIVE_STATEMENTS=integer ]

[ MIN_COST=float ]

[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]

[ MEMORY_LIMIT='memory_units' ]

When you look the DDL from pgadmin3 it looks like different than what you created. Here is the mapping.

CREATE RESOURCE QUEUE demorq

ACTIVE THRESHOLD 2 --> Maximum Active statement . we can set it 4 if but to get better performance keep it 2

COST THRESHOLD 5000000 --> Represent max query cost . Cost is measured in units of disk page fetch. So for ACTIVE THRESHOLD 2 max cost/query 50 million

IGNORE THRESHOLD 100000 --> Smaller Queries that cost <100000 bypass the queue

NOOVERCOMMIT; --> Queries that exceed the cost limit will always be rejected

1. If you want more high cost queries to run at the same time then increase ACTIVE THRESHOLD. Do not increase it more than 4. We will get more errors if we increase it more than 4.

2. If you want to increase the cost threshold/query keeping the ACTIVE THRESHOLD 2 then just increase the COST THRESHOLD. It is 5 million (2.5 million/query) now.

3. If you want to run more small queries costing <100000 then you can just increase the IGNORE THRESHOLD value.

Examples

Create a resource queue with an active query limit of 20:

CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20);

Create a resource queue with an active query limit of 20 and a total memory limit of 2000MB (each query will be allocated 100MB of segment host memory at execution time):

CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20, MEMORY_LIMIT='2000MB');

Create a resource queue with a query cost limit of 3000.0:

CREATE RESOURCE QUEUE myqueue WITH (MAX_COST=3000.0);

Create a resource queue with a query cost limit of 310 (or 30000000000.0) and do not allow overcommit. Allow small queries with a cost under 500 to run immediately:

CREATE RESOURCE QUEUE myqueue WITH (MAX_COST=3e+10, COST_OVERCOMMIT=FALSE, MIN_COST=500.0);

Create a resource queue with both an active query limit and a query cost limit:

CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=30, MAX_COST=5000.00);

Create a resource queue with an active query limit of 5 and a maximum priority setting:

CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=5, PRIORITY=MAX);