Why does a table in Amazon Redshift show a high unsorted percentage when the vacuum sort benefit is 0?

Short description

Amazon Redshift calculates the unsorted column in SVV_TABLE_INFO to show the percentage of table data that doesn't match the sort key order. If the unsorted percentage is high, then a VACUUM sort operation might improve query performance.

The vacuum_sort_benefit value determines whether a sort operation can improve performance. When the value is 0, Amazon Redshift can't benefit from a VACUUM operation because sorting doesn't optimize the queries.

A high unsorted percentage with a vacuum_sort_benefit value of 0 can occur for the following reasons:

• Amazon Redshift determines that sorting offers minimal benefit based on recent query plans. Queries access only a small part of the table, or few queries access the table.
• Queries don't filter or order by the sort key columns. Sorting provides minimal benefit, even when the table appears unsorted.
• No queries run on the table, so Amazon Redshift doesn't need to optimize the sort order.

For more information, see Vacuuming tables.

Resolution

To determine whether sorting improves performance, review both the vacuum_sort_benefit and unsorted values.

If your vacuum_sort_benefit value equals 0 and the unsorted value is high, then your query patterns don't benefit from sorting the data. The vacuum_sort_benefit metric calculates potential query performance improvement from sorting based on your workload patterns.

Run the following query to get the values:

SELECT "schema", "table", sortkey1, sortkey_num, unsorted, vacuum_sort_benefit 
 FROM SVV_TABLE_INFO 
WHERE "table" = 'table_name';

Note: Replace table_name with the name of the table.

Make sure that the sort key aligns with the frequently queried filter columns. If the sort key isn't relevant to the query patterns, then a high unsorted percentage doesn't significantly affect performance.

If you determine that sorting improves performance, then run the following VACUUM sort command to manually vacuum the table:

VACUUM SORT ONLY table_name;

Note: Replace table_name with the name of the table.

Related information

Automatic table sort