--
-- $Id: pg_advisor.sql,v 1.69 2004/03/21 17:38:52 coelho Exp $
--
-- pg_advisor maybe future system schema?
--
-- at the time it is 'xpg_advisor',
-- as the 'pg_' prefix is reserved for system schemas.
--
-- how to localise comments? descriptions?
-- should this use 'information_schema' instead of 'pg_catalog'?
-- more explicit comments about views? with examples?
-- add comments to all columns?
-- give a grade/mark? ;-)
--

DROP SCHEMA xpg_advisor CASCADE;

CREATE SCHEMA xpg_advisor;

COMMENT ON SCHEMA xpg_advisor IS 
'Various advices about database design or performance.
This views check various common \'maybe\' common faults using
available information from the table descriptions. It deals
primarily with constraints (foreign key, primary key, unique)
and attempts to detect inconsistancies.'
;

SET search_path TO xpg_advisor,pg_catalog;

-------------------------------------------------------------------------------
--
-- UTILS
--

CREATE TABLE advice_kind(
  kid INTEGER PRIMARY KEY, 
  description TEXT UNIQUE NOT NULL
);

COPY advice_kind(kid,description) FROM STDIN;
1	misc
2	design
3	performance
\.

COMMENT ON TABLE advice_kind IS
'kind of advice such as \'performance\' or \'design\''
;

CREATE TABLE advice_severity(
  sid INTEGER PRIMARY KEY, 
  description TEXT UNIQUE NOT NULL
);

COPY advice_severity(sid,description) FROM STDIN;
1	info
2	notice
3	warning
4	error
\.

COMMENT ON TABLE advice_severity IS
'severity description of an advice, from \'info\' to \'serious\''
;

CREATE TABLE advice_classification(
  advice OID PRIMARY KEY, -- REFERENCES pg_class(oid), 
  -- the following denormalisation is NECESSARY at the time.
  name TEXT UNIQUE NOT NULL, -- copy of pg_class.relname of above
  kid INTEGER NOT NULL REFERENCES advice_kind,
  sid INTEGER NOT NULL REFERENCES advice_severity,
  title TEXT UNIQUE NOT NULL,
  abstract TEXT UNIQUE NOT NULL,
  description TEXT
);

COMMENT ON TABLE advice_classification IS
'advice classification such as kind, severity...'
;

-- 
-- may be somewhere?
-- 
CREATE OR REPLACE FUNCTION is_system_schema(TEXT) RETURNS BOOLEAN AS '
SELECT $1 LIKE \'pg_%\' OR $1 LIKE \'xpg_%\' OR $1 = \'information_schema\';
' LANGUAGE 'SQL';

COMMENT ON FUNCTION is_system_schema(TEXT) IS '
tells whether a schema name is system or user.
includes extensions as xpg_* or information_schema.
';

-- test:
-- SELECT is_system_schema('pg_catalog');
-- SELECT is_system_schema('public');

--
-- missing array_index function. quick PL/pgSQL fix.
--
CREATE OR REPLACE FUNCTION array_index(anyarray, anyelement) 
RETURNS INTEGER AS '
DECLARE
  tab ALIAS FOR $1;
  item ALIAS FOR $2;
  i INTEGER;
BEGIN
  FOR i IN 1..array_upper(tab, 1) LOOP
    IF tab[i] = item THEN
      RETURN i;
    END IF;
  END LOOP;
  -- item not found in tab
  RETURN NULL;
END;'
LANGUAGE plpgsql;

COMMENT ON FUNCTION array_index(anyarray, anyelement) IS
'missing array_index function... should be already there!'
;

-- test whether two array contents are set-equal.
-- [1,1,2] and [2,1,2] are consided equal.
CREATE OR REPLACE FUNCTION array_ceq(anyarray, anyarray) 
RETURNS BOOLEAN AS '
DECLARE
  tab1 ALIAS FOR $1;
  tab2 ALIAS FOR $2;
  i INTEGER;
BEGIN
  IF array_upper(tab1, 1) != array_upper(tab2, 1) THEN
    RETURN FALSE;
  END IF;
  FOR i IN 1..array_upper(tab1, 1) LOOP
    IF NOT tab1[i] = ANY (tab2) THEN
      RETURN FALSE;
    END IF;
    IF NOT tab2[i] = ANY (tab1) THEN
      RETURN FALSE;
    END IF;
  END LOOP;
  RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

COMMENT ON FUNCTION array_ceq(anyarray, anyarray) IS
'equality of content of two arrays in any order'
;

--
-- how to SELECT COUNT(*) FROM [the view of which I have the oid]?
-- here is the missing function, that may be somewhere I guess?
--
CREATE TABLE counter(call OID);

CREATE OR REPLACE FUNCTION count_tuples(TEXT) 
RETURNS INTEGER AS '
DECLARE
  tablename ALIAS FOR $1;
  res RECORD;
BEGIN
  -- should prevent recursion? how?
  FOR res IN EXECUTE \'SELECT COUNT(*) AS n FROM xpg_advisor.\' || tablename
  LOOP
    RETURN res.n;
  END LOOP;
END;'
LANGUAGE 'plpgsql';

COMMENT ON FUNCTION count_tuples(OID) IS 
'meta function for meta queries...
- count number of rows in table or view, given its oid.
- beware, this function is dangerous (costly, infinite recursion).'
;

-- ??? how to advise postgres optimiser that count_tuples is very expensive?

--
-- add advice classification
--
CREATE OR REPLACE FUNCTION 
  set_advice_info(TEXT, INTEGER, INTEGER, TEXT, TEXT, TEXT)
RETURNS INTEGER AS '
INSERT INTO 
  advice_classification(advice, name, kid, sid, title, abstract, description)
SELECT c.oid, $1, $2, $3, $4, $5, $6
FROM pg_namespace AS n JOIN pg_class AS c ON (c.relnamespace=n.oid)
WHERE n.nspname=current_schema() AND c.relname=$1;
SELECT 1;'
LANGUAGE 'SQL';

COMMENT ON FUNCTION 
set_advice_info(TEXT, INTEGER, INTEGER, TEXT, TEXT, TEXT) IS
'add an advice information to advice_classification table:
- view name of the advice
- kind of advice number (see advice_kind)
- severity of advice number (see advice_severity)
- advice title (a short sentence)
- advice abstract (a paragraph)
- advice description (a longer explanation)'
;

-------------------------------------------------------------------------------
--
-- DESIGN ADVICES views
--
-- the comment should justify really why these advices are given.
-- I'm not even sure they are good advices;-)

--
-- tables without primary keys
--

CREATE VIEW da_tables_without_primary_key AS 
SELECT
  n.nspname AS SchemaName,
  c.relname AS TableName
FROM pg_class AS c 
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
  -- no comments about system catalogs.
  NOT is_system_schema(n.nspname)
  -- only tables
  AND c.relkind = 'r' 
  -- no primary key
  AND NOT c.relhaspkey
ORDER BY SchemaName ASC, TableName ASC;

COMMENT ON VIEW da_tables_without_primary_key IS 
'advice: all tables without a declared primary key'
;

SELECT set_advice_info('da_tables_without_primary_key', 2, 4,
'no primary key',
'a primary key on every table is better design...
- what is the point of having a table, if you do not have
  and identifiable concept to put inside?
- relational algebra is build upon the set theory. 
  relations are sets. sets only store distinct elements.
- maybe some UNIQUE NOT NULL attribute should be the primary key?',
NULL
);

-- SELECT * FROM da_tables_without_primary_key;

--
-- tables with composite primary keys?
-- or should it be declared only if foreigns??
-- or if partial???
-- or not if parts are foreign keys?
-- or it is ok anyway?
-- 

CREATE VIEW da_tables_with_composite_primary_key AS
SELECT 
  n.nspname AS SchemaName, 
  c.relname AS TableName
FROM pg_class AS c 
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
  JOIN pg_constraint AS o ON (o.conrelid=c.oid)
WHERE
  -- no comments about system catalogs.
  NOT is_system_schema(n.nspname)
  -- only tables
  AND c.relkind = 'r' 
  -- with a primary key
  AND c.relhaspkey
  -- the primary key constraint
  AND contype = 'p'
  -- more than 1 element in conkey
  AND array_upper(o.conkey, 1)>1
  -- partial primary key???
  AND relnatts>array_upper(o.conkey, 1)
ORDER BY SchemaName ASC, TableName ASC;

COMMENT ON VIEW da_tables_with_composite_primary_key IS 
'advice: all tables with composite primary keys';

SELECT set_advice_info('da_tables_with_composite_primary_key', 2, 1,
'composite partial primary key',
'- I prefer a simple primary key, without semantics...
  because foreign keys if any will be simpler.',
NULL
);

-- SELECT * FROM da_tables_with_composite_primary_key;

--
-- composite foreign key...
--
CREATE VIEW da_composite_foreign_key AS
SELECT
  n.nspname AS SchemaName,
  c.relname AS TableName,
  o.conname AS ConstraintName
FROM pg_class AS c 
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
  JOIN pg_constraint AS o ON (o.conrelid=c.oid)
WHERE
  -- no comments about system catalogs.
  NOT is_system_schema(n.nspname)
  -- only tables
  AND c.relkind = 'r' 
  -- the foreign key constraint
  AND contype = 'f'
  -- more than 1 element in confkey
  AND array_upper(o.confkey, 1)>1
ORDER BY SchemaName ASC, TableName ASC, ConstraintName ASC;

COMMENT ON VIEW da_composite_foreign_key IS 
'advice: composite foreign key'
;

SELECT set_advice_info('da_composite_foreign_key', 2, 2,
'composite foreign key',
'simple foreign keys are better design...
- the foreign key replicates the information that some
  referenced key components belong together.
- on updates of the referenced keys, 
  all corresponding foreign keys must be updated.',
NULL
);

-- SELECT * FROM da_composite_foreign_key;

--
-- I like simple INTEGER PRIMARY KEY better
--
CREATE VIEW da_non_integer_primary_key AS
SELECT -- DISTINCT
  n.nspname AS SchemaName, 
  t.relname AS TableName,
  a.attname AS AttName,
  format_type(a.atttypid, a.atttypmod) AS TypeName
--  c.conname AS ConstraintName
FROM pg_namespace AS n
  JOIN pg_class AS t ON (t.relnamespace=n.oid)
  JOIN pg_constraint AS c ON (c.conrelid=t.oid)
  JOIN pg_attribute AS a ON (a.attnum = ANY (c.conkey) AND a.attrelid=t.oid)
  JOIN pg_type AS typ ON (a.atttypid=typ.oid)
  JOIN pg_namespace AS ntyp ON (typ.typnamespace=ntyp.oid)
WHERE
   -- no comments about system schema
   NOT is_system_schema(n.nspname)
   -- only tables (redundant?)
   AND t.relkind = 'r'
   -- primary key contraint
   AND c.contype = 'p'
   -- the key type is not an integer? or a short type??
   AND ntyp.nspname='pg_catalog' 
   AND NOT typ.typname = ANY (ARRAY['int2', 'int4', 'int8', 'oid'])
ORDER BY SchemaName ASC, TableName ASC;

COMMENT ON VIEW da_non_integer_primary_key IS
'advice: non integer primary key'
;

SELECT set_advice_info('da_non_integer_primary_key', 2, 1,
'primary key is not an integer',
'not really a problem, but:
- I like semantics integer primary keys without application semantics...
- it makes updates easier, as the number will not need to be changed,
  hence matching foreign keys won\'t need to be changed either.',
NULL
);

--
-- unique constraint with nullable attribute?
--

CREATE VIEW da_unique_nullable_attribute AS
SELECT
  nc.nspname AS SchemaName,
  c.relname AS TableName,
  a.attname AS AttName,
  no.nspname AS ConstraintSchemaName,
  o.conname AS ConstraintName
FROM pg_class AS c 
  JOIN pg_namespace AS nc ON (c.relnamespace=nc.oid)
  JOIN pg_constraint AS o ON (o.conrelid=c.oid)
  JOIN pg_namespace AS no ON (o.connamespace=no.oid)
  JOIN pg_attribute AS a ON (a.attrelid=c.oid)
WHERE
  -- no comments about system catalogs.
  NOT is_system_schema(nc.nspname) AND NOT is_system_schema(no.nspname)
  -- only tables (redundant, constraint are necessary on tables?)
  AND c.relkind = 'r'
  -- it is a 'unique' constraint
  AND contype = 'u'
  -- attribute is in the constraint
  AND a.attnum = ANY (o.conkey)
  -- attribute is nullable
  AND NOT a.attnotnull
