Comparison of Explain Statement Output on Amazon R

Question

I have written a very complicated query in Amazon Redshift which comprises of 3-4 temporary tables along with sub-queries.Since, Query is slow in execution, I tried to replace it with another query, which uses derived tables instead of temporary tables. I just want to ask, Is there any way to compare the "Explain" Output for both the queries, so that we can conclude which query is working better in performance(both space and time).
Also, how much helpful is replacing temporary tables with derived tables in redshift ?

Answer 1

When Redshift generates it's own temporary tables (visible in the plan) then you may be able to tune the query by creating them as temporary tables yourself, specifying compression and adding distribution and sort keys that help with joins done on the table.

Very slow queries typically use a nested loop join style. The fastest join type is a merge join. If possible, rewrite the query or modify the tables to use merge join or at least hash join. Details here: https://docs.aws.amazon.com/redshift/latest/dg/query-performance-improvement-opportunities.html

Resources to better understand Redshift query planning and execution:

• Query Planning And Execution Workflow:
  • https://docs.aws.amazon.com/redshift/latest/dg/c-query-planning.html
• Reviewing Query Plan Steps:
  • https://docs.aws.amazon.com/redshift/latest/dg/reviewing-query-plan-steps.html
• Mapping the Query Plan to the Query Summary:
  • https://docs.aws.amazon.com/redshift/latest/dg/query-plan-summary-map.html
• Diagnostic Queries for Query Tuning:
  • https://docs.aws.amazon.com/redshift/latest/dg/diagnostic-queries-for-query-tuning.html