Redshift Observatory

System Table Tracker

System view pg_catalog.svv_vacuum_summary version 1.0.45698 / 2023-01-27

View Text

SELECT COALESCE(rtrim(CAST(t.name AS text)),
                CAST('TABLE DROPPED' AS text)) AS table_name,
       v.xid,
       s.parts AS sort_partitions,
       m.increments AS merge_increments,
       date_diff(CAST('microsec' AS text),
                 v.eventtime,
                 v2.eventtime) AS elapsed_time,
       v2.rows - v.rows AS row_delta,
       v2.sortedrows - v.sortedrows AS sortedrow_delta,
       v2.blocks - v.blocks AS block_delta,
       v.max_merge_partitions
FROM stl_vacuum AS v
     INNER JOIN (SELECT stl_query.xid,
                        count(*) AS parts
                 FROM stl_query
                 WHERE rtrim(CAST(stl_query.querytxt AS text)) ~~ CAST('% sort (partition%' AS text)
                    OR rtrim(CAST(stl_query.querytxt AS text)) ~~ CAST('Vacuum: delete %' AS text)
                 GROUP BY stl_query.xid) AS s ON v.xid = s.xid
     INNER JOIN (SELECT stl_query.xid,
                        count(*) AS increments
                 FROM stl_query
                 WHERE rtrim(CAST(stl_query.querytxt AS text)) ~~ CAST('% merge (increment%' AS text)
                    OR rtrim(CAST(stl_query.querytxt AS text)) ~~ CAST('Vacuum: delete %' AS text)
                 GROUP BY stl_query.xid) AS m ON v.xid = m.xid
     LEFT JOIN stv_tbl_perm AS t ON v.table_id = t.id
                                AND t.slice = 0,
     stl_vacuum AS v2
WHERE v.status <> CAST('Finished' AS bpchar)
  AND v2.status = CAST('Finished' AS bpchar)
  AND v.xid = v2.xid
ORDER BY COALESCE(rtrim(CAST(t.name AS text)),
                  CAST('TABLE DROPPED' AS text)),
         v.xid

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