PostgreSQL Query Catalog

Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.

Find indexes that are used by the DBMS.

Find indexes that do not support a declarative constraint and that are perhaps badly named. Table names make the names more expressive and user-friendly.

Find indexes that may be redundant. In addition to identical indexes it also considers indexes that cover the same columns and have the same properties except uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes.

Find indexes that support a uniqueness constraint and have the same leading column.

Find indexes to a subset of table rows.

Find indexes with less common access methods. Gin indexes are, for instance, used to speed up PostgreSQL's built in full text search.

Find information about tables that are associated with triggers.

Find inheritance between base tables. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables.

Find instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed.

Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint.

Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments.

Find mandatory (NOT NULL) base table columns that name, column type, and field size refers to the possibility that these are used to register large textual values like comments, descriptions, and explanations.

Find materialized views with exactly the same subquery. There should not be multiple materialized views with the same subquery. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find membership relations between roles.

Find multicolumn CHECK constraints. Such constraints must be associated directly with a base table, i.e., these cannot be associated with a domain. Enforce as much data integrity as possible at the database level and prefer declarative constraints to a trigger.

Find multiple rules with the same definition (event, condition, action) on the same table. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find named input parameters that are not referenced in the routine body. All the parameters that are presented in the routine signature declaration must be used in its body. Otherwise these are dead code elements.

Find names (identifiers) of user-defined database objects that are 63 bytes long. This is the longest permitted length of identifiers if the default value of the NAMEDATALEN parameter has not been changed. PostgreSQL shortens too long identifiers automatically. Automatic code modification could break it somewhere.

Find names (identifiers) of user-defined database objects that are regular identifiers and contain the $ sign starting from the second position. "Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable." (PostgreSQL manual)