Postgres and Indexes on Foreign Keys and Primary Keys

Question

Does Postgres automatically put indexes on Foreign Keys and Primary Keys   How can I tell   Is there a command that will return all indexes on a table

User · Answer

This function  based on the work by Laurenz Albe at https   www cybertec-postgresql com en index-your-foreign-key   list all the foreign keys with missing indexes  The size of the table is shown  as for small tables the scanning performance could be superior to the index one  -- -- function     fkeys missing indexes -- purpose      list all foreing keys in the database without and index in the source table  -- author       Laurenz Albe -- see          https   www cybertec-postgresql com en index-your-foreign-key  -- create or replace function oftool fkey missing indexes     returns table     src table     regclass    fk columns    varchar    table size    varchar    fk constraint name    dst table     regclass   as    select   -- source table having ta foreign key declaration   tc conrelid  regclass as src table       -- ordered list of foreign key columns   string agg ta attname      order by tx n  as fk columns       -- source table size   pg catalog pg size pretty       pg catalog pg relation size tc conrelid      as table size       -- name of the foreign key constraint   tc conname as fk constraint       -- name of the target or destination table   tc confrelid  regclass as dst table    from pg catalog pg constraint tc  -- enumerated key column numbers per foreign key cross join lateral unnest tc conkey  with ordinality as tx attnum  n   -- name for each key column join pg catalog pg attribute ta on ta attnum   tx attnum and ta attrelid   tc conrelid  where not exists     -- is there ta matching index for the constraint    select 1 from pg catalog pg index i   where      i indrelid   tc conrelid and      -- the first index columns must be the same as the key columns  but order doesn t matter      i indkey  smallint    0 cardinality tc conkey -1    gt  tc conkey  and      tc contype    f    group by      tc conrelid       tc conname       tc confrelid   order by      pg catalog pg relation size tc conrelid  desc     language sql   test it this way  select   from oftool fkey missing indexes     you ll see a list like this  fk columns             table size fk constraint                      dst table          ---------------------- ---------- ---------------------------------- -----------------  id group               0 bytes    fk customer  group                 im group           id product             0 bytes    fk cart item  product              im store product   id tax                 0 bytes    fk order tax resume  tax           im tax             id product             0 bytes    fk order item  product             im store product   id tax                 0 bytes    fk invoice tax resume  tax         im tax             id product             0 bytes    fk invoice item  product           im store product   id article locale code 0 bytes    im article comment id article fkey im article locale

User · Answer

PostgreSQL automatically creates indexes on primary keys and unique constraints  but not on the referencing side of foreign key relationships    When Pg creates an implicit index it will emit a NOTICE-level message that you can see in psql and or the system logs  so you can see when it happens  Automatically created indexes are visible in  d output for a table  too   The documentation on unique indexes says      PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness  Thus  it is not necessary to create an index explicitly for primary key columns    and the documentation on constraints says      Since a DELETE of a row from the referenced table or an UPDATE of a   referenced column will require a scan of the referencing table for   rows matching the old value  it is often a good idea to index the   referencing columns  Because this is not always needed  and there are   many choices available on how to index  declaration of a foreign key   constraint does not automatically create an index on the referencing   columns    Therefore you have to create indexes on foreign-keys yourself if you want them   Note that if you use primary-foreign-keys  like 2 FK s as a PK in a M-to-N table  you will have an index on the PK and probably don t need to create any extra indexes   While it s usually a good idea to create an index on  or including  your referencing-side foreign key columns  it isn t required  Each index you add slows DML operations down slightly  so you pay a performance cost on every INSERT  UPDATE or DELETE  If the index is rarely used it may not be worth having

User · Answer

If you want to list the indexes of all the tables in your schema s  from your program  all the information is on hand in the catalog   select      n nspname  as  Schema       t relname  as  Table       c relname  as  Index  from           pg catalog pg class c      join pg catalog pg namespace n on n oid          c relnamespace      join pg catalog pg index i     on i indexrelid   c oid      join pg catalog pg class t     on i indrelid     t oid where         c relkind    i      and n nspname not in   pg catalog    pg toast       and pg catalog pg table is visible c oid  order by      n nspname      t relname      c relname   If you want to delve further  such as columns and ordering   you need to look at pg catalog pg index  Using psql -E  dbname  comes in handy for figuring out how to query the catalog

User · Answer

