PostgreSQL Query Catalog

Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12.

Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table.

Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries.

Find how many base tables are referenced from a base table by using foreign keys.

Find how many base tables reference to a base table by using foreign keys. The more references there are the more impact the change of the table could have to the overall system.

Find how many tables with a certain range of the number of columns there are in the database.

Find identifiers that explicitly say that they carry no meaning. Such identifier is called "unnamed" or "anonymous".

Find immutable routines that contain SELECT statements from a database (including SELECT .. FOR UPDATE), INSERT, UPDATE, DELETE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Immutable routines should not have side effects and should also not ask data from a database because it could change over time. Immutable routines cannot lock tables or its specific rows.

Find implementations of state machines that use a state classifier table.

Find implementations of state machines that uses an enumeration type.

Find implementations of state machines that uses a set of columns with a timestamp type.

Find implementations of state machines that uses a set of one or more Boolean columns. These columns could have the type Boolean or could probably (based on the column name and non-participation in a foreign key) contain values that represent truth values.

Find implementations of the adjacency list design pattern, where the corresponding foreign key columns are optional but the foreign key constraint does not have ON DELETE SET NULL compensating action. Implement adjacency list correctly.

Find in case of base tables, materialized views, and views the number of columns based on the length of the column name.

Find in case of each base table that participates in a table inheritance hierarchy the path to the table from the top-level table. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables. Also make sure that the identifier of each child table in an inheritance hierarchy is a hyponym of the identifier of its parent table.

Find incorrectly implemented adjacency lists.

Find indexed textual columns where the indexing does not consider the possibility of pattern-based search. Such columns do not have an index where the used operator class makes the index suitable for use by queries involving pattern matching expressions.

Find indexes that are identical, i.e., have the same properties, including 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 are not on a foreign key column but the name of the index contains ixfk or idxfk.

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.