ORDER BY SchemaName ASC, TableName ASC, AttName ASC,
         ConstraintSchemaName ASC, ConstraintName ASC;

COMMENT ON VIEW da_unique_nullable_attribute IS
'advice: unique constraint with nullable attribute'
;

SELECT set_advice_info('da_unique_nullable_attribute', 2, 2,
'unique constraint with nullable attribute',
'maybe a bad design... either
- the NULL value has a particular semantic for an attribute
- or you forgot a NOT NULL declaration',
NULL
);

-- SELECT * FROM da_unique_nullable_attribute;

CREATE VIEW da_nullable_attribute AS
SELECT 
  n.nspname AS SchemaName,
  c.relname AS TableName,
  a.attname AS AttName
FROM pg_class AS c 
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
  JOIN pg_attribute AS a ON (a.attrelid=c.oid)
WHERE
  -- no comments about system catalogs.
  NOT is_system_schema(n.nspname)
  -- only tables (redundant, constraint are necessary on tables?)
  AND c.relkind = 'r'
  -- attribute is nullable
  AND NOT a.attnotnull
ORDER BY SchemaName ASC, TableName ASC, AttName ASC;

COMMENT ON VIEW da_nullable_attribute IS
'advice: nullable attributes'
;

SELECT set_advice_info('da_nullable_attribute', 2, 1,
'nullable attribute',
'- most of the time attributes should be NOT NULL
- but it is perfectly right to have a NULL value of missing fields',
NULL
);

--
-- internal foreign key constraint...
--
CREATE VIEW in_foreign_key_contraint AS
SELECT
  -- constraint
  c.oid AS constraint, 
  -- referencing table
  c.conrelid AS crel, 
  cca.attname AS ccol,
  cca.atttypid AS ctyp,
  cca.atttypmod AS cmod,
  cca.attlen AS clen,
  -- referenced table, foreign part
  c.confrelid AS frel, 
  fka.attname AS fcol, 
  fka.atttypid AS ftyp, 
  fka.atttypmod AS fmod,
  fka.attlen AS flen,
  array_index(c.confkey, fka.attnum) AS component
FROM pg_constraint AS c
     JOIN pg_attribute AS cca ON (c.conrelid=cca.attrelid)
     JOIN pg_attribute AS fka ON (c.confrelid=fka.attrelid)
WHERE
  -- foreign key constraint
  c.contype='f'
  -- column attribute in constraint
  AND cca.attnum = ANY (c.conkey)
  -- foreign key attribute
  AND fka.attnum = ANY (c.confkey)
  -- matching key component
  AND array_index(c.confkey, fka.attnum)=array_index(c.conkey, cca.attnum)
;

-- verbose view
CREATE VIEW in_verbose_foreign_key_contraint AS
SELECT 
  -- ready for pretty printing
  nc.nspname AS SchemaName,
  cc.relname AS TableName,
  fkc.ccol AS AttName,
  format_type(fkc.ctyp, fkc.cmod) AS ColumnType,
  nf.nspname AS FSchemaName,
  cf.relname AS FName,
  fkc.fcol AS FAttName,
  format_type(fkc.ftyp, fkc.fmod) AS ForeignType,
  -- row stuff for selection
  fkc.*
FROM in_foreign_key_contraint AS fkc 
  JOIN pg_class AS cc ON (fkc.crel=cc.oid)
  JOIN pg_namespace AS nc ON (cc.relnamespace=nc.oid)
  JOIN pg_class AS cf ON (fkc.frel=cf.oid)
  JOIN pg_namespace AS nf ON (cf.relnamespace=nf.oid)
WHERE
  -- no comments about system catalogs.
  NOT is_system_schema(nc.nspname) AND NOT is_system_schema(nf.nspname)
  -- only tables (redundant? only tables can have ri contraints?)
  AND cc.relkind='r' AND cf.relkind='r';

--
-- foreign keys which do not exactly match their target key types?
--

CREATE VIEW da_foreign_key_type_dont_match AS
SELECT SchemaName, TableName, AttName, ColumnType,
       FSchemaName, FName, FAttName, ForeignType,
       Component
