Why is my query planning time so high in Amazon Redshift?

Short Description

If there are queries with exclusive locks on a production load, the lock wait time can increase. This causes your query planning time in Amazon Redshift to be much longer than the actual execution time. Check the Workload Execution Breakdown metric to see if there is a sudden increase in query planning time. This increase in time is likely caused by a transaction that's waiting for a lock.

Resolution

To detect a transaction that's waiting for a lock, perform the following steps:

1.    Open a new session for your first lock:

begin; lock table1;

2.    Open a second session that runs in parallel:

select * from table1 limit 1000;

The query in this second session submits an AccessSharedLock request. However, the query must wait for the AccessExclusiveLock, because the first session has already claimed it. The ExclusiveLock then blocks all other operations on table1.

3.    Check your Workload Execution Breakdown metrics. A sudden spike in query planning time confirms that there is a transaction waiting for a lock.

4.    (Optional) If a transaction waiting for a lock exists, then release the lock by manually terminating the session:

select pg_terminate_backend(PID);

For more information about releasing locks, see How do I detect and release locks in Amazon Redshift?

Related Information

Analyzing Workload Performance

Query Planning and Execution Workflow