Module: PGTrunk::Operations::Statistics

Defined in:

lib/pg_trunk/operations/statistics.rb,
lib/pg_trunk/operations/statistics/base.rb,
lib/pg_trunk/operations/statistics/drop_statistics.rb,
lib/pg_trunk/operations/statistics/create_statistics.rb,
lib/pg_trunk/operations/statistics/rename_statistics.rb

Defined Under Namespace

Classes: Base, CreateStatistics, DropStatistics, RenameStatistics

Constant Summary

SQL_V10 =

SQL snippet to fetch statistics in v10-13

<<~SQL.freeze
  WITH
    list (key, name) AS (
      VALUES ('m', 'mcv'), ('f', 'dependencies'), ('d', 'ndistinct')
    )
  SELECT
    s.oid,
    (s.stxnamespace::regnamespace || '.' || s.stxname) AS name,
    (t.relnamespace::regnamespace || '.' || t.relname) AS "table",
    (
      SELECT array_agg(l.name)
      FROM list l
      WHERE ARRAY[l.key]::char[] <@ s.stxkind::char[]
    ) AS kinds,
    (
      SELECT array_agg(DISTINCT a.attname)
      FROM pg_attribute a
      WHERE a.attrelid = s.stxrelid
        AND ARRAY[a.attnum]::int[] <@ s.stxkeys::int[]
    ) AS columns,
    d.description AS comment
  FROM pg_statistic_ext s
    JOIN pg_trunk e ON e.oid = s.oid AND e.classid = 'pg_statistic_ext'::regclass
    JOIN pg_class t ON t.oid = s.stxrelid
    LEFT JOIN pg_description d ON d.objoid = s.oid;
SQL

SQL_V14 =

In version 14 statistics can be collected for expressions.

<<~SQL.freeze
  WITH
    list (key, name) AS (
      VALUES ('m', 'mcv'), ('f', 'dependencies'), ('d', 'ndistinct')
    )
  SELECT
    s.oid,
    (s.stxnamespace::regnamespace || '.' || s.stxname) AS name,
    (t.relnamespace::regnamespace || '.' || t.relname) AS "table",
    (
      SELECT array_agg(l.name)
      FROM list l
      WHERE ARRAY[l.key]::char[] <@ s.stxkind::char[]
    ) AS kinds,
    (
      SELECT array_agg(DISTINCT a.attname)
      FROM pg_attribute a
      WHERE a.attrelid = s.stxrelid
        AND ARRAY[a.attnum]::int[] <@ s.stxkeys::int[]
    ) AS columns,
    pg_get_expr(s.stxexprs, stxrelid, true) AS expressions,
    d.description AS comment
  FROM pg_statistic_ext s
    JOIN pg_trunk e ON e.oid = s.oid AND e.classid = 'pg_statistic_ext'::regclass
    JOIN pg_class t ON t.oid = s.stxrelid
    LEFT JOIN pg_description d ON d.objoid = s.oid;
SQL