| 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 |
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_table13.tbl,
derived_table13.col,
derived_table13.compressed_val,
derived_table13.orig_count,
CASE
WHEN derived_table13.orig_count <> 0
AND (derived_table13.prec_count <> 0
OR derived_table13.prec_count IS NULL)
THEN derived_table13.orig_count
WHEN (derived_table13.group_size - derived_table13.rank) < (derived_table13.count % derived_table13.group_size)
THEN (derived_table13.count / derived_table13.group_size) + 1
ELSE derived_table13.count / derived_table13.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_table13) 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