I love how this is explained in the article Cool performance features of EclipseLink 2 5     Indexing Foreign Keys      The first feature is auto indexing of foreign keys  Most people incorrectly assume that databases index   foreign keys by default  Well  they don t  Primary keys are auto   indexed  but foreign keys are not  This means any query based on the   foreign key will be doing full table scans  This is any OneToMany    ManyToMany or ElementCollection relationship  as well as many OneToOne   relationships  and most queries on any relationship involving joins or   object comparisons  This can be a major perform issue  and you should   always index your foreign keys fields

User · Answer

This query will list missing indexes on foreign keys  original source   Edit  Note that it will not check small tables  less then 9 MB  and some other cases  See final WHERE statement   -- check for FKs where there is no matching index -- on the referencing side -- or a bad index  WITH fk actions   code  action   AS       VALUES    a    error                r    restrict                c    cascade                n    set null                d    set default       fk list AS       SELECT pg constraint oid as fkoid  conrelid  confrelid as parentid          conname  relname  nspname          fk actions update action as update action          fk actions delete action as delete action          conkey as key cols     FROM pg constraint         JOIN pg class ON conrelid   pg class oid         JOIN pg namespace ON pg class relnamespace   pg namespace oid         JOIN fk actions AS fk actions update ON confupdtype   fk actions update code         JOIN fk actions AS fk actions delete ON confdeltype   fk actions delete code     WHERE contype    f     fk attributes AS       SELECT fkoid  conrelid  attname  attnum     FROM fk list         JOIN pg attribute             ON conrelid   attrelid             AND attnum   ANY  key cols       ORDER BY fkoid  attnum    fk cols list AS       SELECT fkoid  array agg attname  as cols list     FROM fk attributes     GROUP BY fkoid    index list AS       SELECT indexrelid as indexid          pg class relname as indexname          indrelid          indkey          indpred is not null as has predicate          pg get indexdef indexrelid  as indexdef     FROM pg index         JOIN pg class ON indexrelid   pg class oid     WHERE indisvalid    fk index match AS       SELECT fk list            indexid          indexname          indkey  int   as indexatts          has predicate          indexdef          array length key cols  1  as fk colcount          array length indkey 1  as index colcount          round pg relation size conrelid   1024 2   numeric  as table mb          cols list     FROM fk list         JOIN fk cols list USING  fkoid          LEFT OUTER JOIN index list             ON conrelid   indrelid             AND  indkey  int2    0  array length key cols 1  -1     gt  key cols     fk perfect match AS       SELECT fkoid     FROM fk index match     WHERE  index colcount - 1   lt   fk colcount         AND NOT has predicate         AND indexdef LIKE   USING btree      fk index check AS       SELECT  no index  as issue     1 as issue sort     FROM fk index match     WHERE indexid IS NULL     UNION ALL     SELECT  questionable index  as issue     2     FROM fk index match     WHERE indexid IS NOT NULL         AND fkoid NOT IN               SELECT fkoid             FROM fk perfect match     parent table stats AS       SELECT fkoid  tabstats relname as parent name           n tup ins   n tup upd   n tup del   n tup hot upd  as parent writes          round pg relation size parentid   1024 2   numeric  as parent mb     FROM pg stat user tables AS tabstats         JOIN fk list             ON relid   parentid    fk table stats AS       SELECT fkoid           n tup ins   n tup upd   n tup del   n tup hot upd  as writes          seq scan as table scans     FROM pg stat user tables AS tabstats         JOIN fk list             ON relid   conrelid   SELECT nspname as schema name      relname as table name      conname as fk name      issue      table mb      writes      table scans      parent name      parent mb      parent writes      cols list      indexdef FROM fk index check     JOIN parent table stats USING  fkoid      JOIN fk table stats USING  fkoid  WHERE table mb  gt  9     AND   writes  gt  1000         OR parent writes  gt  1000         OR parent mb  gt  10   ORDER BY issue sort  table mb DESC  table name  fk name

User · Answer

For a PRIMARY KEY  an index will be created with the following message   NOTICE  CREATE TABLE   PRIMARY KEY will create implicit index  index  for table  table     For a FOREIGN KEY  the constraint will not be created if there is no index on the referenced table   An index on referencing table is not required  though desired   and therefore will not be implicitly created

User · Answer

Yes - for primary keys  no - for foreign keys  more in the docs     d  lt table name gt    in  psql  shows a description of a table including all its indexes

[sql] Postgres and Indexes on Foreign Keys and Primary Keys

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to foreign-keys