问题描述
我在 Amazon Redshift 中编写了一个非常复杂的查询，其中包含3-4个临时表以及子查询.由于查询执行缓慢，因此我尝试将其替换为另一个查询，它使用派生表代替临时表.我只想问一下，是否可以比较两个查询的"解释"输出，以便我们可以得出结论，哪个查询在性能上都比较好(空格 >和时间).
另外，在redshift中用派生表替换临时表有多大帮助?
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 ?
推荐答案
当Redshift生成它自己的临时表(在计划中可见)时，您可以通过自己将它们创建为临时表，指定压缩和设置来优化查询.添加分发和排序键，以帮助完成表上的联接.
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.
非常慢的查询通常使用nested loop连接样式.最快的联接类型是merge join.如果可能，重写查询或修改表以使用merge join或至少使用hash join.此处的详细信息: https://docs.aws .amazon.com/redshift/latest/dg/query-performance-improvement-opportunities.html
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
可以更好地了解Redshift查询计划和执行的资源:
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
这篇关于Amazon Redshift上解释语句输出的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！
- Query Planning And Execution Workflow: