Also the when it’ll use what materialization is also internal to RedShift. But unfortunately, it’ll work for all the queries. Partially NO, Redshift support both early and late materialization. This is cool right? But is it not possible in RedShift? # Then even if you compress the sort key column, it won’t affect much. Then instead of scanning all the blocks it’ll read the exact block where the actual row_id lives. But in late materialization the filtering will happen before fetching the data, so only necessary data and it row_id is fetched. In this method first it’ll fetch all the data and do the filter. Instead of reading that single value and its ROW_ID why redshift is reading the whole block where ID=3 located?Īctually this is an OLAP database concept. In sort key block, ID=3 is a single value. In relation transaction debases, we have index and that will help us to read the necessary value. But why RedShift read full block on sort key column? # This is the reason we should not compress the sort key column or the first column of the sort key. Now its clear that we are scanning only 20 rows. So it consumed 3 blocks.īlokc 4 - First ROW_ID = 21 -> not needed Now the comments column data is pretty large and even after applying the compression the size is 2.8MB.So all the 30 rows will fit it single block. After compression lets say the size is 800KB. Then the final result will be provided to the client.The same process will continue to fetch all the values from Block3.So it’ll fetch all the values in that block. For FirstName column there is only block and the row ID 1,2 will fit in that block.Then it’ll look into the zone map where ROW_ID 1 and 2 will fit.Now it’ll take the ROW_ID (1, 2) that is fetched from the ID column’s block and go to Firstname column’s Zone MAP. Then it has to check the **firstname** columns block.Now it’ll go to block 1 and scan all the values + row_id in that block.RedShift will go to its zone map’s min and max values for the ID column, and it will find the block1 is the exact block where ID =1 lives.Similarly other blocks also will have the same structure. We know Zone maps will contains the min and max values. If you take a look at the Block1 structure with zone map it’ll be looks like below. This tables will be stored in Redshift blocks along with its own row number or row_id. Lets consider the you have table with 3 columns. The sort key columns contains the row number of the other column as well for that particular row. This is not the actual RedShift block structure, but they have the similar structure. To explain this in simple, we have illustrated with some images. For late materialization its ok to compress the sort key column, but RedShift will not use Late materialization for all the queries. But in late materialization, the row numbers will be filtered instead of the data. In early materialization the data will be filtered at the first step before a join or a filter. RedShift using both early materialization and late materialization. If you have multiple column in your sort key then don’t compress the first column of the sort key. But you should not compress the sort key columns. Generally its best practice to compress all the columns in RedShift to get the better performance. RedShift Why You Should Not Compress RedShift Sort Key Column
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |