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

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

# show view svv_table_info;

↓整形済み
SELECT
    btrim(((stvp.datname) :: character varying) :: text) AS "database",
    btrim(
        (
            (nvl2(mvi.mv_id, mvi.mv_schema, pgn.nspname)) :: character varying
        ) :: text
    ) AS "schema",
    nvl2(mvi.mv_id, mvi.mv_id, pgc.oid) AS table_id,
    btrim(
        (
            (nvl2(mvi.mv_id, mvi.mv_name, pgc.relname)) :: character varying
        ) :: text
    ) AS "table",
    btrim(
        (
            CASE
                WHEN (
                    (pge.autoencode IS NOT NULL)
                    AND (stvc.encoded = 1)
                ) THEN 'Y, AUTO(ENCODE)' :: character varying
                WHEN (
                    (pge.autoencode IS NOT NULL)
                    AND (stvc.encoded = 0)
                ) THEN 'N, AUTO(ENCODE)' :: character varying
                WHEN (stvc.encoded = 1) THEN 'Y' :: character varying
                ELSE 'N' :: character varying
            END
        ) :: text
    ) AS encoded,
    btrim(
        (
            CASE
                WHEN (
                    COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 0
                ) THEN 'EVEN' :: character varying
                WHEN (
                    COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 1
                ) THEN (
                    (
                        (
                            ('KEY(' :: character varying) :: text || stvc."distkey"
                        ) || (')' :: character varying) :: text
                    )
                ) :: character varying
                WHEN (
                    COA LESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                ) THEN 'ALL' :: character varying
                WHEN (
                    COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 10
                ) THEN 'AUTO(ALL)' :: character vary ing
                WHEN (
                    COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 11
                ) THEN 'AUTO(EVEN)' :: character varying
                WHEN (
                    COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 12
                ) THEN (
                    (
                        (
                            ('A
UTO(KEY(' :: character varying) :: text || stvc."distkey"
                        ) || ('))' :: character varying) :: text
                    )
                ) :: character varying
                ELSE NULL :: character varying
            END
        ) :: text
    ) AS "diststyle",
    btrim(
        (
            (
                CASE
                    WHE N (
                        (pge.autosortkey = 1)
                        AND (stvc.headsort IS NOT NULL)
                    ) THEN (
                        (
                            ('AUTO(SORTKEY(' :: character varying) :: text || stvc.headsort
                        ) || ('))' :: character varying) :: text
                    )
                    WHEN (
                        (pge.autosortkey = 1)
                        AND (stvc.headsort IS NULL)
                    ) THEN ('AUTO(SORTKEY)' :: character varying) :: text
                    ELSE stvc.headsort
                END
            ) :: character varying
        ) :: text
    ) AS sortkey1,
    stvc.max_varchar,
    stvc.sortkey1_enc,
    stvc.sortkey_num,
    COALESCE(stvc.tblsize, (0) :: bigint) AS size,
    (
        (
            (
                (COALESCE(stvc.tblsize, (0) :: bigint)) :: numeric * 100.0
            ) / (
                (
                    SELECT
                        sum(sp.capacity) AS total_mb
                    FROM
                        stv_node_storage_ capacity sp
                )
            ) :: numeric
        )
    ) :: numeric(10, 4) AS pct_used,
    stvc.empty,
    (
        CASE
            WHEN (
                (
                    (
                        (
                            COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                        )
                        OR (
                            COALESCE(pge.releffectivediststyle, pgc.r eldiststyle) = 10
                        )
                    )
                    AND (stvp.max_rows > 0)
                )
                AND (stvc.headsort IS NOT NULL)
            ) THEN (
                (
                    ((stvp.max_rows - stvp.max_sorted_rows)) :: numeric * 100.0
                ) / (stvp.max_rows) :: numeric
            )
            WHEN (
                (stvp.sum_rows > 0)
                AND (stvc.headsort IS NOT NULL)
            ) THEN (
                (
                    ((stvp.sum_rows - stvp.sum_sorted_rows)) :: numeric * 100.0
                ) / (stvp.sum_rows) :: numeric
            )
            ELSE NULL :: numeric
        END
    ) :: numeric(5, 2) AS un sorted,
    CASE
        WHEN (
            (pgs.stairows = (0) :: double precision)
            AND (
                (pgs.staidels > (0) :: double precision)
                OR (pgs.staiins > (0) :: double precision)
            )
        ) THEN (100) :: numeric
        WHEN (
            (
                (pgs.stairow s = (0) :: double precision)
                AND (pgs.staidels = (0) :: double precision)
            )
            AND (pgs.staiins = (0) :: double precision)
        ) THEN NULL :: numeric
        WHEN (pgs.stairows IS NULL) THEN NULL :: numeric
        WHEN (
            CASE
                WHEN (
                    (
                        COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                    )
                    OR (
                        COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 10
                    )
                ) THEN stvp.max_rows
                ELSE stvp.sum_rows
            END = 0
        ) THEN NULL :: numeric
        WHEN (pgs.stairows > (0) :: double precision) THEN (
            LEAST(
                (
                    (
                        (pgs.staidels + pgs.staiins) * (100) :: double precision
                    ) / pgs.stairows
                ),
                (100) :: double precision
            )
        ) :: numeri c(5, 2)
        ELSE NULL :: numeric
    END AS stats_off,
    (
        (
            CASE
                WHEN (
                    (
                        COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                    )
                    OR (
                        COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 10
                    )
                ) TH EN stvp.max_rows
                ELSE stvp.sum_rows
            END
        ) :: numeric
    ) :: numeric(38, 0) AS tbl_rows,
    stvc.skew_sortkey1,
    (
        round(
            CASE
                WHEN (
                    (
                        (
                            COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 1
                        )
                        OR (
                            CO ALESCE(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,
    (pgs.stairows) :: numeric(38, 0) AS estimated_visible_rows,
    pge.risk_event,
    (
        (
            CASE
                WHEN (stvc.sortkey_num > 0) THEN COALESCE(stvt.avg_qpd, (0) :: bigint)
                ELSE NULL :: bigint
            END
        ) :: numeric
    ) :: numeric(12, 2) AS vacuum_sort_benefit,
    pge.relcreationtime AS create_time
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                pg_class pgc
                                JOIN pg_namespace pgn ON ((pgc.relnamespace = pgn.oid))
                            )
                            JOIN (
                                SELECT
                                    stvp.id,
                                    pgd.datname,
                                    sum(stvp."rows") AS sum_rows,
                                    sum(stvp.sorted_rows) AS sum_sorted_rows,
                                    "max"(stvp."rows") AS max_rows,
                                    "max"(stvp.sorted_rows) AS max_s orted_rows,
                                    min(stvp."rows") AS min_rows
                                FROM
                                    (
                                        (
                                            stv_tbl_perm stvp
                                            JOIN stv_slices stvs USING (slice)
                                        )
                                        JOIN pg_database pgd ON (((stvp.db_id) :: oid = pgd.oid))
                                    )
                                GROUP BY
                                    stvp.id,
                                    pgd.dat name
                            ) stvp ON ((pgc.oid = (stvp.id) :: oid))
                        )
                        JOIN (
                            SELECT
                                tblmeta.tbl,
                                "max"(tblmeta.headsort) AS headsort,
                                "max"((tblmeta.distkeycol) :: text) AS "distkey",
                                "max"(tblmeta.varcharlen) AS max_varchar,
                                sum(tblmeta.col_size) AS tblsize,
                                "max"(
                                    CASE
                                        WHEN (tblmeta.sortkey_num <> 1) THEN tblmeta.col_size
                                        ELSE NULL :: bigint
                                    END
                                ) AS max_col_size,
                                "max"(
                                    CASE
                                        WHEN (tblmeta.sortkey _num = 1) THEN tblmeta.col_size
                                        ELSE NULL :: bigint
                                    END
                                ) AS sortkey_col_size,
                                (
                                    round(
                                        (
                                            (
                                                (
                                                    "max"(
                                                        CASE
                                                            WHEN (tblmeta.sortkey_num <> 1) THEN tblmeta.col_size
                                                            ELSE NULL :: bigint
                                                        END
                                                    )
                                                ) :: numeric * 1.0
                                            ) / (
                                                CASE
                                                    WHEN (
                                                        "max"(
                                                            CASE
                                                                WHEN (tblmeta.sortkey_num = 1) THEN tblmeta.col_size
                                                                ELSE NULL :: bigint
                                                            END
                                                        ) > 0
                                                    ) THEN "max"(
                                                        CASE
                                                            WHEN (tblmeta.sortkey_num = 1) THEN tblmeta.col_size EL SE NULL :: bigint
                                                        END
                                                    )
                                                    ELSE NULL :: bigint
                                                END
                                            ) :: numeric
                                        ),
                                        2
                                    )
                                ) :: numeric(19, 2) AS skew_sortkey1,
                                sum(tblmeta.empty) AS empty,
                                "max"(abs(tblmeta.sortkey_num)) AS sortkey_num,
                                "max"(
                                    CASE
                                        WHEN (tblmeta.attencodingtype > 0) THEN 1
                                        ELSE 0
                                    END
                                ) AS encoded,
                                (
                                    btrim(
                                        "max"(
                                            (
                                                CASE
                                                    WHEN (abs(tblmeta.sortkey_num) = 1) THEN format_encoding((tblmeta.attencodingtype) :: integer)
                                                    ELSE NULL: :bpchar
                                                END
                                            ) :: text
                                        )
                                    )
                                ) :: character(32) AS sortkey1_enc
                            FROM
                                (
                                    SELECT
                                        stvts.tbl,
                                        stvts.colnum,
                                        btrim(
                                            (
                                                (
                                                    CASE
                                                        WHEN (
                                                            (pga.attsortkeyord = 1)
                                                            AND (NOT pga.attisdropped)
                                                        ) THEN pga.attname
                                                        WHEN (
                                                            (pga.attsortkeyord = -1)
                                                            AND (NOT pga.attisdropped)
                                                        ) THEN 'INTERLEAVED' :: name
                                                        ELSE NULL :: name
                                                    END
                                                ) :: character varying
                                            ) :: text
                                        ) AS headsort,
                                        (
                                            CASE
                                                WHEN (pga.attisdistkey IS TRUE) THEN p ga.attname
                                                ELSE NULL :: name
                                            END
                                        ) :: character varying AS distkeycol,
                                        CASE
                                            WHEN (pga.atttypid = (1043) :: oid) THEN (pga.atttypmod - 4)
                                            ELSE 0
                                        END AS varcharlen,
                                        pga.attencodingtype,
                                        pga.att sortkeyord AS sortkey_num,
                                        stvts.col_blkcnt AS col_size,
                                        0 AS empty
                                    FROM
                                        (
                                            (
                                                SELECT
                                                    stv_tbl_column_slice_state.table_id AS tbl,
                                                    ((stv_tbl_column_slice_state.col + 1)) :: smallint AS colnum,
                                                    sum(stv_tbl_column_slice_state.num_blocks) AS col_blkcnt
                                                FROM
                                                    stv_tbl_column_slice_state
                                                GROUP BY
                                                    stv_tbl_column_slice_state.table_id,
                                                    ((stv_tbl_column_slice_state.col + 1)) :: smallin t
                                                HAVING
                                                    (sum(stv_tbl_column_slice_state.num_blocks) > 0)
                                            ) stvts
                                            LEFT JOIN pg_attribute pga ON (
                                                (
                                                    ((stvts.tbl) :: oid = pga.attrelid)
                                                    AND (stvts.colnum = pga.attnum)
                                                )
                                            )
                                        )
                                    WHERE
                                        (stvts.tbl > 0)
                                    GROUP BY
                                        stvts.tbl,
                                        stvts.colnum,
                                        stvts.col_blkcnt,
                                        btrim(
                                            (
                                                (
                                                    CASE
                                                        WHEN (
                                                            (pga.attsortkeyord = 1)
                                                            AND (NOT pga.attisdropped)
                                                        ) THEN pga.attname
                                                        WHEN (
                                                            (pga.attsortkeyord = -1)
                                                            AND (NOT pga.attisdropped)
                                                        ) THEN 'INTERLEAVED' :: name
                                                        ELSE NULL :: name
                                                    END
                                                ) :: character varying
                                            ) :: text
                                        ),
                                        (
                                            CASE
                                                WHEN (pga.attisdistkey IS TRUE) THEN pga.attname
                                                ELSE NULL :: name
                                            END
                                        ) :: character vary ing,
                                        CASE
                                            WHEN (pga.atttypid = (1043) :: oid) THEN (pga.atttypmod - 4)
                                            ELSE 0
                                        END,
                                        pga.attencodingtype,
                                        pga.attsortkeyord
                                ) tblmeta
                            GROUP BY
                                tblmeta.tbl
                        ) stvc ON ((pgc.oid = (stvc.tbl) :: o id))
                    )
                    LEFT JOIN (
                        SELECT
                            pg_statistic_indicator.stairelid,
                            sum(pg_statistic_indicator.stairows) AS stairows,
                            sum(pg_statistic_indicator.staiins) AS staiins,
                            sum(pg_statistic_indicator.s taidels) AS staidels
                        FROM
                            pg_statistic_indicator
                        GROUP BY
                            pg_statistic_indicator.stairelid
                        HAVING
                            (count(pg_statistic_indicator.stairelid) = 1)
                    ) pgs ON ((pgc.oid = pgs.stairelid))
                ) LEF T
                JOIN (
                    SELECT
                        pg_class_extended.reloid,
                        btrim(
                            (
                                (
                                    "max"(
                                        CASE
                                            WHEN (
                                                (pg_class_extended.colnum = 5)
                                                AND (pg_class_extended.value <> '' :: text)
                                            ) THEN "replace"(
                                                pg_class_extended.value,
                                                spli t_part(pg_class_extended.value, '|' :: text, 3),
                                                (
                                                    date_add(
                                                        'us' :: text,
                                                        (
                                                            split_part(pg_class_extended.value, '|' :: text, 3)
                                                        ) :: bigint,
                                                        '2000-01-01 00:00:00' :: timestamp without time zone
                                                    )
                                                ) :: text
                                            )
                                            ELSE NULL :: text
                                        END
                                    )
                                ) :: character varying
                            ) :: text
                        ) AS risk_event,
                        "max"(
                            CASE
                                WHEN (
                                    (pg_class_extended.colnum = 12)
                                    AND (pg_class_extended.value = (1) :: text)
                                ) THEN (pg_class_extende d.value) :: smallint
                                ELSE NULL :: smallint
                            END
                        ) AS autosortkey,
                        "max"(
                            CASE
                                WHEN (
                                    (pg_class_extended.colnum = 14)
                                    AND (pg_class_extended.value = (1) :: text)
                                ) THEN pg_class_extended.value ELS E NULL :: text
                            END
                        ) AS autoencode,
                        (
                            "max"(
                                CASE
                                    WHEN (pg_class_extended.colnum = 0) THEN pg_class_extended.value
                                    ELSE NULL :: text
                                END
                            )
                        ) :: smallint AS releffectivediststyle,
                        "max"(
                            CASE
                                WHEN (pg_class_extended.colnum = 1) THEN date_add(
                                    ('microsecond' :: character varying) :: text,
                                    (pg_class_extended.value) :: bigint,
                                    '2000-01-01 00:00:00' :: timestamp without time zone
                                )
                                ELSE NULL: :timestamp without time zone
                            END
                        ) AS relcreationtime
                    FROM
                        pg_class_extended
                    GROUP BY
                        pg_class_extended.reloid
                ) pge ON ((pgc.oid = pge.reloid))
            )
            LEFT JOIN (
                SELECT
                    stv_table_partitions.i d,
                    CASE
                        WHEN (avg(stv_table_partitions.qpd) = 0) THEN NULL :: bigint
                        ELSE avg(stv_table_partitions.qpd)
                    END AS avg_qpd
                FROM
                    stv_table_partitions
                WHERE
                    (
                        (stv_table_partitions.is_sorted = 'f' :: bpchar)
                        AND (stv_table_partitions.qpd > 0)
                    )
                GROUP BY
                    stv_table_partitions.id
            ) stvt ON ((pgc.oid = (stvt.id) :: oid))
        )
        LEFT JOIN (
            SELECT
                pce.reloid AS mv_id,
                pgn.nspname AS mv_schema,
                pgc.relname AS mv_name,
                pgd.refobjid AS tbl_id
            FROM
                pg_rewrite pgr,
                pg_depend pgd,
                pg_class pgc,
                pg_class_extended pce,
                pg_namespace pgn
            WHERE
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                (pgr.rulename = '_RETURN' :: name)
                                                AND (pgr.oid = pgd.objid)
                                            )
                                            AND (pgd.refobjsubid = 1)
                                        )
                                        AND (pgd.deptype = 'n' :: "char")
                                    )
                                    AND (pgd.refclassid = ('pg_class' :: regclass) :: oid)
                                )
                                AND (pgr.ev_class = pce.reloid)
                            )
                            AND (pce.c olnum = 6)
                        )
                        AND (pgc.oid = pce.reloid)
                    )
                    AND (pgn.oid = pgc.relnamespace)
                )
        ) mvi ON ((mvi.tbl_id = pgc.oid))
    )
WHERE
    (pgn.nspname <> 'pg_automv' :: name);

参考

• ビューを表示する - Amazon Redshift
• SVV_TABLE_INFO - Amazon Redshift