Redshift Observatory

System Table Tracker

System view pg_catalog.svv_table_info version 1.0.41881 / 2022-09-30

schema name column data type
pg_catalog svv_table_info database text
pg_catalog svv_table_info diststyle text
pg_catalog svv_table_info empty int8
pg_catalog svv_table_info encoded text
pg_catalog svv_table_info estimated_visible_rows numeric(38,0)
pg_catalog svv_table_info max_varchar int4
pg_catalog svv_table_info pct_used numeric(10,4)
pg_catalog svv_table_info risk_event text
pg_catalog svv_table_info schema text
pg_catalog svv_table_info size int8
pg_catalog svv_table_info skew_rows numeric(19,2)
pg_catalog svv_table_info skew_sortkey1 numeric(19,2)
pg_catalog svv_table_info sortkey1 text
pg_catalog svv_table_info sortkey1_enc char(32)
pg_catalog svv_table_info sortkey_num int4
pg_catalog svv_table_info stats_off numeric(5,2)
pg_catalog svv_table_info table text
pg_catalog svv_table_info table_id oid
pg_catalog svv_table_info tbl_rows numeric(38,0)
pg_catalog svv_table_info unsorted numeric(5,2)
pg_catalog svv_table_info vacuum_sort_benefit numeric(12,2)

View Text

SELECT tbl_info_base.database,
       tbl_info_base.schema,
       tbl_info_base.table_id,
       tbl_info_base."table",
       tbl_info_base.encoded,
       tbl_info_base.diststyle,
       tbl_info_base.sortkey1,
       tbl_info_base.max_varchar,
       tbl_info_base.sortkey1_enc,
       tbl_info_base.sortkey_num,
       tbl_info_base.size,
       tbl_info_base.pct_used,
       tbl_info_base.empty,
       tbl_info_base.unsorted,
       CASE
         WHEN psi.stairows = CAST(0 AS double precision)
          AND (psi.staidels > CAST(0 AS double precision)
            OR psi.staiins > CAST(0 AS double precision))
           THEN CAST(CAST(100 AS numeric) AS numeric(5, 2))
         WHEN psi.stairows = CAST(0 AS double precision)
          AND psi.staidels = CAST(0 AS double precision)
          AND psi.staiins = CAST(0 AS double precision)
           THEN CAST(NULL AS numeric)
         WHEN psi.stairows IS NULL
           THEN CAST(NULL AS numeric)
         WHEN tbl_info_base.tbl_rows = CAST(0 AS numeric)
           THEN CAST(NULL AS numeric)
         WHEN psi.stairows > CAST(0 AS double precision)
           THEN CAST(LEAST(((psi.staidels + psi.staiins) * CAST(100 AS double precision)) / psi.stairows,
                           CAST(100 AS double precision)) AS numeric(5, 2))
         ELSE CAST(NULL AS numeric)
       END AS stats_off,
       tbl_info_base.tbl_rows,
       tbl_info_base.skew_sortkey1,
       tbl_info_base.skew_rows,
       CAST(psi.stairows AS numeric(38, 0)) AS estimated_visible_rows,
       tbl_info_base.risk_event,
       tbl_info_base.vacuum_sort_benefit