FROM in_verbose_foreign_key_contraint
WHERE ctyp!=ftyp -- non matching type
ORDER BY SchemaName ASC, TableName ASC, 
         FSchemaName ASC, FName ASC, Component ASC;

COMMENT ON VIEW da_foreign_key_type_dont_match IS
'advice: non matching foreign key types'
;

SELECT set_advice_info('da_foreign_key_type_dont_match', 2, 3,
'non exactly matching foreign key types',
'maybe a bad design...
- do you have any argument why the type shouldn\'t be the same?',
NULL
);

-- SELECT * FROM da_foreign_key_type_dont_match;

--
-- foreign keys which do match their target types but with different sizes?
--
CREATE VIEW da_foreign_key_type_match_but_size AS
SELECT SchemaName, TableName, AttName, ColumnType,
       FSchemaName, FName, FAttName, ForeignType,
       Component
FROM in_verbose_foreign_key_contraint
WHERE ctyp=ftyp -- matching type
  -- but non matching variable length
  AND clen=-1 AND flen=-1 AND cmod!=fmod
ORDER BY SchemaName ASC, TableName ASC,
         FSchemaName ASC, FName ASC, Component ASC;

COMMENT ON VIEW da_foreign_key_type_match_but_size IS
'advice: non matching size in foreign key types'
;

SELECT set_advice_info('da_foreign_key_type_match_but_size', 2, 3,
'non exactly matching foreign key type sizes',
'maybe a bad design... 
- do you have any argument why the type size shouldn\'t be the same?
- as a foreign key must match its referenced key, 
  all matching values will necessarily be the same.
  . if the foreign key is smaller, some referenced key cannot be matched.
  . if the foreign key is larger, the added part cannot be used.',
NULL
);

--
-- tables with large primary keys (such as TEXT, VARCHAR(64)...)?
--   if there are foreign keys that use them...
--
CREATE VIEW da_large_primary_key_with_foreigns AS
SELECT SchemaName, TableName, AttName, ColumnType,
       FSchemaName, FName, FAttName, ForeignType,
       Component
FROM in_verbose_foreign_key_contraint
WHERE ctyp=ftyp -- matching type
  -- variable length
  AND clen=-1 AND flen=-1 
  -- matching (otherwise already in previous table)
  AND cmod=fmod 
  -- but large (16 bytes) or unbounded (e.g. TEXT)
  AND (cmod>16 OR cmod=-1)
ORDER BY SchemaName ASC, TableName ASC,
         FSchemaName ASC, FName ASC, Component ASC;

COMMENT ON VIEW da_large_primary_key_with_foreigns IS
'advice: large primary key with foreign keys'
;

SELECT set_advice_info('da_large_primary_key_with_foreigns', 2, 2,
'large key types for foreign keys',
'maybe a bad design...
- if the type is large, it means that there are some information inside,
  and this information is replicated in foreign keys.
- if the key is meaningful and must be updated, all foreign keys will
  have to be updated as well.
- maybe you should rather have an simple primary key,
  and a UNIQUE NOT NULL field?',
NULL
);

--
-- other design advices?
--

CREATE VIEW da_foreign_key_not_to_primary AS
SELECT
  nf.nspname AS FSchemaName,
  clf.relname AS FTableName,
  np.nspname AS SchemaName,
  clp.relname AS TableName
FROM 
  -- foreign key part
    pg_namespace AS nf
    JOIN pg_class AS clf ON (clf.relnamespace=nf.oid)
    JOIN pg_constraint AS cf ON (cf.conrelid=clf.oid)
  JOIN
  -- primary key part
    pg_namespace AS np
    JOIN pg_class AS clp ON (clp.relnamespace=np.oid)
    JOIN pg_constraint AS cp ON (cp.conrelid=clp.oid)
  ON (cf.confrelid = cp.conrelid)
WHERE
  -- no comments about system tables
  NOT is_system_schema(nf.nspname) 
  AND NOT is_system_schema(np.nspname)
  -- tables (redundant? constraints only on tables?)
  AND clp.relkind='r' AND clf.relkind='r' 
  -- foreign constraint and primary constraint
  AND cf.contype='f' AND cp.contype='p'
  -- that no not match
  AND NOT array_ceq(cp.conkey, cf.confkey)
