export_effective_stats-9.3.sql.sample

   1 /*
   2  * If you want the statistics of per-relation or per-column, please modify
   3  * nspname, relname, and attname in 'WHERE' clause.
   4  */
   5
   6 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   7 /*
   8  * If you want to chage the output file name, please modify the following name.
   9  */
  10 \o export_stats.dmp
  11
  12 COPY (SELECT n2.nspname,
  13              cl.relname,
  14              r.relpages,
  15              r.reltuples,
  16              r.relallvisible,
  17              r.curpages,
  18              r.last_analyze,
  19              r.last_autoanalyze,
  20              COALESCE(a.attname, ''),
  21              n1.nspname AS schemaname_of_atttype,
  22              t.typname,
  23              a.atttypmod,
  24              co.stainherit,
  25              co.stanullfrac,
  26              co.stawidth,
  27              co.stadistinct,
  28              co.stakind1,
  29              co.stakind2,
  30              co.stakind3,
  31              co.stakind4,
  32              co.stakind5,
  33              co.staop1,
  34              co.staop2,
  35              co.staop3,
  36              co.staop4,
  37              co.staop5,
  38              co.stanumbers1,
  39              co.stanumbers2,
  40              co.stanumbers3,
  41              co.stanumbers4,
  42              co.stanumbers5,
  43              co.stavalues1,
  44              co.stavalues2,
  45              co.stavalues3,
  46              co.stavalues4,
  47              co.stavalues5
  48         FROM dbms_stats.column_stats_effective co
  49         JOIN pg_attribute a
  50           ON (co.starelid = a.attrelid AND co.staattnum = a.attnum)
  51         JOIN pg_type t
  52           ON a.atttypid = t.oid
  53         JOIN pg_namespace n1
  54           ON t.typnamespace = n1.oid
  55        RIGHT JOIN dbms_stats.relation_stats_effective r
  56           ON co.starelid = r.relid
  57         JOIN pg_catalog.pg_class cl
  58           ON r.relid = cl.oid
  59         JOIN pg_catalog.pg_namespace n2
  60           ON cl.relnamespace = n2.oid
  61        -- WHERE n2.nspname = 'public'
  62        -- AND cl.relname = 'test'
  63        -- AND a.attname = 'id'
  64        ORDER BY starelid, staattnum)
  65 TO STDOUT
  66 (FORMAT 'binary');
  67 \o
  68 COMMIT;