[ltbarcly3]

Find missing indexes, return SQL to create them.

  SELECT CONCAT('CREATE INDEX ', relname, '_', conname, '_ix ON ',
                nspname, '.', relname, ' ',
                regexp_replace(
                      regexp_replace(pg_get_constraintdef(pg_constraint.oid, true),
                                 ' REFERENCES.*$','',''),
                             'FOREIGN KEY ','',''),
              ';') AS query
  FROM pg_constraint
  JOIN pg_class
      ON (conrelid = pg_class.oid)
  JOIN pg_namespace
      ON (relnamespace = pg_namespace.oid)
  WHERE contype = 'f' AND
      NOT EXISTS (
             SELECT 1
               FROM pg_index
              WHERE indrelid = conrelid AND
                    conkey::int[] @> indkey::int[] AND
                    indkey::int[] @> conkey::int[]);

[netcraft]

youve got an extra escape in there, probably an artifact of HN or something, its showing as *

[ltbarcly3]

thank you, fixed

[heuristic81]

This seems to have a few false positives. Multi-column indexes that have the first column as the foreign key work as well as a single column index. For more modern postgres, partial indexes with `WHERE column_name IS NOT NULL` on columns that can be null are also valid and more performant.

Here's what we use in CI to check for missing indexes:

    -- Unindexed FK -- Missing indexes - For CI

    WITH y AS (
    SELECT
    pg_catalog.format('%I', c1.relname)  AS referencing_tbl,
    pg_catalog.quote_ident(a1.attname) AS referencing_column,
    (SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement
    FROM pg_catalog.pg_constraint t
    JOIN pg_catalog.pg_attribute  a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
    JOIN pg_catalog.pg_class      c1 ON c1.oid = t.conrelid
    JOIN pg_catalog.pg_namespace  n1 ON n1.oid = c1.relnamespace
    JOIN pg_catalog.pg_class      c2 ON c2.oid = t.confrelid
    JOIN pg_catalog.pg_namespace  n2 ON n2.oid = c2.relnamespace
    JOIN pg_catalog.pg_attribute  a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1]
    WHERE t.contype = 'f'
    AND NOT EXISTS (
    SELECT 1
    FROM pg_catalog.pg_index i
    WHERE i.indrelid = t.conrelid
    AND i.indkey[0] = t.conkey[1]
    AND indpred IS NULL
    )
    )
    SELECT  referencing_tbl || '.' || referencing_column as column
    FROM y
    WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)'))
    ORDER BY 1;

Additionally I have this to specify the index creation commands (CONCURRENTLY is recommended for existing tables in production as it doesn't cause locking):

    -- Unindexed FK -- Missing indexes - Show Create Syntax

    WITH y AS (
        SELECT
            pg_catalog.format('%I.%I', n1.nspname, c1.relname)  AS referencing_tbl,
            pg_catalog.quote_ident(a1.attname) AS referencing_column,
            (SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement,
            t1.typname AS referencing_type,
            t.conname AS existing_fk_on_referencing_tbl,
            pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl,
            pg_catalog.quote_ident(a2.attname) AS referenced_column,
            t2.typname AS referenced_type,
            pg_relation_size( pg_catalog.format('%I.%I', n1.nspname, c1.relname) ) AS referencing_tbl_bytes,
            pg_relation_size( pg_catalog.format('%I.%I', n2.nspname, c2.relname) ) AS referenced_tbl_bytes,
            pg_catalog.format($$CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %s%I(%I)%s;$$, c1.relname || '_' || a1.attname || 'x' , CASE WHEN n1.nspname = 'public' THEN '' ELSE n1.nspname || '.' END, c1.relname, a1.attname, CASE WHEN a1.attnotnull THEN '' ELSE ' WHERE ' || a1.attname || ' IS NOT NULL' END) AS suggestion
        FROM pg_catalog.pg_constraint t
        JOIN pg_catalog.pg_attribute  a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
        JOIN pg_catalog.pg_type       t1 ON a1.atttypid = t1.oid
        JOIN pg_catalog.pg_class      c1 ON c1.oid = t.conrelid
        JOIN pg_catalog.pg_namespace  n1 ON n1.oid = c1.relnamespace
        JOIN pg_catalog.pg_class      c2 ON c2.oid = t.confrelid
        JOIN pg_catalog.pg_namespace  n2 ON n2.oid = c2.relnamespace
        JOIN pg_catalog.pg_attribute  a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1]
        JOIN pg_catalog.pg_type       t2 ON a2.atttypid = t2.oid
        WHERE t.contype = 'f'
        AND NOT EXISTS (
            SELECT 1
            FROM pg_catalog.pg_index i
            WHERE i.indrelid = t.conrelid
            AND i.indkey[0] = t.conkey[1]
            AND i.indpred IS NULL
        )
    )
    SELECT  referencing_tbl,
            referencing_column,
            existing_fk_on_referencing_tbl,
            referenced_tbl,
            referenced_column,
            pg_size_pretty(referencing_tbl_bytes) AS referencing_tbl_size,
            pg_size_pretty(referenced_tbl_bytes) AS referenced_tbl_size,
            suggestion
    FROM y
    WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)'))
    ORDER BY
        referencing_tbl_bytes DESC,
        referenced_tbl_bytes DESC,
        referencing_tbl,
        referenced_tbl,
        referencing_column,
        referenced_column;