ORDER BY FSchemaName ASC, FTableName ASC, SchemaName ASC, TableName ASC
;

COMMENT ON VIEW da_foreign_key_not_to_primary IS
'advice: foreign key does not reference primary key'
;

SELECT set_advice_info('da_foreign_key_not_to_primary', 2, 1,
'foreign key not to a primary key',
'not really a problem
- but why not to the primary key of the target?',
NULL
);

-- SELECT * FROM da_foreign_key_not_to_primary;

CREATE VIEW in_tables AS
SELECT DISTINCT n.nspname, t.relname, t.oid
FROM pg_namespace AS n JOIN pg_class AS t ON (t.relnamespace=n.oid)
WHERE NOT is_system_schema(n.nspname) AND t.relkind='r';

CREATE VIEW in_table_src_ri_constraints AS
SELECT * 
FROM in_tables AS t JOIN pg_constraint AS c ON (c.conrelid=t.oid)
WHERE c.contype='f';

CREATE VIEW in_table_dst_ri_constraints AS
SELECT * 
FROM in_tables AS t JOIN pg_constraint AS c ON (c.confrelid=t.oid)
WHERE c.contype='f';

--
-- isolated tables, that have no foreign keys, and that are not referenced...
--
CREATE VIEW da_isolated_table AS
SELECT nspname AS SchemaName, relname AS TableName
FROM in_tables
EXCEPT
SELECT nspname AS SchemaName, relname AS TableName
FROM in_table_src_ri_constraints
EXCEPT
SELECT nspname AS SchemaName, relname AS TableName
FROM in_table_dst_ri_constraints;

COMMENT ON VIEW da_isolated_table IS
'advice: tables which are isolated (not referenced and no reference)'
;

SELECT set_advice_info('da_isolated_table', 2, 3,
'isolated tables, not referenced and no reference',
'maybe missing FOREIGN KEY declarations?
- tables without reference on other tables
- and not referenced by other tables',
NULL);

-------------------------------------------------------------------------------
--
-- PERFORMANCE ADVICES (pa_*)
--
-- no usable primary key index for foreign key referencial integrity checks?
-- no usable index for foreign key on deletes?
-- never used indexes given enough statistics? costly?
--

-------------------------------------------------------------------------------
--
-- count SUMMARY of advices
--
CREATE VIEW summary_of_advices AS
SELECT
  -- optimizer/structural bug if c.relname instead of ac.name
  -- count_tuples get called too often, with infinite recursion in expansion?
  count_tuples(ac.name) AS Nb, -- costly!
  c.relname AS TableName,
  akind.description AS Kind,
  akind.kid AS nkind,
  asev.description AS Severity,
  asev.sid AS nsev,
  ac.title AS Title,
  ac.abstract AS Abstract,
  ac.description AS Description
FROM
  -- system information
  pg_class AS c
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
  -- advice information
  JOIN advice_classification AS ac ON (c.oid=ac.advice)
  JOIN advice_kind AS akind ON (ac.kid=akind.kid)
  JOIN advice_severity AS asev ON (ac.sid=asev.sid)
WHERE
  -- only advisor tables!
  n.nspname = 'xpg_advisor'
  -- always true:
  -- AND ac.name=c.relname 
ORDER BY nkind ASC, nsev DESC, Title ASC;

COMMENT ON VIEW summary_of_advices IS 
'count summary of all advices'
;

--
-- the short version...
--

CREATE VIEW short_summary_of_advices AS
SELECT Kind, Severity, Nb, Title
FROM summary_of_advices
WHERE Nb>0;

COMMENT ON VIEW short_summary_of_advices IS
'short summary of currently loaded design advices (da_* views)'
;

COMMENT ON COLUMN short_summary_of_advices.Kind IS
'the kind of advice it is, such as \'performance\'or \'design\''
;

COMMENT ON COLUMN short_summary_of_advices.Severity IS
'the severity of the advice, from \'info\' to \'error\''
;

COMMENT ON COLUMN short_summary_of_advices.Nb IS
'the number of offending objects found.'
;

COMMENT ON COLUMN short_summary_of_advices.Title IS
'short description of the advice.'
;

-- VACUUM FULL ANALYZE;

-- show design advice counts...
\echo '-- summary of advices'
SELECT * FROM short_summary_of_advices;