FROM (SELECT row_d.db_n AS database,
             stg_d.schema_n AS schema,
             stg_d.tbl_id AS table_id,
             stg_d.tbl_n AS "table",
             CASE
               WHEN strct_d.max_enc = 0
                AND strct_d.is_encode_auto = 1
                 THEN CAST('N, AUTO(ENCODE)' AS text)
               WHEN strct_d.max_enc <> 0
                AND strct_d.is_encode_auto = 1
                 THEN CAST('Y, AUTO(ENCODE)' AS text)
               ELSE CASE
                      WHEN strct_d.max_enc = 0
                        THEN CAST('N' AS text)
                      ELSE CAST('Y' AS text)
                    END
             END AS encoded,
             btrim(CASE
                     WHEN stg_d.diststyle = 0
                       THEN CAST('EVEN' AS text)
                     WHEN stg_d.diststyle = 1
                       THEN (CAST('KEY(' AS text) || strct_d.distkey) || CAST(')' AS text)
                     WHEN stg_d.diststyle = 8
                       THEN CAST('ALL' AS text)
                     WHEN stg_d.diststyle = 10
                       THEN CAST('AUTO(ALL)' AS text)
                     WHEN stg_d.diststyle = 11
                       THEN CAST('AUTO(EVEN)' AS text)
                     WHEN stg_d.diststyle = 12
                       THEN (CAST('AUTO(KEY(' AS text) || strct_d.distkey) || CAST('))' AS text)
                     ELSE CAST(NULL AS text)
                   END) AS diststyle,
             CASE
               WHEN strct_d.is_sortkey_auto < 1
                 THEN btrim(strct_d.headsort)
               WHEN strct_d.is_sortkey_auto = 1
                AND btrim(strct_d.headsort) IS NOT NULL
                 THEN (CAST('AUTO(SORTKEY(' AS text) || btrim(strct_d.headsort)) || CAST('))' AS text)
               WHEN strct_d.is_sortkey_auto = 1
                AND btrim(strct_d.headsort) IS NULL
                 THEN CAST('AUTO(SORTKEY)' AS text)
               ELSE CAST(NULL AS text)
             END AS sortkey1,
             strct_d.max_vc_size AS max_varchar,
             format_encoding(CAST(strct_d.headsort_enc AS integer)) AS sortkey1_enc,
             strct_d.n_sortkeys AS sortkey_num,
             sum(stg_d.col_mb) AS size,
             CASE
               WHEN cap_d.total_mb = 0
                 THEN CAST(NULL AS numeric)
               ELSE CAST((CAST(CAST(sum(stg_d.col_mb) AS numeric) AS numeric(19, 3)) / CAST(CAST(cap_d.total_mb AS numeric) AS numeric(19, 3))) * CAST(100 AS numeric) AS numeric(10, 4))
             END AS pct_used,
             sum(stg_d.empty_col_mb) AS empty,
             CASE
               WHEN row_d.tbl_rows = 0
                 THEN CAST(NULL AS numeric)
               WHEN strct_d.n_sortkeys = 0
                 THEN CAST(NULL AS numeric)
               ELSE CAST((CAST(CAST(row_d.unsorted_tbl_rows AS numeric) AS numeric(19, 3)) / CAST(CAST(row_d.tbl_rows AS numeric) AS numeric(19, 3))) * CAST(100 AS numeric) AS numeric(5, 2))
             END AS unsorted,
             CAST(CASE
                    WHEN row_d.tbl_rows = 0
                      THEN CAST(NULL AS numeric)
                    WHEN strct_d.n_sortkeys = 0
                      THEN CAST(NULL AS numeric)
                    WHEN max(tbl_avg_qpd.avg_qpd) IS NULL
                      THEN CAST(0 AS numeric)
                    ELSE max(tbl_avg_qpd.avg_qpd)
                  END AS numeric(12, 2)) AS vacuum_sort_benefit,
             CASE
               WHEN (stg_d.diststyle = 8
                  OR stg_d.diststyle = 10)
                AND max(row_d.node_num) > 0
                 THEN CAST(CAST(row_d.tbl_rows / max(row_d.node_num) AS numeric) AS numeric(38, 0))
               ELSE CAST(row_d.tbl_rows AS numeric)
             END AS tbl_rows,
             CASE
               WHEN max(stg_d.sk_mb) = 0
                 THEN CAST(NULL AS numeric)
               WHEN btrim(strct_d.headsort) = CAST('INTERLEAVED' AS text)
                 THEN CAST(NULL AS numeric)
               WHEN strct_d.n_sortkeys = 0
                 THEN CAST(NULL AS numeric)
               ELSE CAST(CAST(CAST(max(stg_d.col_mb) AS numeric) AS numeric(19, 3)) / CAST(CAST(max(stg_d.sk_mb) AS numeric) AS numeric(19, 3)) AS numeric(19, 2))
             END AS skew_sortkey1,
             CASE
               WHEN stg_d.diststyle = 0
                 OR stg_d.diststyle = 8
                 OR stg_d.diststyle = 10
                 OR stg_d.diststyle = 11
                 THEN CAST(NULL AS numeric)
               WHEN row_d.min_slice_rows = 0
                AND row_d.max_slice_rows = 0
                 THEN CAST(0 AS numeric)
               WHEN row_d.min_slice_rows = 0
                AND row_d.max_slice_rows > 0
                 THEN CAST(100 AS numeric)
               ELSE CAST(CAST(CAST(row_d.max_slice_rows AS numeric) AS numeric(19, 3)) / CAST(CAST(row_d.min_slice_rows AS numeric) AS numeric(19, 3)) AS numeric(19, 2))
             END AS skew_rows,
             risk_d.value AS risk_event
      FROM (SELECT btrim(CAST(pn.nspname AS text)) AS schema_n,
                   btrim(CAST(pc.relname AS text)) AS tbl_n,
                   pc.oid AS tbl_id,
                   sb.col,
                   CASE
                     WHEN pc.reldiststyle <= 8
                       THEN CAST(pc.reldiststyle AS integer)
                     ELSE CAST(pce.value AS integer)
                   END AS diststyle,
                   pc.reltuples AS stats_rows,
                   count(sb.blocknum) AS col_mb,
                   sum(CASE
                         WHEN pa.attnum IS NOT NULL
                          AND pa.attnum = (sb.col + 1)
                           THEN 1
                         ELSE 0
                       END) AS sk_mb,
                   sum(CASE
                         WHEN sb.tombstone > 0
                           THEN 1
                         ELSE 0
                       END) AS empty_col_mb
            FROM stv_blocklist AS sb
                 INNER JOIN pg_class AS pc ON CAST(sb.tbl AS oid) = pc.oid
                 INNER JOIN pg_namespace AS pn ON pc.relnamespace = pn.oid
                 LEFT JOIN pg_class_extended AS pce ON pce.reloid = pc.oid
                                                   AND pce.colnum = 0
                 LEFT JOIN pg_attribute AS pa ON CAST(sb.tbl AS oid) = pa.attrelid
                                             AND pa.attsortkeyord = 1
                                             AND pa.attisdropped = FALSE
            GROUP BY btrim(CAST(pn.nspname AS text)),
                     btrim(CAST(pc.relname AS text)),
                     pc.oid,
                     sb.col,
                     pce.value,
                     pc.reldiststyle,
                     pc.reltuples) AS stg_d
           INNER JOIN (SELECT sum(sp.capacity) AS total_mb
                       FROM stv_node_storage_capacity AS sp) AS cap_d ON 1 = 1
           INNER JOIN (SELECT inner_q.db_n,
                              inner_q.tbl_id,
                              count(DISTINCT inner_q.node_num) AS node_num,
                              sum(inner_q.slice_rows) AS tbl_rows,
                              sum(inner_q.unsorted_slice_rows) AS unsorted_tbl_rows,
                              min(inner_q.slice_rows) AS min_slice_rows,
                              max(inner_q.slice_rows) AS max_slice_rows
                       FROM (SELECT btrim(CAST(pd.datname AS text)) AS db_n,
                                    stp.id AS tbl_id,
                                    stp.slice,
                                    ss.node AS node_num,
                                    sum(stp.rows) AS slice_rows,
                                    sum(stp.rows - stp.sorted_rows) AS unsorted_slice_rows
                             FROM stv_tbl_perm AS stp, pg_database AS pd, stv_slices AS ss
                             WHERE CAST(stp.db_id AS oid) = pd.oid
                               AND stp.slice = ss.slice
                             GROUP BY btrim(CAST(pd.datname AS text)),
                                      stp.id,
                                      ss.node,
                                      stp.slice) AS inner_q
                       GROUP BY inner_q.db_n, inner_q.tbl_id) AS row_d ON CAST(row_d.tbl_id AS oid) = stg_d.tbl_id
           INNER JOIN (SELECT pa.attrelid AS tbl_id,
                              CASE
                                WHEN pce.value IS NULL
                                  THEN -1
                                ELSE CAST(pce.value AS integer)
                              END AS is_sortkey_auto,
                              CASE
                                WHEN pce_enc.value IS NULL
                                  THEN -1
                                ELSE CAST(pce_enc.value AS integer)
                              END AS is_encode_auto,
                              min(CAST(CASE
                                         WHEN pa.attisdistkey = TRUE
                                          AND NOT pa.attisdropped
                                           THEN pa.attname
                                         ELSE CAST(NULL AS name)
                                       END AS text)) AS distkey,
                              min(CAST(CASE
                                         WHEN pa.attsortkeyord = 1
                                          AND NOT pa.attisdropped
                                           THEN pa.attname
                                         WHEN pa.attsortkeyord = -1
                                          AND NOT pa.attisdropped
                                           THEN CAST('INTERLEAVED' AS name)
                                         ELSE CAST(NULL AS name)
                                       END AS text)) AS headsort,
                              min(CASE
                                    WHEN abs(pa.attsortkeyord) = 1
                                     AND NOT pa.attisdropped
                                      THEN pa.attnum
                                    ELSE CAST(NULL AS smallint)
                                  END) AS headsort_col,
                              min(CASE
                                    WHEN abs(pa.attsortkeyord) = 1
                                     AND NOT pa.attisdropped
                                      THEN pa.attencodingtype
                                    ELSE CAST(NULL AS smallint)
                                  END) AS headsort_enc,
                              max(abs(pa.attsortkeyord)) AS n_sortkeys,
                              max(COALESCE(CAST(pa.attencodingtype AS integer),
                                           0)) AS max_enc,
                              max(CASE
                                    WHEN pt.typname = CAST('varchar' AS name)
                                      THEN pa.atttypmod - 4
                                    ELSE 0
                                  END) AS max_vc_size
                       FROM pg_attribute AS pa
                            LEFT JOIN pg_class_extended AS pce ON pa.attrelid = pce.reloid
                                                              AND pce.colnum = 12
                            LEFT JOIN pg_class_extended AS pce_enc ON pa.attrelid = pce_enc.reloid
                                                                  AND pce_enc.colnum = 14
                            INNER JOIN pg_type AS pt ON pa.atttypid = pt.oid
                                                    AND pa.attnum > 0
                       GROUP BY pa.attrelid, pce.value, pce_enc.value) AS strct_d ON strct_d.tbl_id = stg_d.tbl_id
           LEFT JOIN (SELECT pce.reloid AS tbl_id,
                             CASE
                               WHEN pce.value = CAST('' AS text)
                                 THEN CAST('NULL' AS text)
                               ELSE replace(pce.value,
                                            split_part(pce.value,
                                                       CAST('|' AS text),
                                                       3),
                                            CAST(date_add(CAST('microseconds' AS text),
                                                          CAST(split_part(pce.value,
                                                                          CAST('|' AS text),
                                                                          3) AS bigint),
                                                          CAST('2000-01-01 00:00:00' AS timestamp)) AS text))
                             END AS value
                      FROM pg_class_extended AS pce
                      WHERE pce.colnum = 5) AS risk_d ON risk_d.tbl_id = stg_d.tbl_id
           LEFT JOIN (SELECT stv_table_partitions.id,
                             CAST(CASE
                                    WHEN sum(stv_table_partitions.qpd) = 0
                                      THEN CAST(NULL AS numeric)
                                    ELSE CAST(CAST(avg(stv_table_partitions.qpd) AS numeric) AS numeric(12, 2))
                                  END AS numeric(12, 2)) AS avg_qpd
                      FROM stv_table_partitions
                      WHERE stv_table_partitions.is_sorted = CAST('f' AS bpchar)
                        AND stv_table_partitions.qpd > 0
                      GROUP BY stv_table_partitions.id) AS tbl_avg_qpd ON stg_d.tbl_id = CAST(tbl_avg_qpd.id AS oid)
      GROUP BY row_d.db_n,
               stg_d.schema_n,
               stg_d.tbl_n,
               stg_d.tbl_id,
               CASE
                 WHEN strct_d.max_enc = 0
                  AND strct_d.is_encode_auto = 1
                   THEN CAST('N, AUTO(ENCODE)' AS text)
                 WHEN strct_d.max_enc <> 0
                  AND strct_d.is_encode_auto = 1
                   THEN CAST('Y, AUTO(ENCODE)' AS text)
                 ELSE CASE
                        WHEN strct_d.max_enc = 0
                          THEN CAST('N' AS text)
                        ELSE CAST('Y' AS text)
                      END
               END,
               btrim(CASE
                       WHEN stg_d.diststyle = 0
                         THEN CAST('EVEN' AS text)
                       WHEN stg_d.diststyle = 1
                         THEN (CAST('KEY(' AS text) || strct_d.distkey) || CAST(')' AS text)
                       WHEN stg_d.diststyle = 8
                         THEN CAST('ALL' AS text)
                       WHEN stg_d.diststyle = 10
                         THEN CAST('AUTO(ALL)' AS text)
                       WHEN stg_d.diststyle = 11
                         THEN CAST('AUTO(EVEN)' AS text)
                       WHEN stg_d.diststyle = 12
                         THEN (CAST('AUTO(KEY(' AS text) || strct_d.distkey) || CAST('))' AS text)
                       ELSE CAST(NULL AS text)
                     END),
               stg_d.diststyle,
               btrim(strct_d.headsort),
               strct_d.max_vc_size,
               format_encoding(CAST(strct_d.headsort_enc AS integer)),
               strct_d.n_sortkeys,
               strct_d.is_sortkey_auto,
               strct_d.is_encode_auto,
               strct_d.max_enc,
               cap_d.total_mb,
               row_d.tbl_rows,
               row_d.unsorted_tbl_rows,
               row_d.min_slice_rows,
               row_d.max_slice_rows,
               risk_d.value) AS tbl_info_base
     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.staidels) AS staidels
                FROM pg_statistic_indicator
                GROUP BY pg_statistic_indicator.stairelid
                HAVING count(pg_statistic_indicator.stairelid) = 1) AS psi ON tbl_info_base.table_id = psi.stairelid


Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers