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