My query planning time in Amazon Redshift is much longer than the actual execution time. Why is this happening?
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.
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:
For more information about releasing locks, see How do I detect and release locks in Amazon Redshift?
Analyzing Workload Performance
Query Planning and Execution Workflow