| 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)
WHERE stg_d.schema_n !~~ CAST('pg_automv' AS text)
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