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

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

SQL_V14 =

In version 14 statistics can be collected for expressions.

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