Sort Keys are just one key ingredient of performance tuning on Amazon Redshift.
#Redshift create table as select how to
Here you can find an example of how to do this. It also always helps to run tests with your actual data before you come up with a good scheme of Sort Keys. Useful ResourcesĪs always, it helps to start with the documentation that is provided by Amazon. So, plan accordingly and make sure you have a good understanding of your data as an analyst and consult the SVV_INTERLEAVED_COLUMNS table for vital statistics on your tables that help you figure out the best possible Vacuuming strategy. As it is preferred to use this type with large tables, the result might be long VACUUMING times. In general, Interleaved Sort Keys are more sensitive to VACUUMING, and usually, it takes longer to perform it on tables that have this kind of sort keys defined.
The Sort Key Type affects the performance of your VACUUMING process. VACUUMING your tables is unavoidable, and you have to consider the performance hit that this has to your operations. When you select your Sort Keys, you need to understand that To fix this problem, you read here how to VACUUM your tables. Compound Sort Keys might work betterįinally, it is important to know that as you load more data on your sorted tables, performance deteriorates over time. Do you work with large tables (make sure to check table statistics)? If your queries include JOINS, GROUP BY, ORDER BY and window functions with PARTITION BY or ORDER BY. Do you have highly selective restrictive predicates in your queries? Then use Compound Keys and make sure that dominant column is first in the column list.Ģ. Then again you should consider Interleaved Sort Keys. Is there a dominant column appearing in your queries? Then Interleaved Sort Keys might work better. Do you plan to use more than one columns as Sort Keys?. To summarize all the above information, when you choose Sort Keys for your tables, keep the following in mind: Again, the nature of the data is important here, so the knowledge of a data analyst might help to figure out if it makes sense to use one or the other Sort Key Type. In this situation, it might give better query performance if the column values have a long common prefix. What you are looking for, is tables with a high number of 1MB blocks per slice and distributed over all slices if possible.Īnother example where you might want to consider an Interleaved Sort Key is when you plan to sort over only one column. To find out if a table is a good candidate for using them, you can query the STV_BLOCKLIST system table. Interleaved Sort Keys are more efficient with large tables. SELECT email from users WHEREname = ‘John’ 
Notably, in the case where a query uses restrictive predicates on secondary sort columns, Interleaved Sort Keys might significantly improve query performance.Īs a case of a restrictive predicate, consider a WHERE clause you filter your data using an equality operator. If there’s no dominant column in your queries, then you might get improved query performance by creating an Interleaved Sort Key. Thus, they can improve the performance of queries with the following operators.Ĭontrary to Compound Sort Keys,Interleaved Sort Keys put an equal weight to each of the included columns in the sort key. When you define a Compound Sort Key, make sure to put as first in the list, the most frequently used column in your queries.Ĭompound Sort Keys work best in situations where the query’s filter applies conditions, which use a prefix of the sort keys.
The order is important, as the performance decreases when queries depend on the secondary sort columns. Compound Sort KeysĬompound Sort Keys, are made up of all the columns that are listed in the sort key definition during the creation of the table, in the order that they are listed. Selecting the right kind requires knowledge of the queries that you plan to execute.
Its tuning depends heavily on the queries we plan to execute and thus to go through the analysis to be performed by the analyst is important in finding the most efficient Sort Keys.Īmazon Redshift supports two different types of Sort Keys, Compound Sort Keys, and Interleaved Sort Keys. These metadata pass to the query planner which in turn exploits this information to generate execution plans that are more performant.īased on the above it becomes obvious that Sort Keys is an important performance tuning parameter of our tables that, the min and max values of each block are stored and can be accessed directly without iterating the data every time a query executes. During this process some metadata is also generated, e.g. These columns are being used as data is loaded into the table to sort it accordingly. When you create a table, you can optionally define one or more columns as sort keys.
0 kommentar(er)
