Redshift Observatory

System Table Tracker

System view pg_catalog.svv_table_info version 1.0.70695 / 2024-07-11

schema name column data type
pg_catalog svv_table_info create_time timestamp
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 btrim(CAST(CAST(stvp.datname AS varchar) AS text)) AS database,
       btrim(CAST(CAST(nvl2(mvi.mv_id, mvi.mv_schema, pgn.nspname) AS varchar) AS text)) AS schema,
       nvl2(mvi.mv_id, mvi.mv_id, pgc.oid) AS table_id,
       btrim(CAST(CAST(nvl2(mvi.mv_id, mvi.mv_name, pgc.relname) AS varchar) AS text)) AS "table",
       btrim(CAST(CASE
                    WHEN pge.autoencode IS NOT NULL
                     AND stvc.encoded = 1
                      THEN CAST('Y, AUTO(ENCODE)' AS varchar)
                    WHEN pge.autoencode IS NOT NULL
                     AND stvc.encoded = 0
                      THEN CAST('N, AUTO(ENCODE)' AS varchar)
                    WHEN stvc.encoded = 1
                      THEN CAST('Y' AS varchar)
                    ELSE CAST('N' AS varchar)
                  END AS text)) AS encoded,
       btrim(CAST(CASE
                    WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 0
                      THEN CAST('EVEN' AS varchar)
                    WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 1
                      THEN CAST((CAST(CAST('KEY(' AS varchar) AS text) || stvc.distkey) || CAST(CAST(')' AS varchar) AS text) AS varchar)
                    WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                      THEN CAST('ALL' AS varchar)
                    WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 10
                      THEN CAST('AUTO(ALL)' AS varchar)
                    WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 11
                      THEN CAST('AUTO(EVEN)' AS varchar)
                    WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 12
                      THEN CAST((CAST(CAST('AUTO(KEY(' AS varchar) AS text) || stvc.distkey) || CAST(CAST('))' AS varchar) AS text) AS varchar)
                    ELSE CAST(NULL AS varchar)
                  END AS text)) AS diststyle,
       btrim(CAST(CAST(CASE
                         WHEN pge.autosortkey = 1
                          AND stvc.headsort IS NOT NULL
                           THEN (CAST(CAST('AUTO(SORTKEY(' AS varchar) AS text) || stvc.headsort) || CAST(CAST('))' AS varchar) AS text)
                         WHEN pge.autosortkey = 1
                          AND stvc.headsort IS NULL
                           THEN CAST(CAST('AUTO(SORTKEY)' AS varchar) AS text)
                         ELSE stvc.headsort
                       END AS varchar) AS text)) AS sortkey1,
       stvc.max_varchar,
       stvc.sortkey1_enc,
       stvc.sortkey_num,
       COALESCE(stvc.tblsize,
                CAST(0 AS bigint)) AS size,
       CAST((CAST(COALESCE(stvc.tblsize,
                           CAST(0 AS bigint)) AS numeric) * 100.0) / CAST((SELECT sum(sp.capacity) AS total_mb
                                                                           FROM stv_node_storage_capacity AS sp) AS numeric) AS numeric(10, 4)) AS pct_used,
       stvc.empty,
       CAST(CASE
              WHEN (COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                 OR COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 10)
               AND stvp.max_rows > 0
               AND stvc.headsort IS NOT NULL
                THEN (CAST(stvp.max_rows - stvp.max_sorted_rows AS numeric) * 100.0) / CAST(stvp.max_rows AS numeric)
              WHEN stvp.sum_rows > 0
               AND stvc.headsort IS NOT NULL
                THEN (CAST(stvp.sum_rows - stvp.sum_sorted_rows AS numeric) * 100.0) / CAST(stvp.sum_rows AS numeric)
              ELSE CAST(NULL AS numeric)
            END AS numeric(5, 2)) AS unsorted,
       CASE
         WHEN pgs.stairows = CAST(0 AS double precision)
          AND (pgs.staidels > CAST(0 AS double precision)
            OR pgs.staiins > CAST(0 AS double precision))
           THEN CAST(100 AS numeric)
         WHEN pgs.stairows = CAST(0 AS double precision)
          AND pgs.staidels = CAST(0 AS double precision)
          AND pgs.staiins = CAST(0 AS double precision)
           THEN CAST(NULL AS numeric)
         WHEN pgs.stairows IS NULL
           THEN CAST(NULL AS 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 CAST(NULL AS numeric)
         WHEN pgs.stairows > CAST(0 AS double precision)
           THEN CAST(LEAST(((pgs.staidels + pgs.staiins) * CAST(100 AS double precision)) / pgs.stairows,
                           CAST(100 AS double precision)) AS numeric(5, 2))
         ELSE CAST(NULL AS numeric)
       END AS stats_off,
       CAST(CAST(CASE
                   WHEN COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 8
                     OR COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 10
                     THEN stvp.max_rows
                   ELSE stvp.sum_rows
                 END AS numeric) AS numeric(38, 0)) AS tbl_rows,
       stvc.skew_sortkey1,
       CAST(round(CASE
                    WHEN (COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 1
                       OR COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 12)
                     AND stvp.min_rows > 0
                      THEN (CAST(stvp.max_rows AS numeric) * 1.0) / CAST(stvp.min_rows AS numeric)
                    WHEN (COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 1
                       OR COALESCE(pge.releffectivediststyle, pgc.reldiststyle) = 12)
                     AND stvp.min_rows = 0
                     AND stvp.max_rows > 0
                      THEN CAST(100 AS numeric)
                    ELSE CAST(NULL AS numeric)
                  END,
                  2) AS numeric(19, 2)) AS skew_rows,
       CAST(pgs.stairows AS numeric(38, 0)) AS estimated_visible_rows,
       pge.risk_event,
       CAST(CAST(CASE
                   WHEN stvc.sortkey_num > 0
                     THEN COALESCE(stvt.avg_qpd,
                                   CAST(0 AS bigint))
                   ELSE CAST(NULL AS bigint)
                 END AS numeric) AS numeric(12, 2)) AS vacuum_sort_benefit,
       pge.relcreationtime AS create_time
FROM pg_class AS pgc
     INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid
     INNER 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_sorted_rows,
                        min(stvp.rows) AS min_rows
                 FROM stv_tbl_perm AS stvp
                      INNER JOIN stv_slices AS stvs USING (slice)
                      INNER JOIN pg_database AS pgd ON CAST(stvp.db_id AS oid) = pgd.oid
                 GROUP BY stvp.id, pgd.datname) AS stvp ON pgc.oid = CAST(stvp.id AS oid)
     INNER JOIN (SELECT tblmeta.tbl,
                        max(tblmeta.headsort) AS headsort,
                        max(CAST(tblmeta.distkeycol AS 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 CAST(NULL AS bigint)
                            END) AS max_col_size,
                        max(CASE
                              WHEN tblmeta.sortkey_num = 1
                                THEN tblmeta.col_size
                              ELSE CAST(NULL AS bigint)
                            END) AS sortkey_col_size,
                        CAST(round((CAST(max(CASE
                                               WHEN tblmeta.sortkey_num <> 1
                                                 THEN tblmeta.col_size
                                               ELSE CAST(NULL AS bigint)
                                             END) AS numeric) * 1.0) / CAST(CASE
                                                                              WHEN max(CASE
                                                                                         WHEN tblmeta.sortkey_num = 1
                                                                                           THEN tblmeta.col_size
                                                                                         ELSE CAST(NULL AS bigint)
                                                                                       END) > 0
                                                                                THEN max(CASE
                                                                                           WHEN tblmeta.sortkey_num = 1
                                                                                             THEN tblmeta.col_size
                                                                                           ELSE CAST(NULL AS bigint)
                                                                                         END)
                                                                              ELSE CAST(NULL AS bigint)
                                                                            END AS numeric),
                                   2) AS 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,
                        CAST(btrim(max(CAST(CASE
                                              WHEN abs(tblmeta.sortkey_num) = 1
                                                THEN format_encoding(CAST(tblmeta.attencodingtype AS integer))
                                              ELSE CAST(NULL AS bpchar)
                                            END AS text))) AS char(32)) AS sortkey1_enc
                 FROM (SELECT stvts.tbl,
                              stvts.colnum,
                              btrim(CAST(CAST(CASE
                                                WHEN pga.attsortkeyord = 1
                                                 AND NOT pga.attisdropped
                                                  THEN pga.attname
                                                WHEN pga.attsortkeyord = -1
                                                 AND NOT pga.attisdropped
                                                  THEN CAST('INTERLEAVED' AS name)
                                                ELSE CAST(NULL AS name)
                                              END AS varchar) AS text)) AS headsort,
                              CAST(CASE
                                     WHEN pga.attisdistkey IS TRUE
                                       THEN pga.attname
                                     ELSE CAST(NULL AS name)
                                   END AS varchar) AS distkeycol,
                              CASE
                                WHEN pga.atttypid = CAST(1043 AS oid)
                                  THEN pga.atttypmod - 4
                                ELSE 0
                              END AS varcharlen,
                              pga.attencodingtype,
                              pga.attsortkeyord AS sortkey_num,
                              stvts.col_blkcnt AS col_size,
                              0 AS empty
                       FROM (SELECT stv_tbl_column_slice_state.table_id AS tbl,
                                    CAST(stv_tbl_column_slice_state.col + 1 AS 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,
                                      CAST(stv_tbl_column_slice_state.col + 1 AS smallint)
                             HAVING sum(stv_tbl_column_slice_state.num_blocks) > 0) AS stvts
                            LEFT JOIN pg_attribute AS pga ON CAST(stvts.tbl AS oid) = pga.attrelid
                                                         AND stvts.colnum = pga.attnum
                       WHERE stvts.tbl > 0
                       GROUP BY stvts.tbl,
                                stvts.colnum,
                                stvts.col_blkcnt,
                                btrim(CAST(CAST(CASE
                                                  WHEN pga.attsortkeyord = 1
                                                   AND NOT pga.attisdropped
                                                    THEN pga.attname
                                                  WHEN pga.attsortkeyord = -1
                                                   AND NOT pga.attisdropped
                                                    THEN CAST('INTERLEAVED' AS name)
                                                  ELSE CAST(NULL AS name)
                                                END AS varchar) AS text)),
                                CAST(CASE
                                       WHEN pga.attisdistkey IS TRUE
                                         THEN pga.attname
                                       ELSE CAST(NULL AS name)
                                     END AS varchar),
                                CASE
                                  WHEN pga.atttypid = CAST(1043 AS oid)
                                    THEN pga.atttypmod - 4
                                  ELSE 0
                                END,
                                pga.attencodingtype,
                                pga.attsortkeyord) AS tblmeta
                 GROUP BY tblmeta.tbl) AS stvc ON pgc.oid = CAST(stvc.tbl AS oid)
     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 pgs ON pgc.oid = pgs.stairelid
     LEFT JOIN (SELECT pg_class_extended.reloid,
                       btrim(CAST(CAST(max(CASE
                                             WHEN pg_class_extended.colnum = 5
                                              AND pg_class_extended.value <> CAST('' AS text)
                                               THEN replace(pg_class_extended.value,
                                                            split_part(pg_class_extended.value,
                                                                       CAST('|' AS text),
                                                                       3),
                                                            CAST(date_add(CAST('us' AS text),
                                                                          CAST(split_part(pg_class_extended.value,
                                                                                          CAST('|' AS text),
                                                                                          3) AS bigint),
                                                                          CAST('2000-01-01 00:00:00' AS timestamp)) AS text))
                                             ELSE CAST(NULL AS text)
                                           END) AS varchar) AS text)) AS risk_event,
                       max(CASE
                             WHEN pg_class_extended.colnum = 12
                              AND pg_class_extended.value = CAST(1 AS text)
                               THEN CAST(pg_class_extended.value AS smallint)
                             ELSE CAST(NULL AS smallint)
                           END) AS autosortkey,
                       max(CASE
                             WHEN pg_class_extended.colnum = 14
                              AND pg_class_extended.value = CAST(1 AS text)
                               THEN pg_class_extended.value
                             ELSE CAST(NULL AS text)
                           END) AS autoencode,
                       CAST(max(CASE
                                  WHEN pg_class_extended.colnum = 0
                                    THEN pg_class_extended.value
                                  ELSE CAST(NULL AS text)
                                END) AS smallint) AS releffectivediststyle,
                       max(CASE
                             WHEN pg_class_extended.colnum = 1
                               THEN date_add(CAST(CAST('microsecond' AS varchar) AS text),
                                             CAST(pg_class_extended.value AS bigint),
                                             CAST('2000-01-01 00:00:00' AS timestamp))
                             ELSE CAST(NULL AS timestamp)
                           END) AS relcreationtime
                FROM pg_class_extended
                GROUP BY pg_class_extended.reloid) AS pge ON pgc.oid = pge.reloid
     LEFT JOIN (SELECT stv_table_partitions.id,
                       CASE
                         WHEN avg(stv_table_partitions.qpd) = 0
                           THEN CAST(NULL AS bigint)
                         ELSE avg(stv_table_partitions.qpd)
                       END 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 stvt ON pgc.oid = CAST(stvt.id AS 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 AS pgr, pg_depend AS pgd, pg_class AS pgc, pg_class_extended AS pce, pg_namespace AS pgn
                WHERE pgr.rulename = CAST('_RETURN' AS name)
                  AND pgr.oid = pgd.objid
                  AND pgd.refobjsubid = 1
                  AND pgd.deptype = CAST('n' AS "char")
                  AND pgd.refclassid = CAST(CAST('pg_class' AS regclass) AS oid)
                  AND pgr.ev_class = pce.reloid
                  AND pce.colnum = 6
                  AND pgc.oid = pce.reloid
                  AND pgn.oid = pgc.relnamespace) AS mvi ON mvi.tbl_id = pgc.oid
WHERE pgn.nspname <> CAST('pg_automv' AS name)


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