title: Indexing Foreign Keys in Postgresql
body: <p>Like many Postgres <code>n00bs</code> we have a lot of tables with foreign key constraints that are not indexed. I some cases this should not be a big performance hit - but this would be subject for further analysis.</p>
  
  <p>I have read the following article: <a href="https://www.cybertec-postgresql.com/en/index-your-foreign-key/" rel="nofollow noreferrer">https://www.cybertec-postgresql.com/en/index-your-foreign-key/</a></p>
  
  <p>And used the following query to find all foreign keys without an index:</p>
  
  <pre><code>SELECT c.conrelid::regclass AS "table",
         /* list of key column names in order */
         string_agg(a.attname, ',' ORDER BY x.n) AS columns,
         pg_catalog.pg_size_pretty(
            pg_catalog.pg_relation_size(c.conrelid)
         ) AS size,
         c.conname AS constraint,
         c.confrelid::regclass AS referenced_table
  FROM pg_catalog.pg_constraint c
     /* enumerated key column numbers per foreign key */
     CROSS JOIN LATERAL
        unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
     /* name for each key column */
     JOIN pg_catalog.pg_attribute a
        ON a.attnum = x.attnum
           AND a.attrelid = c.conrelid
  WHERE NOT EXISTS
          /* is there a matching index for the constraint? */
          (SELECT 1 FROM pg_catalog.pg_index i
           WHERE i.indrelid = c.conrelid
             /* the first index columns must be the same as the
                key columns, but order doesn't matter */
             AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
                 @&gt; c.conkey::int[])
    AND c.contype = 'f'
  GROUP BY c.conrelid, c.conname, c.confrelid
  ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;
  </code></pre>
  
  <p>This shows me for tables with composite unique constraints only "one" of the columns in the unique index:</p>
  
  <pre><code>\d topics_items;
  -----------------+---------+--------------+---------------+------------------------------
   topics_items_id | integer |              | not null      | generated always as identity
   topic_id        | integer |              | not null      |
   item_id         | integer |              | not null      |
  Index:
      "topics_items_pkey" PRIMARY KEY, btree (topics_items_id)
      "topic_id_item_id_unique" UNIQUE CONSTRAINT, btree (topic_id, item_id)
  Foreign Keys:
      "topics_items_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
      "topics_items_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES topics(topic_id) ON DELETE CASCADE
  </code></pre>
  
  <p>In this case the check query only finds the <code>item_id</code> and not the <code>topic_id</code> as an not indexed field.</p>
  
  <p>Is it fair to say, that this is just an issue of the query used, and I have to separately index both fields (topic_id and item_id) - or is there some black sorcery involved and only the <code>item_id</code> needs an index?</p>