| schema | name | column | data type |
|---|---|---|---|
| pg_catalog | svv_vacuum_progress | database_name | varchar(128) |
| pg_catalog | svv_vacuum_progress | db_id | int4 |
| pg_catalog | svv_vacuum_progress | status | varchar(4000) |
| pg_catalog | svv_vacuum_progress | table_id | int4 |
| pg_catalog | svv_vacuum_progress | table_name | varchar(255) |
| pg_catalog | svv_vacuum_progress | time_remaining_estimate | varchar(32) |
| pg_catalog | svv_vacuum_progress | total_duration_s | int8 |
| pg_catalog | svv_vacuum_progress | vacuum_end_time | timestamp |
| pg_catalog | svv_vacuum_progress | vacuum_start_time | timestamp |
| pg_catalog | svv_vacuum_progress | xid | int8 |
SELECT derived_table17.table_name,
derived_table17.status,
derived_table17.time_remaining_estimate,
derived_table17.total_duration_s,
derived_table17.xid,
derived_table17.table_id,
derived_table17.db_id,
derived_table17.database_name,
derived_table17.vacuum_start_time,
derived_table17.vacuum_end_time
FROM (SELECT CAST(rtrim(CAST(t.name AS text)) AS varchar(255)) AS table_name,
CAST(CASE
WHEN v.status ~~* CAST('%Finished%' AS text)
THEN CAST('Complete' AS text)
WHEN z.status ~~* CAST('%Skipped%' AS text)
THEN rtrim(CAST(z.status AS text))
WHEN x.xid IS NULL
AND v.status IS NULL
THEN CAST('Failed' AS text)
ELSE rtrim(CAST(COALESCE(q.text,
CAST('Running ' AS bpchar)) AS text))
END AS varchar(4000)) AS status,
stvd.sum_rows AS tot_rows,
z.rows,
date_diff(CAST('s' AS text),
z.eventtime,
getdate()) AS curr_dur_s,
CAST(ceil(CAST(date_diff(CAST('s' AS text),
z.eventtime,
getdate()) AS numeric) * (((CAST(z.rows AS numeric) / 1.0) / CAST(stvd.sum_rows AS numeric)) - CAST(1 AS numeric))) AS bigint) AS est_dur_s,
CAST(CASE
WHEN v.status ~~* CAST('%Finished%' AS text)
OR v.status ~~ CAST('Skipped%' AS text)
OR x.xid IS NULL
THEN CAST(NULL AS text)
WHEN CAST(ceil(CAST(date_diff(CAST('s' AS text),
z.eventtime,
getdate()) AS numeric) * (((CAST(z.rows AS numeric) / 1.0) / CAST(stvd.sum_rows AS numeric)) - CAST(1 AS numeric))) AS bigint) < 0
THEN CAST('0m 0s' AS text)
ELSE ((((CAST(CAST(ceil(CAST(date_diff(CAST('s' AS text),
z.eventtime,
getdate()) AS numeric) * (((CAST(z.rows AS numeric) / 1.0) / CAST(stvd.sum_rows AS numeric)) - CAST(1 AS numeric))) AS bigint) / 3600 AS text) || CAST('h ' AS text)) || CAST((CAST(ceil(CAST(date_diff(CAST('s' AS text),
z.eventtime,
getdate()) AS numeric) * (((CAST(z.rows AS numeric) / 1.0) / CAST(stvd.sum_rows AS numeric)) - CAST(1 AS numeric))) AS bigint) % CAST(3600 AS bigint)) / 60 AS text)) || CAST('m ' AS text)) || CAST(CAST(ceil(CAST(date_diff(CAST('s' AS text),
z.eventtime,
getdate()) AS numeric) * (((CAST(z.rows AS numeric) / 1.0) / CAST(stvd.sum_rows AS numeric)) - CAST(1 AS numeric))) AS bigint) % CAST(60 AS bigint) AS text)) || CAST('s' AS text)
END AS varchar(32)) AS time_remaining_estimate,
date_diff(CAST('s' AS text),
z.eventtime,
CASE
WHEN x.xid IS NOT NULL
THEN getdate()
WHEN v.eventtime IS NOT NULL
THEN v.eventtime
ELSE CAST(NULL AS timestamp)
END) AS total_duration_s,
z.table_id,
t.db_id,
CAST(d.datname AS varchar) AS database_name,
z.eventtime AS vacuum_start_time,
v.eventtime AS vacuum_end_time,
z.xid,
CASE
WHEN x.xid IS NULL
THEN pg_catalog.row_number() OVER (PARTITION BY x.xid
ORDER BY z.xid DESC)
ELSE CAST(1 AS bigint)
END AS rownum
FROM (SELECT stl_vacuum.userid,
stl_vacuum.xid,
stl_vacuum.table_id,
stl_vacuum.status,
stl_vacuum.rows,
stl_vacuum.sortedrows,
stl_vacuum.blocks,
stl_vacuum.max_merge_partitions,
stl_vacuum.eventtime,
stl_vacuum.reclaimable_rows,
stl_vacuum.reclaimable_space_mb,
stl_vacuum.cutoff_xid,
stl_vacuum.is_recluster
FROM stl_vacuum
WHERE stl_vacuum.status !~~* CAST('%Finished%' AS text)) AS z
LEFT JOIN stl_vacuum AS v ON z.xid = v.xid
AND z.table_id = v.table_id
AND z.eventtime < v.eventtime
LEFT JOIN stv_xact AS x ON z.xid = x.xid
LEFT JOIN stv_inflight AS q ON z.xid = q.xid
LEFT JOIN stv_tbl_perm AS t ON z.table_id = t.id
AND t.slice = 0
LEFT JOIN pg_database AS d ON CAST(t.db_id AS oid) = d.oid
LEFT JOIN (SELECT derived_table16.table_id,
derived_table16.xid,
max(derived_table16.sum_max_end_row) AS sum_rows
FROM (SELECT stl_vacuum_detail.slice,
stl_vacuum_detail.table_id,
stl_vacuum_detail.xid,
stl_vacuum_detail.part,
sum(max(stl_vacuum_detail.end_row)) OVER (PARTITION BY stl_vacuum_detail.table_id, stl_vacuum_detail.xid
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sum_max_end_row
FROM stl_vacuum_detail
WHERE stl_vacuum_detail.part = -1
GROUP BY stl_vacuum_detail.slice, stl_vacuum_detail.table_id, stl_vacuum_detail.xid, stl_vacuum_detail.part) AS derived_table16
WHERE derived_table16.slice = 0
GROUP BY derived_table16.table_id, derived_table16.xid) AS stvd ON z.xid = stvd.xid
AND z.table_id = stvd.table_id
ORDER BY q.query DESC,
z.eventtime DESC) AS derived_table17
WHERE derived_table17.rownum <= 2 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