ablog

不器用で落着きのない技術者のメモ

Amazon Redshift のスライス毎の行の偏りを確認する

  • svv_table_info.skew_rows (スライス単位での最大行数/最小行数)の値が大きいと偏っている。
=# select * from svv_table_info where "table" = 'lineitem_dk_shipdate1';
-[ RECORD 1 ]----------+---------------------------------
database               | tpch_3tb_skew_distkey
schema                 | public
table_id               | 111347
table                  | lineitem_dk_shipdate1
encoded                | Y, AUTO(ENCODE)
diststyle              | KEY(l_shipdate)
sortkey1               | l_shipdate
max_varchar            | 44
sortkey1_enc           | none                            
sortkey_num            | 2
size                   | 953627
pct_used               | 0.1862
empty                  | 0
unsorted               | 0.44
stats_off              | 0.00
tbl_rows               | 18080048306
skew_sortkey1          | 3.35
skew_rows              | 1.29 ★
estimated_visible_rows | 18080047104
risk_event             | 
vacuum_sort_benefit    | 0.00
  • stv_tbl_perm.rows でスライス毎の行数を確認できる。
=# select *  from stv_tbl_perm where name = 'lineitem_dk_shipdate1' order by slice;
 slice |   id   |                                                               name                                                               |    rows★    | sorted_rows | temp | db_id  | insert_pristine | delete_pristine | backup | dist_style | block_count 
-------+--------+----------------------------------------------------------------------------------------------------------------------------------+------------+-------------+------+--------+-----------------+-----------------+--------+------------+-------------
     0 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1105831627 |  1105831627 |    0 | 110787 |               0 |               1 |      1 |          1 |       58332
     1 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1205274176 |  1205274176 |    0 | 110787 |               0 |               1 |      1 |          1 |       63588
     2 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1122205096 |  1122205096 |    0 | 110787 |               0 |               1 |      1 |          1 |       59151
     3 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1318458166 |  1318458166 |    0 | 110787 |               0 |               1 |      1 |          1 |       69562
     4 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1191046470 |  1111046470 |    0 | 110787 |               0 |               1 |      1 |          1 |       62940
     5 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1107365471 |  1107365471 |    0 | 110787 |               0 |               1 |      1 |          1 |       58409
     6 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1145335102 |  1145335102 |    0 | 110787 |               0 |               1 |      1 |          1 |       60410
     7 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1023442529 |  1023442529 |    0 | 110787 |               0 |               1 |      1 |          1 |       53997
     8 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1090088206 |  1090088206 |    0 | 110787 |               0 |               1 |      1 |          1 |       57473
     9 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1140357965 |  1140357965 |    0 | 110787 |               0 |               1 |      1 |          1 |       60123
    10 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1128260563 |  1128260563 |    0 | 110787 |               0 |               1 |      1 |          1 |       59512
    11 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1044099754 |  1044099754 |    0 | 110787 |               0 |               1 |      1 |          1 |       55074
    12 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1071680574 |  1071680574 |    0 | 110787 |               0 |               1 |      1 |          1 |       56517
    13 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1014295796 |  1014295796 |    0 | 110787 |               0 |               1 |      1 |          1 |       53462
    14 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1183561831 |  1183561831 |    0 | 110787 |               0 |               1 |      1 |          1 |       62405
    15 | 111347 | lineitem_dk_shipdate1                                                                                                            | 1188744980 |  1188744980 |    0 | 110787 |               0 |               1 |      1 |          1 |       62672
 12814 | 111347 | lineitem_dk_shipdate1                                                                                                            |          0 |           0 |    0 | 110787 |               3 |               1 |      1 |          1 |           0