![]() On the other hand, Interleaved sort key is also made up of one or more of its columns, but each sort key column has equal importance. Both join columns are the distribution key and include the primary of Compound sort key.Īlso, Compound sort keys help improve compression of the column.Used for INNER JOIN and OUTER JOIN, not for FULL JOIN.If the criteria are not met, Redshift chooses Hash Join or Nested Loop for the join operator instead. However, the more complex tables or data are, the harder achieving Merge Join is, because the criteria are very strict. Merge Join is the fastest way to join in Redshift and that advantage can be seen in my examination, too. One of the major advantages of Compound sort key is that it enables Merge Join when the following criteria are met. For example, when you want to query the following table which has Compound sort key, the primary dateid can be used only in ORDER BY operations, but the secondary eventid is definitely used with the primary dateid otherwise the sorting performance will be degraded. Querying only by the primary column has a good effect on the speed, while the secondary column and following columns can't be powerful without simultaneously using the primary. When using Compound sorting, you need to consider the order of columns in Compound sort key, the first one is called primary and the second one is called secondary. Basically, Compound sorting is effective with these SQL operations ORDER BY, GROUP BY and PARTITION BY used in window functions. Characteristics of Compound Sort Key and Interleaved Sort KeyĬharacteristics of Compound Sort Key and Interleaved Sort KeyĬompound Sort Key and Interleaved Sort Key have detail (too detail!) characteristics.Ĭompound sort key is the default sort type in Redshift and made up of one or more of its columns.Today, I'm going to provide a brief summary of characteristics of Compound Sort Key and Interleaved Sort Key and differences between them, and then examine each SQL performance. Although you can refer to Recommendations from your Redshift Advisor which lets you know how to deal with this problem, you need to review overall sort key designs of the tables alerted to find a fundamental solution of the problem. Interleaved Sorting gives equal weight to each column, or a subset of columns, which is very convenient for database designers to use this for a bunch of tables "anyway", but this sometimes has a bad influence on Redshift performance. Recreate 5 tables to use a single-column compound sort key.īoth recommendations popped up because of using Interleaved Sort Key. Replace Single Column Interleaved Sort Keys Recomendation Run VACUUM REINDEX, as a superuser, on tables with inactive interleaved sort keys. ![]() Initialize Interleaved Sort Keys Recomendation Have you ever received something like following alerts from your Redshift Advisor? I'm not a native English speaker, so please let me know if you find something like a grammatical error. This post is English translation of the Japanese version.
0 Comments
Leave a Reply. |