Understand accuracy ofvacuum_sort_benefit

Question

Hi All,
Greetings for the day.

I am curious to undersand the significance and accuracy of the field "vacuum_sort_benefit" in system view "svv_table_info".

In the current cluster that I am working, I see tables where column "unsorted" is 100 but the vacuum_sort_benefit is 0.0 which is making me to think if its really worth to run a vacuum and analyze on these tables.

Any inputs in this regard is greatly appreciated.

Accepted Answer

It might be due to full refresh, like it depends that are you truncating table or deleting rows or dropping table. And as documented, if Redshift does not see any query improvement from sorting of the data it will not show benefit. I request you to raise support case with details. So, we can check and help you better. Also, you can use best practice to avoid vacuum. Because, you are doing full refresh where this will be more helpful. Please check : https://docs.aws.amazon.com/redshift/latest/dg/c_loading-data-best-practices.html & https://docs.aws.amazon.com/redshift/latest/dg/vacuum-load-in-sort-key-order.html

```
Thanks for your response.

One of the tables (with 100% unsorted value and 0%- vacuum_sort_benefit ) is heavily used but we do a full refresh (delete n reload) on a daily basis.
Can you pls help me in understanding why the vacuum_sort_benefit is 0. Is it to do with the full refresh?
```

Answer

The unsorted column reflects the physical sort order of a table. The vacuum_sort_benefit column specifies the impact of sorting a table by manually running VACUUM SORT.

Now, if you find the unsorted is 100% and vacuum_sort_benefit is 0%, then this might be either because only a small portion of the table is accessed by queries, or very few queries accessed the table. So, it indicates that query performance does not have impact due to unsorted rows of that table even if it is 100% unsorted.

Please check detailed explanation with example here:  https://docs.aws.amazon.com/redshift/latest/dg/t_Reclaiming_storage_space202.html#automatic-table-sort . We have explained both column indications here.

I hope this helps you!

Understand accuracy ofvacuum_sort_benefit

Relevant content