Imagine if you have information stored in a bunch of different file cabinets. This can be useful when joining datasets together because it lets Redshift know where to easily locate the queried data. Distkeys What is a Distkey?Ī distkey is a way for Redshift to decide which row goes to which node of a cluster. You can execute a VACUUM in a scheduled SQL Script. Remember that vacuuming is resource-intensive so you’ll want to run the command when you expect there to be minimal activity on the cluster. To keep your table fully sorted you’ll occasionally need to run a VACUUM command on it. When you add data to a table, it’s sorted but also temporarily stored in a separate region on disk. What About Tables That Add/Remove Data Often? Because the data is already sorted on the join key, the query optimizer can bypass the sort phase of the sort merge join. This enables the query optimizer to choose a sort merge join instead of a slower hash join.If you frequently join a table, specify the join column as both the sort key and the distribution key.Redshift can skip reading entire blocks of data for that column because it keeps track of the minimum and maximum column values stored on each block and can skip blocks that don't apply to the predicate range.If you do frequent range filtering or equality filtering on one column, specify that column as the sort key.Queries will be more efficient because they can skip entire blocks that fall outside the time range.If recent data is queried most frequently, specify the timestamp column as the leading column for the sort key.When it comes to choosing the best sortkey, Amazon has this to say: If the data is not sorted, more of the disk blocks (possibly all of them) have to be scanned.” “For example, if a table stores five years of data sorted by date and a query specifies a date range of one month, up to 98% of the disk blocks can be eliminated from the scan. Here’s an example from Amazon’s documentation:

A prominent example would be a date column. Which Columns Should be Sortkeys?Ī good rule of thumb is to designate any column that is commonly used in JOINs or in the “WHERE” clause of a SQL query as a sortkey. Civis's import feature currently supports a maximum of two sortkeys per table.

Redshift allows for up to 400 columns in a table to be designated as sortkeys. Assigning a sortkey can help Redshift quickly find data when executing a query. Sortkeys What is a Sortkey?Ī sortkey is a designation given to a column that allows Redshift to optimize its query planning process. Distkeys and Sortkeys are Redshift-only column designations that can help speed up query performance.

Sort key in redshift