The short answer
In Amazon Redshift, data on disk is stored in one-megabyte immutable blocks. For every block, Redshift automatically records metadata called zone maps in memory. Zone maps hold the minimum and maximum values for every column inside that block.
When a query contains a WHERE clause, the query engine checks the zone maps before reading the disk. If your filter is created_date = '2025-01-15' and a block has a min date of 2025-02-01 and a max date of 2025-02-28, Redshift skips that block entirely.
Block 1: min=2025-01-01, max=2025-01-10 -> Scanned
Block 2: min=2025-01-11, max=2025-01-20 -> Scanned (target inside)
Block 3: min=2025-01-21, max=2025-01-31 -> Skipped
Block 4: min=2025-02-01, max=2025-02-10 -> SkippedCompound sort keys
A compound sort key sorts data hierarchically using the columns in the order you define them. For example, if you declare COMPOUND SORTKEY(date, country, store_id), data is sorted first by date, then by country within each date, then by store within each country.
Compound sort keys provide the highest query performance when queries filter on the leading columns. If a query filters only on the second or third column without filtering on the leading column, Redshift cannot skip blocks effectively.
Interleaved sort keys
An interleaved sort key gives equal weight to every column in the sort key list. This helps when queries filter on different combinations of columns, such as filtering only by country or only by store identifier.
However, interleaved sort keys require long VACUUM times to maintain, and writing new rows is substantially slower. In production, most data engineering teams prefer compound sort keys with the most common date filter placed as the leading column.