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

Short description

Queries with exclusive locks on a production load can increase the lock wait time. This increase 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 whether there's a sudden increase in query planning time. A transaction that's waiting for a lock likely caused the increase in time.

Resolution

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

1. Open a new session for your first lock:

   begin; lock table1;

2. Open a second session that runs in parallel, and then run the following query:

   select * from table1 limit 1000;

   The query in this second session submits an AccessSharedLock request. Because the first session already claimed the AccessExclusiveLock, this second query must wait to access the lock. Then, the ExclusiveLock blocks all other operations on table1.

3. Check your Workload Execution Breakdown metrics. A sudden spike in query planning time can confirm that there's a transaction waiting for a lock.

4. (Optional) If a transaction waiting for a lock does exist, then manually terminate the session to release the lock:

   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