Redshift Auto Sortkeys in ATO

0

Does auto sortkeys option really help with good performance if i have unpredictable order by and join condition on table also if it helps does it incure more resources to shuffle the table every time with new sort keys addition or deletion. Please specify how it will work with auto sort keys my tables data will be sort only once or based on query changes over period it will again resort the whole table data and changes the keys.

질문됨 2달 전165회 조회
1개 답변
0

Hello Nilesh,

Automatic table optimization is a self-tuning capability that automatically optimizes the design of tables by applying sort and distribution keys without the need for administrator intervention.

Sorting enables efficient handling of range-restricted predicates by reducing the no. of blocks scanned hence improving the performance. You would typically use most commonly used columns in filters ( WHERE clause ) as sort keys so that it will scan only the data related to the column values and skip scanning rest of the data blocks.

If you don't explicitly define sort key in your table, Amazon Redshift uses automatic table optimization to choose the sort key as it continuously observes how queries interact with tables. If the workload pattern changes and it needs to alter the sort key column, it will do so in the background with minimum impact to the user workload.

Amazon Redshift automatically sorts data (AUTO VACCUM ) in the background to maintain table data in the order of its sort key, so if there are changes to the table and if the process finds the table will benefit from sorting ( vacuum_sort_benefit ) it will do so when the cluster is not heavily utilized.

Please refer to the below docs and let me know if you have any other questions.

https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html https://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html

AWS
전문가
답변함 2달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