Unindexed foreign keys

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m
m
Line 12: Line 12:
 
Tested in PostgreSQL 9.2 (might work with earlier versions).
 
Tested in PostgreSQL 9.2 (might work with earlier versions).
  
 
+
<source lang="sql">
 
  create or replace view unindexed_foreign_keys as
 
  create or replace view unindexed_foreign_keys as
 
  select  referencing_tbl,
 
  select  referencing_tbl,
Line 53: Line 53:
 
  order by    referencing_tbl_bytes desc, referenced_tbl_bytes desc,
 
  order by    referencing_tbl_bytes desc, referenced_tbl_bytes desc,
 
             referencing_tbl, referenced_tbl, referencing_column, referenced_column;
 
             referencing_tbl, referenced_tbl, referencing_column, referenced_column;
 +
</source>

Revision as of 14:44, 8 July 2013

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 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 n1.nspname
                     else 'public' end) || '.' || c1.relname  AS referencing_tbl,
                 a1.attname AS referencing_column,
                 t.conname AS existing_fk_on_referencing_tbl,
                 (case when n2.nspname IS NOT NULL then n2.nspname
                     else 'public' end) || '.' || c2.relname AS referenced_tbl,
                 a2.attname AS referenced_column,
                 pg_relation_size( ((case when n1.nspname IS NOT NULL then n1.nspname
                                         else 'public' end) || '.' || c1.relname) ) AS referencing_tbl_bytes,
                 pg_relation_size( ((case when n2.nspname IS NOT NULL then n2.nspname
                                         else 'public' end) || '.' || 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 n1.nspname
                         else 'public' end) || '.' || 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