Amazon Redshift — Query/Code Compilation Cache

If you have worked with Redshift for a while you should already be aware of the result cache. It makes the subsequent runs of queries to be executed in milliseconds while the 1st execution took more like 10seconds or so on.

Well, there is another cache that is in place in Redshift that makes your queries run faster even when you have turned off the result cache. If you don’t know how to turn off your result cache fear not. Run the below query to disable the query result cache. This can be helpful to benchmark your query execution time.

Set Enable_Result_Cache_For_Session = FALSE;

How I found out

I was creating a benchmark execution period for different queries by turning the result cache off, but still, my subsequent queries ran in milliseconds while my 1st execution took around 10 seconds. Baffled with this, I dug thru some posts and AWS documentation for Redshift and found out there happen to be something called query compilation cache that makes your subsequent queries to run faster irrespective of the query cache.

How it works

The leader node is responsible to create the query execution plan and compile it for the compile nodes to execute your query for results. Once you run your query the leader node has already created the query plan, so next time you run the same query the leader node will use the same query plan for execution that makes your subsequent queries run faster than your 1st execution.

When it works

The query compilation cache can get to work if:

• If you run the same query again
• Even if you change the parameters
• Even though you run your same query in different sessions.

Conclusion

I think this can be used as an advantage to enhance the query performance. If I can get the most run queries to pre-compile by executing the queries during off hours, the subsequent queries will run faster. This solution might not be perfect but still can make a difference in the query performance. The end user/client will have faster running reports/dashboards.

See here about Result Cache.