Redshift Research Project

System Table Tracker

System Table Version 1.0.41533 / 2022-09-18

schema name column data type
pg_catalog svv_interleaved_columns col int4
pg_catalog svv_interleaved_columns interleaved_skew numeric(19,2)
pg_catalog svv_interleaved_columns last_reindex timestamp
pg_catalog svv_interleaved_columns tbl int4

View Text

SELECT skew_d.tbl,
       skew_d.col,
       skew_d.interleaved_skew,
       vac_d.end_time AS last_reindex
FROM (SELECT smoothed_dtls.tbl,
             smoothed_dtls.col,
             CASE
               WHEN sum(smoothed_dtls.smoothed_count) = 0
                 THEN CAST(NULL AS numeric)
               ELSE CAST(CAST(max(smoothed_dtls.smoothed_count) AS double precision) / (CAST(sum(smoothed_dtls.smoothed_count) AS double precision) / CAST(count(DISTINCT smoothed_dtls.compressed_val) AS double precision)) AS numeric(19, 2))
             END AS interleaved_skew
      FROM (SELECT derived_table12.tbl,
                   derived_table12.col,
                   derived_table12.compressed_val,
                   derived_table12.orig_count,
                   CASE
                     WHEN derived_table12.orig_count <> 0
                      AND (derived_table12.prec_count <> 0
                        OR derived_table12.prec_count IS NULL)
                       THEN derived_table12.orig_count
                     WHEN (derived_table12.group_size - derived_table12.rank) < (derived_table12.count % derived_table12.group_size)
                       THEN (derived_table12.count / derived_table12.group_size) + 1
                     ELSE derived_table12.count / derived_table12.group_size
                   END AS smoothed_count
            FROM (SELECT wt.tbl,
                         wt.col,
                         indices.compressed_val,
                         indices.count AS orig_count,
                         wt.count,
                         pg_catalog.lead(indices.count, 1) OVER (PARTITION BY wt.tbl, wt.col
                                                                 ORDER BY indices.compressed_val DESC) AS prec_count,
                         pg_catalog.rank() OVER (PARTITION BY wt.tbl, wt.col, indices.idx
                                                 ORDER BY indices.compressed_val) AS rank,
                         count(*) OVER (PARTITION BY wt.tbl, wt.col, indices.idx
                                        ORDER BY indices.compressed_val
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS group_size
                  FROM (SELECT a.tbl,
                               a.col,
                               a.compressed_val,
                               a.count,
                               min(COALESCE(b.compressed_val, a.compressed_val)) AS idx
                        FROM (SELECT counts_sum.tbl,
                                     counts_sum.col,
                                     counts_sum.compressed_val,
                                     counts_sum.count
                              FROM (SELECT stv_interleaved_counts.tbl,
                                           stv_interleaved_counts.col,
                                           stv_interleaved_counts.index,
                                           stv_interleaved_counts.active,
                                           stv_interleaved_counts.compressed_val,
                                           sum(stv_interleaved_counts.count) AS count
                                    FROM stv_interleaved_counts
                                    GROUP BY stv_interleaved_counts.tbl, stv_interleaved_counts.col, stv_interleaved_counts.index, stv_interleaved_counts.active, stv_interleaved_counts.compressed_val) AS counts_sum
                              WHERE counts_sum.active = (SELECT min(stv_interleaved_counts.active) AS min
                                                         FROM stv_interleaved_counts
                                                         WHERE stv_interleaved_counts.tbl = counts_sum.tbl)) AS a
                             LEFT JOIN (SELECT counts_sum.tbl,
                                               counts_sum.col,
                                               counts_sum.compressed_val,
                                               counts_sum.count
                                        FROM (SELECT stv_interleaved_counts.tbl,
                                                     stv_interleaved_counts.col,
                                                     stv_interleaved_counts.index,
                                                     stv_interleaved_counts.active,
                                                     stv_interleaved_counts.compressed_val,
                                                     sum(stv_interleaved_counts.count) AS count
                                              FROM stv_interleaved_counts
                                              GROUP BY stv_interleaved_counts.tbl, stv_interleaved_counts.col, stv_interleaved_counts.index, stv_interleaved_counts.active, stv_interleaved_counts.compressed_val) AS counts_sum
                                        WHERE counts_sum.active = (SELECT min(stv_interleaved_counts.active) AS min
                                                                   FROM stv_interleaved_counts
                                                                   WHERE stv_interleaved_counts.tbl = counts_sum.tbl)) AS b ON a.tbl = b.tbl
                                                                                                                           AND a.col = b.col
                                                                                                                           AND b.compressed_val >= a.compressed_val
                                                                                                                           AND (a.count <> 0
                                                                                                                             OR b.count > a.count)
                        GROUP BY a.tbl, a.col, a.compressed_val, a.count) AS indices,
                       (SELECT counts_sum.tbl,
                               counts_sum.col,
                               counts_sum.compressed_val,
                               counts_sum.count
                        FROM (SELECT stv_interleaved_counts.tbl,
                                     stv_interleaved_counts.col,
                                     stv_interleaved_counts.index,
                                     stv_interleaved_counts.active,
                                     stv_interleaved_counts.compressed_val,
                                     sum(stv_interleaved_counts.count) AS count
                              FROM stv_interleaved_counts
                              GROUP BY stv_interleaved_counts.tbl, stv_interleaved_counts.col, stv_interleaved_counts.index, stv_interleaved_counts.active, stv_interleaved_counts.compressed_val) AS counts_sum
                        WHERE counts_sum.active = (SELECT min(stv_interleaved_counts.active) AS min
                                                   FROM stv_interleaved_counts
                                                   WHERE stv_interleaved_counts.tbl = counts_sum.tbl)) AS wt
                  WHERE indices.idx = wt.compressed_val
                    AND indices.tbl = wt.tbl
                    AND indices.col = wt.col) AS derived_table12) AS smoothed_dtls
      GROUP BY smoothed_dtls.tbl, smoothed_dtls.col) AS skew_d
     LEFT JOIN (SELECT outer_pairs.table_id,
                       outer_pairs.end_time
                FROM (SELECT vac_start.table_id,
                             vac_start.eventtime AS start_time,
                             min(vac_end.eventtime) AS end_time
                      FROM stl_vacuum AS vac_start,
                           (SELECT e.table_id,
                                   e.eventtime
                            FROM stl_vacuum AS s, stl_vacuum AS e
                            WHERE e.status = CAST('Finished' AS bpchar)
                              AND (s.status = CAST('Started' AS bpchar)
                                OR s.status = CAST('Started Sort Only' AS bpchar)
                                OR s.status = CAST('Started Reindex' AS bpchar))
                              AND s.xid = e.xid
                              AND s.table_id = e.table_id) AS vac_end
                      WHERE vac_start.table_id = vac_end.table_id
                        AND vac_start.status = CAST('Started Reindex' AS bpchar)
                        AND vac_start.eventtime < vac_end.eventtime
                      GROUP BY vac_start.table_id, vac_start.eventtime) AS outer_pairs
                WHERE outer_pairs.start_time IN (SELECT max(inner_pairs.start_time) AS max
                                                 FROM (SELECT vac_start.table_id,
                                                              vac_start.eventtime AS start_time,
                                                              min(vac_end.eventtime) AS end_time
                                                       FROM stl_vacuum AS vac_start,
                                                            (SELECT e.table_id,
                                                                    e.eventtime
                                                             FROM stl_vacuum AS s, stl_vacuum AS e
                                                             WHERE e.status = CAST('Finished' AS bpchar)
                                                               AND (s.status = CAST('Started' AS bpchar)
                                                                 OR s.status = CAST('Started Sort Only' AS bpchar)
                                                                 OR s.status = CAST('Started Reindex' AS bpchar))
                                                               AND s.xid = e.xid
                                                               AND s.table_id = e.table_id) AS vac_end
                                                       WHERE vac_start.table_id = vac_end.table_id
                                                         AND vac_start.status = CAST('Started Reindex' AS bpchar)
                                                         AND vac_start.eventtime < vac_end.eventtime
                                                       GROUP BY vac_start.table_id, vac_start.eventtime) AS inner_pairs
                                                 WHERE inner_pairs.end_time IS NOT NULL
                                                   AND inner_pairs.table_id = outer_pairs.table_id)) AS vac_d ON skew_d.tbl = vac_d.table_id


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