PostgreSQL Query Catalog

Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.

Find declaratively partitioned tables that have exactly one partition. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. If there is only one partition, then it raises question as to why the additional complexity associated with partitioning is needed.

Find default values that return current timestamp with the maximum number of fractional seconds (6).

Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the

Find derived tables that subqueries invoke a PL/pgSQL function. Avoid context switch that is caused by the invocation of PL/pgSQL functions from the subqueries of derived tables.

Find derived tables that subquery contains terms "foo", "bar", "foobar", or "baz".

Find derived tables (views and materialized views) that contain both DISTINCT and GROUP BY. Make sure that the means for removing duplicate rows from the query result are not duplicated.

Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product.

Find derived tables (views and materialized views) that present data from one base table without adding columns, renaming columns, changing the order of columns, removing columns, or restricting rows. Perhaps something is missing from the subquery of the derived table.

Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.

Find derived tables (views and materialized views) that sort rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.

Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed.

Find derived tables (views, materialized views) that are based on more than five tables and that have more than 15 columns. This view might produce "a denormalized world view" where all the data is together in one table and applications make queries based on this single view to fulfill their specific tasks. Such view does not follow the separation of concerns principle.

Find derived tables where the rows are ordered, i.e., there is ORDER BY clause at the end of the view. Different users may want to see the rows in different order. Thus, the DBMS may have to do extra and unnecessary work by firstly sorting based on one set of rows and after that based on other set of rows. Use sorting if you know that all the users want the rows in the same order and cannot/will not change the order in the application.

Find derived tables with a search condition that is possible based on a name instead of a code.

Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input.

Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions.

Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task.

Find different tasks that are solved by using triggers, i.e., different triggers on the same table or different tables that do the same thing are considered to solve one task.

Find domain check constraint names that are used more than once (within the same schema or in different schemas). Different things should have different names. However, here different constraints have the same name. Also make sure that this is not a sign of duplication of domains.