Trouble joining svv_table_info and svv_interleaved_columns tables

1

We earlier used to run the following query to get the skew for tables with interleaved sort keys and it suddenly started failing:

Query: " select svv_table_info.table as tablename, svv_table_info.schema as schemaname, interleaved_skew from svv_interleaved_columns join svv_table_info where svv_interleaved_columns.tbl = svv_table_info.table_id"

Failing with Error: " ERROR: This type of correlated subquery pattern is not supported due to internal error"

  • We are also facing similar issue. Earlier query used to work but has started failing staring 03/30.

    SELECT 1 FROM svv_table_info svvti
                    JOIN svv_interleaved_columns svvic ON svvti.table_id = svvic.tbl;
    

    [XX000] ERROR: This type of correlated subquery pattern is not supported due to internal error

asked 10 months ago212 views
2 Answers
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;
Hi5
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;
Hi5
answered 10 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions