Redshift の SVV_TABLE_INFO.skew_rows の意味

Redshift の SVV_TABLE_INFO.skew_rows の意味

• lineorder テーブルの skew_rows は 3.67

=# select * from svv_table_info where schema = 'ssbgz_before' and "table" = 'lineorder';

-[ RECORD 1 ]----------+---------------------------------
database               | dev
schema                 | ssbgz_before
table_id               | 621306
table                  | lineorder
encoded                | Y, AUTO(ENCODE)
diststyle              | KEY(lo_linenumber)
sortkey1               | lo_shipmode
max_varchar            | 15
sortkey1_enc           | none
sortkey_num            | 2
size                   | 25652
pct_used               | 0.1603
empty                  | 0
unsorted               | 100.00
stats_off              | 0.00
tbl_rows               | 600037902
skew_sortkey1          | 0.46
skew_rows              | 3.67
estimated_visible_rows | 600037888
risk_event             |
vacuum_sort_benefit    | 0.00
create_time            | 2023-03-16 09:14:35.856642

• lineorder テーブルの max(rows) / min(rows) は 3.666... （rows が 0 は除く）

=# select cast(max(a.rows)::decimal(20,3)/min(a.rows)::decimal(20,3) as decimal(20,3)),
  min(a.rows), 
  max(a.rows)
from stv_tbl_perm a, svv_table_info b
where b.schema = 'ssbgz_before'
and b."table" = 'lineorder'
and a.id = b.table_id
and a.rows > 0;

-[ RECORD 1 ]------
numeric | 3.666
min     | 64290276
max     | 235730597

参考

Column name	Data type	Description
skew_rows	numeric(19,2)	Ratio of the number of rows in the slice with the most rows to the number of rows in the slice with the fewest rows.

SVV_TABLE_INFO - Amazon Redshift

# show view svv_table_info;

    (
        round(
            CASE
                WHEN (
                    (
                        (
                            COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 1
                        )
                        OR (
                            COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 12
                        )
                    )
                    AND (stvp.min_rows > 0)
                ) THEN (
                    ((stvp.max_rows) :: numeric * 1.0) / (stvp.min_rows) :: numeric
                )
                WHEN (
                    (
                        (
                            (
                                COALESCE(pge.releffectiv ediststyle, pgc.reldiststyle) = 1
                            )
                            OR (
                                COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 12
                            )
                        )
                        AND (stvp.min_rows = 0)
                    )
                    AND (stvp.max_rows > 0)
                ) THEN (100) :: numeric
                ELSE NULL :: num eric
            END,
            2
        )
    ) :: numeric(19, 2) AS skew_rows,

Redshift のシステムビューの定義を確認する - ablog

yohei-a.hatenablog.jp
docs.aws.amazon.com