The list of all the queries

Unnecessary use of gist index type in case of an exclude constraint

Query goal: Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

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 where the query belongs to

Collection nameCollection description
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 where the query belongs to

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

The list of all the queries