Goal Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
with excl as (select 
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) as excl_schema,
c.relname as excl_table, 
c.oid as excl_table_oid,
o.conkey AS excl_col,
o.conexclop AS excl_op,
pg_get_constraintdef(o.oid) as const_def
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('x') ),
excl_unnest as (select conname, excl_schema, excl_table, excl_table_oid, excl_col, excl_col_num, ordin_col, const_def, excl_op
from excl, unnest(excl.excl_col) with ordinality as k(excl_col_num, ordin_col)),
excl_with_names as (select conname, excl_schema, excl_table,  array_agg(a_key.attname order by ordin_col) as excl_col, const_def, excl_op 
from excl_unnest k left join pg_attribute a_key on k.excl_col_num = a_key.attnum and k.excl_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, excl_schema, excl_table, const_def, excl_op),
excl_unnest_op as (select conname, excl_schema, excl_table, excl_col, const_def, excl_op, excl_op_num, ordin_op
from excl_with_names, unnest(excl_with_names.excl_op) with ordinality as k(excl_op_num, ordin_op)),
excl_with_names_op as (select conname, excl_schema, excl_table, excl_col, const_def,  array_agg(op.oprname order by ordin_op) as excl_op, array_agg(DISTINCT op.oprname order by oprname) as excl_op_distinct
from excl_unnest_op k inner join pg_operator op on k.excl_op_num=op.oid
group by conname, excl_schema, excl_table, excl_col, const_def)
select conname, excl_schema, excl_table, excl_col, excl_op, const_def
from excl_with_names_op
where cardinality(excl_op_distinct)=1
and excl_op_distinct[1]='='
and const_def~'USING gist'
order by excl_schema, excl_table, excl_col;

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Uniqueness

Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

NameDescription
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.