Speedup of deletes on PostgreSQL

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;

Correct me if I'm wrong but, FKs are rarely created for existing columns.

You usually create the column and the FK in the same script. And usually starting with a NULL value for existing rows.

And if it's a new table then there's no rows anyway.

So the most common operations when creating FK's aren't expensive as far as I know.

You know what's expensive? Creating an index on a large table because you or your RDMS forgot to create the index when the FK was created and now JOINS are crawling to halt.

This seems like a very easy thing for any sort of middleware (or ORM) to do for you. Maybe even add typed where clauses that only exist for indexed columns.

You can store 255 values in one byte, and reserving two bytes is not what that did.

And even if I did, it still leaves the inability to actually rename enums without scanning the full table at least twice (which still doesn't seem possible in MariaDB, unless I missed something there).

If you potentially want great flexibility you shouldn't be using enums in the first place but int and a relational mapping to another table.

On Postgres, they are. You'll get an informational message saying the index was automatically generated.

But this is not normal behavior. I think Postgres is the only one that does this.