PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 80 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name ▲ Goal Type Data source Last update License
1 All clustered indexes Find all tables that rows have been physically sorted based on an index. General system catalog base tables only 2022-10-31 10:17 MIT License View
2 All covering indexes Find all covering indexes, which include data from additional columns in leaf blocks. General INFORMATION_SCHEMA+system catalog base tables 2020-12-23 11:54 MIT License View
3 All declaratively partitioned tables Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 15:13 MIT License View
4 All gin indexes Find indexes with less common access methods. Gin indexes are, for instance, used to speed up PostgreSQL's built in full text search. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
5 All non-unique indexes Find secondary indexes that have been created in the database. General INFORMATION_SCHEMA+system catalog base tables 2020-12-23 11:50 MIT License View
6 All partial indexes Find indexes to a subset of table rows. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
7 Base table FILLFACTOR is not 100 Find all base tables where FILLFACTOR is not 100, i.e., the default value. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
8 Base tables and materialized views without any index Find base tables and materialized views that do not have any index. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-05 19:39 MIT License View
9 Base tables that have more than five indexes Find base tables that have more than five indexes. Indexes can be used to increase the speed of queries (SELECT statements). However, the amount of indexes shouldn't be too large. Otherwise it may reduce the speed of operations that are used to modify data. Problem detection system catalog base tables only 2022-10-21 10:33 MIT License View
10 Base tables that have no uniqueness requirement for rows whatsoever Find base tables without any unique constraints and primary key as well as any unique index, whether it is created explicitly by a developer or automatically by the DBMS. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-10-21 10:16 MIT License View
11 Base tables where uniqueness is achieved by using only unique indexes Find base tables where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-10-21 01:54 MIT License View
12 Base tables, which statistics is probably not up to date Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
13 B-tree index fillfactor has been explicitly set to 90 Find B-tree indexes that fillfactor has been explicitly set to 90. In case of B-tree indexes the default is 90. Problem detection system catalog base tables only 2024-12-11 14:37 MIT License View
14 Case sensitive and case insensitive uniqueness of the same simple key Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
15 Columns with exact/floating numeric types have textual default values The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
16 Constraint-supporting UNIQUE indexes with the same leading column Find indexes that support a uniqueness constraint and have the same leading column. General system catalog base tables only 2023-11-26 16:01 MIT License View
17 Declaratively partitioned tables with one partition 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
18 Declaratively partitioned tables without partitions Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
19 Definition of a non-minimal superkey instead of a candidate key (based on unique indexes) Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction. Problem detection system catalog base tables only 2022-10-21 15:56 MIT License View
20 Derived table uses a function to get data from another table Find views that use a function to get data from another table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:36 MIT License View