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) |
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