2 Answers
- Newest
- Most votes
- Most comments
0
A temporary fix can be new view creation based on definition of svv_interleaved_columns by removing all correlated subqueries and use it in join condition. Refer below code for the same.
create or replace view <schema_name>.new_svv_interleaved_columns(tbl, col, interleaved_skew, last_reindex) as
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 NULL::numeric
ELSE ("max"(smoothed_dtls.smoothed_count)::double precision /
(sum(smoothed_dtls.smoothed_count)::double precision /
count(DISTINCT smoothed_dtls.compressed_val)::double precision))::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)
counts_sum
JOIN
(SELECT tbl, min(stv_interleaved_counts.active) AS min_val
FROM stv_interleaved_counts
GROUP BY tbl
) min_sic
ON counts_sum.active = min_sic.min_val
AND counts_sum.tbl = min_sic.tbl
GROUP BY counts_sum.tbl, counts_sum.col, counts_sum.compressed_val, counts_sum.count
) 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
) counts_sum
JOIN
(SELECT tbl, min(stv_interleaved_counts.active) AS min_val
FROM stv_interleaved_counts
GROUP BY tbl
) min_sic
ON counts_sum.active = min_sic.min_val
AND counts_sum.tbl = min_sic.tbl
GROUP BY counts_sum.tbl, counts_sum.col, counts_sum.compressed_val, counts_sum.count
) 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) 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 ) counts_sum
JOIN
(SELECT tbl, min(stv_interleaved_counts.active) AS min_val
FROM stv_interleaved_counts
GROUP BY tbl
) min_sic
ON counts_sum.active = min_sic.min_val
AND counts_sum.tbl = min_sic.tbl
GROUP BY counts_sum.tbl, counts_sum.col, counts_sum.compressed_val, counts_sum.count
) wt
WHERE indices.idx = wt.compressed_val
AND indices.tbl = wt.tbl
AND indices.col = wt.col) derived_table13) smoothed_dtls
GROUP BY smoothed_dtls.tbl, smoothed_dtls.col) 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 vac_start,
(SELECT e.table_id, e.eventtime
FROM stl_vacuum s,
stl_vacuum e
WHERE e.status = 'Finished'::bpchar
AND (s.status = 'Started'::bpchar OR s.status = 'Started Sort Only'::bpchar OR
s.status = 'Started Reindex'::bpchar)
AND s.xid = e.xid
AND s.table_id = e.table_id) vac_end
WHERE vac_start.table_id = vac_end.table_id
AND vac_start.status = 'Started Reindex'::bpchar
AND vac_start.eventtime < vac_end.eventtime
GROUP BY vac_start.table_id, vac_start.eventtime
) outer_pairs
JOIN (SELECT temp_inner.table_id, max(temp_inner.start_time) AS max_start_time
FROM (SELECT vac_start.table_id,
vac_start.eventtime AS start_time,
min(vac_end.eventtime) AS end_time
FROM stl_vacuum vac_start,
(SELECT e.table_id, e.eventtime
FROM stl_vacuum s,
stl_vacuum e
WHERE e.status = 'Finished'::bpchar
AND (s.status = 'Started'::bpchar OR
s.status = 'Started Sort Only'::bpchar OR
s.status = 'Started Reindex'::bpchar)
AND s.xid = e.xid
AND s.table_id = e.table_id) vac_end
WHERE vac_start.table_id = vac_end.table_id
AND vac_start.status = 'Started Reindex'::bpchar
AND vac_start.eventtime < vac_end.eventtime
GROUP BY vac_start.table_id, vac_start.eventtime
) temp_inner
WHERE temp_inner.end_time IS NOT NULL
GROUP BY temp_inner.table_id
) inner_pairs
ON outer_pairs.start_time = inner_pairs.max_start_time
AND inner_pairs.table_id = outer_pairs.table_id
) vac_d
ON skew_d.tbl = vac_d.table_id;
answered 10 months ago
0
@AWS Support - I'm wondering why there are so many correlated subqueries in Redshift system views. Converting them to joins will make code easier to read and performant. Also, use of WITH clauses will make codes further more readable.
Sample code
CREATE OR REPLACE VIEW <view_name>
AS
WITH a AS (SELECT 1 aa) /*Doing task A*/
, b AS (SELECT 1 bb) /*Doing task B*/
SELECT * FROM a
JOIN b
ON a.aa = b.bb;
answered 10 months ago
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
We are also facing similar issue. Earlier query used to work but has started failing staring 03/30.
[XX000] ERROR: This type of correlated subquery pattern is not supported due to internal error