-- Identify parents whose DEFAULT partition holds >= 70% of total rows.
-- Assumes ANALYZE has been run recently (uses reltuples as estimates).
-- Greenplum 7 version

WITH child_parts AS (
  SELECT
    p.schemaname            AS parent_schema,
    p.tablename             AS parent_table,
    p.partitionschemaname   AS child_schema,
    p.partitiontablename    AS child_table,
    p.partitionisdefault    AS is_default
  FROM gp_toolkit.gp_partitions p
  WHERE p.partitionlevel = 0
),
row_estimates AS (
  SELECT
    parent_schema,
    parent_table,
    SUM(CASE WHEN is_default THEN c.reltuples ELSE 0 END) AS default_rows,
    SUM(c.reltuples)                                     AS total_rows
  FROM child_parts cp
  JOIN pg_class c
    ON c.oid = (quote_ident(cp.child_schema)||'.'||quote_ident(cp.child_table))::regclass
  GROUP BY parent_schema, parent_table
)
SELECT
  parent_schema || '.' || parent_table AS table_name,
  COALESCE(default_rows,0)::bigint     AS default_row_est,
  COALESCE(total_rows,0)::bigint       AS total_row_est,
  CASE WHEN total_rows > 0
       THEN ROUND(100.0 * default_rows::bigint / total_rows::bigint, 1)
       ELSE 0 END                      AS default_pct
FROM row_estimates
WHERE total_rows > 0
  AND default_rows >= 0.7 * total_rows
ORDER BY default_pct DESC, table_name;

Tip: If you’re using subpartitioning, run a similar query at the subpartition level (change partitionlevel filter) to catch “hidden” defaults there too.