Redshift Observatory

System Table Tracker

System view pg_catalog.svv_vacuum_progress version 1.0.74097 / 2024-09-02

View Text

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(q.text AS text))
                  END AS varchar(4000)) AS status,
             CAST(CASE
                    WHEN v.status ~~* CAST('%Finished%' AS text)
                      OR v.status ~~ CAST('Skipped%' AS text)
                      OR x.xid IS NULL
                      OR e.est_remaining IS NULL
                      THEN CAST(NULL AS text)
                    WHEN CAST(e.est_remaining AS text) < CAST(0 AS text)
                     AND NOT(v.status ~~* CAST('%Finished%' AS text)
                          OR v.status ~~ CAST('Skipped%' AS text)
                          OR x.xid IS NULL
                          OR e.est_remaining IS NULL)
                      THEN CAST('0m 0s' AS text)
                    ELSE ((CAST(CAST(date_part(CAST('minute' AS text),
                                               e.est_remaining) AS varchar) AS text) + CAST('m ' AS text)) + CAST(CAST(date_part(CAST('second' AS text),
                                                                                                                                 e.est_remaining) - (date_part(CAST('minute' AS text),
                                                                                                                                                               e.est_remaining) * CAST(60 AS double precision)) AS varchar) 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.xid,
                             max(derived_table16.time_remaining) AS est_remaining
                      FROM (SELECT q.query,
                                   q.xid,
                                   d.slice,
                                   sum(d.num_blocks_replaced) AS blocks_replaced,
                                   sum(q.endtime - q.starttime) AS etime,
                                   CASE
                                     WHEN sum(d.num_blocks_replaced) = 0
                                       OR max(d.total_block_io_estimate) = 0
                                       THEN CAST(NULL AS interval)
                                     ELSE (sum(q.endtime - q.starttime) / CAST(CAST(sum(d.num_blocks_replaced) AS real) / CAST(max(d.total_block_io_estimate) AS real) AS double precision)) - sum(q.endtime - q.starttime)
                                   END AS time_remaining
                            FROM stl_query AS q
                                 INNER JOIN stl_vacuum_detail AS d USING (xid, query)
                            GROUP BY q.query, q.xid, d.slice) AS derived_table16
                      GROUP BY derived_table16.xid) AS e ON z.xid = e.xid
      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