Unindexed foreign keys

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m
(Added QUOTE_IDENT() around schemas, tables, and columns (suggestion from Scott Ribe).)
 
Line 13: Line 13:
  
 
<source lang="sql">
 
<source lang="sql">
create or replace view unindexed_foreign_keys as
+
create or replace view admin.unindexed_foreign_keys as
select  referencing_tbl,
+
select  referencing_tbl,
        referencing_column,
+
        referencing_column,
        existing_fk_on_referencing_tbl,
+
        existing_fk_on_referencing_tbl,
        referenced_tbl,
+
        referenced_tbl,
        referenced_column,
+
        referenced_column,
        pg_size_pretty(referencing_tbl_bytes) as referencing_tbl_size,
+
        pg_size_pretty(referencing_tbl_bytes) as referencing_tbl_size,
        pg_size_pretty(referenced_tbl_bytes) as referenced_tbl_size,
+
        pg_size_pretty(referenced_tbl_bytes) as referenced_tbl_size,
        suggestion
+
        suggestion
from    (
+
from    (
        select  (case when n1.nspname is not null then n1.nspname
+
        select  (case when n1.nspname is not null then quote_ident(n1.nspname) else 'public' end) || '.' || quote_ident(c1.relname) as referencing_tbl,
                    else 'public' end) || '.' || c1.relname  as referencing_tbl,
+
                quote_ident(a1.attname) as referencing_column,
                a1.attname as referencing_column,
+
                t.conname as existing_fk_on_referencing_tbl,
                t.conname as existing_fk_on_referencing_tbl,
+
                (case when n2.nspname is not null then quote_ident(n2.nspname) else 'public' end) || '.' || quote_ident(c2.relname) || '.' || quote_ident(a2.attname) as referenced_tbl,
                (case when n2.nspname is not null then n2.nspname
+
                quote_ident(a2.attname) as referenced_column,
                    else 'public' end) || '.' || c2.relname as referenced_tbl,
+
                pg_relation_size( ((case when n1.nspname is not null then quote_ident(n1.nspname) else 'public' end) || '.' || quote_ident(c1.relname)) ) as referencing_tbl_bytes,
                a2.attname as referenced_column,
+
                pg_relation_size( ((case when n2.nspname is not null then quote_ident(n2.nspname) else 'public' end) || '.' || quote_ident(c2.relname)) ) as referenced_tbl_bytes,
                pg_relation_size( ((case when n1.nspname is not null then n1.nspname
+
                'Create an index on column ' || quote_ident(a1.attname) || ' in table ' ||
                                        else 'public' end) || '.' || c1.relname) ) as referencing_tbl_bytes,
+
                    (case when n1.nspname is not null then quote_ident(n1.nspname) else 'public' end) || '.' || quote_ident(c1.relname) as suggestion
                pg_relation_size( ((case when n2.nspname is not null then n2.nspname
+
        from pg_constraint t
                                        else 'public' end) || '.' || c2.relname) ) as referenced_tbl_bytes,
+
        join pg_attribute  a1 on a1.attrelid = t.conrelid and a1.attnum = t.conkey[1]
                'Create an index on column "' || quote_ident(a1.attname) || '" in table "' ||
+
        join pg_class      c1 on c1.oid = t.conrelid
                    (case when n1.nspname is not null then n1.nspname
+
        join pg_namespace  n1 on n1.oid = c1.relnamespace
                        else 'public' end) || '.' || c1.relname || '"' as suggestion
+
        join pg_class      c2 on c2.oid = t.confrelid
        from pg_constraint t
+
        join pg_namespace  n2 on n2.oid = c2.relnamespace
        join pg_attribute  a1 on a1.attrelid = t.conrelid and a1.attnum = t.conkey[1]
+
        join pg_attribute  a2 on a2.attrelid = t.confrelid and a2.attnum = t.confkey[1]
        join pg_class      c1 on c1.oid = t.conrelid
+
        where t.contype = 'f'
        join pg_namespace  n1 on n1.oid = c1.relnamespace
+
        and not exists
        join pg_class      c2 on c2.oid = t.confrelid
+
            (select 1
        join pg_namespace  n2 on n2.oid = c2.relnamespace
+
            from pg_index i
        join pg_attribute  a2 on a2.attrelid = t.confrelid and a2.attnum = t.confkey[1]
+
            where i.indrelid = t.conrelid
        where t.contype = 'f'
+
            and i.indkey[0] = t.conkey[1])
        and not exists
+
        ) as y
            (select 1
+
order by referencing_tbl_bytes desc, referenced_tbl_bytes desc, referencing_tbl,
            from pg_index i
+
        referenced_tbl, referencing_column, referenced_column;
            where i.indrelid = t.conrelid
+
            and i.indkey[0] = t.conkey[1])
+
        ) as y
+
order by   referencing_tbl_bytes desc, referenced_tbl_bytes desc,
+
            referencing_tbl, referenced_tbl, referencing_column, referenced_column;
+
 
</source>
 
</source>

Latest revision as of 00:28, 10 July 2013

[edit] Unindexed Foreign Keys

Administrative Snippets

Unindexed foreign keys

Works with PostgreSQL

... – 9.2

Written in

SQL

Depends on

Nothing


I'm not sure of the original source of this query, but it might be from Michael Fuhr in the pgsql-performance mailing lists back in 2007.

It has been modified in several ways:

  • Size of referencing and referenced tables added,
  • Suggestion on index to add,
  • Searches only for single-column or compound indexes where the leading column is the referencing key.

Tested in PostgreSQL 9.2 (might work with earlier versions).

CREATE OR REPLACE VIEW admin.unindexed_foreign_keys AS
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    (
        SELECT  (case when n1.nspname IS NOT NULL then quote_ident(n1.nspname) else 'public' end) || '.' || quote_ident(c1.relname)  AS referencing_tbl,
                quote_ident(a1.attname) AS referencing_column,
                t.conname AS existing_fk_on_referencing_tbl,
                (case when n2.nspname IS NOT NULL then quote_ident(n2.nspname) else 'public' end) || '.' || quote_ident(c2.relname) || '.' || quote_ident(a2.attname) AS referenced_tbl,
                quote_ident(a2.attname) AS referenced_column,
                pg_relation_size( ((case when n1.nspname IS NOT NULL then quote_ident(n1.nspname) else 'public' end) || '.' || quote_ident(c1.relname)) ) AS referencing_tbl_bytes,
                pg_relation_size( ((case when n2.nspname IS NOT NULL then quote_ident(n2.nspname) else 'public' end) || '.' || quote_ident(c2.relname)) ) AS referenced_tbl_bytes,
                'Create an index on column ' || quote_ident(a1.attname) || ' in table ' ||
                    (case when n1.nspname IS NOT NULL then quote_ident(n1.nspname) else 'public' end) || '.' || quote_ident(c1.relname) AS suggestion
        FROM pg_constraint t
        JOIN pg_attribute  a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
        JOIN pg_class      c1 ON c1.oid = t.conrelid
        JOIN pg_namespace  n1 ON n1.oid = c1.relnamespace
        JOIN pg_class      c2 ON c2.oid = t.confrelid
        JOIN pg_namespace  n2 ON n2.oid = c2.relnamespace
        JOIN 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_index i
            WHERE i.indrelid = t.conrelid
            AND i.indkey[0] = t.conkey[1])
        ) AS y
ORDER BY referencing_tbl_bytes DESC, referenced_tbl_bytes DESC, referencing_tbl,
         referenced_tbl, referencing_column, referenced_column;
Personal tools