The list of all the queries

Non-foreign key indexes that have been named as foreign key indexes

Query goal: Find indexes that are not on a foreign key column but the name of the index contains ixfk or idxfk.
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
Fixing suggestion: Rename index and remove the "fk" part from the name.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

with fk as (select 
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as fk_schema,
c.relname as fk_table, 
c.oid as fk_table_oid,
o.conkey AS fk_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype='f'),
fk_unnest as (select conname, fk_schema, fk_table, fk_table_oid, fk_col, fk_col_num, ordin
from fk, unnest(fk.fk_col) with ordinality as k(fk_col_num, ordin)),
fk_with_names as (select conname, fk_schema, fk_table, array_agg(a_key.attname order by ordin) as fk_col
from fk_unnest k inner join pg_attribute a_key on k.fk_col_num = a_key.attnum and k.fk_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, fk_schema, fk_table),
idx AS (SELECT 
n.nspname AS index_schema,
c.relname AS index_name,
c2.relname AS table_name,
i.indrelid AS index_table_oid,
indkey AS index_col
FROM pg_catalog.pg_index AS i INNER JOIN pg_catalog.pg_class AS c ON i.indexrelid=c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid=c2.oid
INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace=n.oid
WHERE nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
idx_unnest as (select index_name, index_schema, table_name, index_table_oid, index_col_num, ordin
from idx, unnest(idx.index_col) with ordinality as k(index_col_num, ordin)),
idx_with_names as (select index_name, index_schema, table_name, array_agg(a_idx.attname order by ordin) as idx_col
from idx_unnest k inner join pg_attribute a_idx on k.index_col_num = a_idx.attnum and k.index_table_oid = a_idx.attrelid and a_idx.attisdropped = false
group by index_name, index_schema, table_name)
SELECT index_name, index_schema, table_name, idx_col 
FROM idx_with_names
WHERE NOT EXISTS (SELECT 1
FROM fk_with_names 
WHERE idx_with_names.index_schema=fk_with_names.fk_schema 
AND idx_with_names.table_name=fk_with_names.fk_table 
AND (idx_with_names.idx_col[1]=fk_with_names.fk_col[1] OR (idx_with_names.idx_col@>fk_with_names.fk_col AND fk_with_names.fk_col@>idx_with_names.idx_col)))
AND index_name~*'(ixfk|idxfk)'
ORDER BY index_schema, table_name, index_name;

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
NamingQueries of this category provide information about the style of naming.

The list of all the queries