PostgreSQL: How to index all foreign keys?

Question

I am working with a large PostgreSQL database, and I am trying to tune it to get more performance.

Our queries and updates seem to be doing a lot of lookups using foreign keys.

What I would like is a relatively simple way to add Indexes to all of our foreign keys without having to go through every table (~140) and doing it manually.

In researching this, I've come to find that there is no way to have Postgres do this for you automatically (like MySQL does), but I would be happy to hear otherwise there, too.

Answer 1

EDIT: so, I wrote the query below and then thought... "hang on, Postgresql requires that foreign key targets have to have unique indices." So I guess I misunderstood what you meant? You can use the below query to check that the source of your foreign keys have indices by substituing "conrelid" for "confrelid" and "conkey" for "confkey" (yeah, yeah, no aliases in the query...)

Well, I guess it should be possible to go through the system catalogues... As usual, the best guide to the system catalogues is to use psql and do "\set ECHO_HIDDEN 1" and then see what SQL it generates for interesting "\d" commands. Here's the SQL used to find the foreign keys for a table ("\d tablename") :

-- $1 is the table OID, e.g. 'tablename'::regclass
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = $1 AND c.contype = 'f' ORDER BY 1;

Seems that pg_constraint has columns conkey and confkey that look like they could be the column numbers that the key is defined across. Probably confkey is the column numbers in the foreign table since it's only non-null for foreign keys. Also, took me a while to realise this is the SQL to show foreign keys referencing the given table. Which is what we want anyway.

So something this query shows the data beginning to take shape:

select confrelid, conname, column_index, attname
from pg_attribute
     join (select confrelid::regclass, conname, unnest(confkey) as column_index
           from pg_constraint
           where confrelid = 'ticket_status'::regclass) fkey
          on fkey.confrelid = pg_attribute.attrelid
             and fkey.column_index = pg_attribute.attnum

I'm going to be using 8.4 features like unnest... you might be able to get along without.

I ended up with:

select pg_index.indexrelid::regclass, 'create index ' || relname || '_' ||
         array_to_string(column_name_list, '_') || '_idx on ' || confrelid ||
         ' (' || array_to_string(column_name_list, ',') || ')'
from (select distinct
       confrelid,
       array_agg(attname) column_name_list,
       array_agg(attnum) as column_list
     from pg_attribute
          join (select confrelid::regclass,
                 conname,
                 unnest(confkey) as column_index
                from (select distinct
                        confrelid, conname, confkey
                      from pg_constraint
                        join pg_class on pg_class.oid = pg_constraint.confrelid
                        join pg_namespace on pg_namespace.oid = pg_class.relnamespace
                      where nspname !~ '^pg_' and nspname <> 'information_schema'
                      ) fkey
               ) fkey
               on fkey.confrelid = pg_attribute.attrelid
                  and fkey.column_index = pg_attribute.attnum
     group by confrelid, conname
     ) candidate_index
join pg_class on pg_class.oid = candidate_index.confrelid
left join pg_index on pg_index.indrelid = confrelid
                      and indkey::text = array_to_string(column_list, ' ')

OK, this monstrosity prints out the candidate index commands and tries to match them up with existing indices. So you can simply add "where indexrelid is null" on the end to get the commands to create indices that don't seem to exist.

This query doesn't deal with multi-column foreign keys very well; but imho if you're using those, you deserve trouble.

LATER EDIT: here's the query with the proposed edits up at the top put in. So this shows the commands to create indices that don't exist, on columns that are the source of a foreign key (not its target).

select pg_index.indexrelid::regclass, 'create index ' || relname || '_' ||
         array_to_string(column_name_list, '_') || '_idx on ' || conrelid ||
         ' (' || array_to_string(column_name_list, ',') || ')'
from (select distinct
       conrelid,
       array_agg(attname) column_name_list,
       array_agg(attnum) as column_list
     from pg_attribute
          join (select conrelid::regclass,
                 conname,
                 unnest(conkey) as column_index
                from (select distinct
                        conrelid, conname, conkey
                      from pg_constraint
                        join pg_class on pg_class.oid = pg_constraint.conrelid
                        join pg_namespace on pg_namespace.oid = pg_class.relnamespace
                      where nspname !~ '^pg_' and nspname <> 'information_schema'
                      ) fkey
               ) fkey
               on fkey.conrelid = pg_attribute.attrelid
                  and fkey.column_index = pg_attribute.attnum
     group by conrelid, conname
     ) candidate_index
join pg_class on pg_class.oid = candidate_index.conrelid
left join pg_index on pg_index.indrelid = conrelid
                      and indkey::text = array_to_string(column_list, ' ')
where indexrelid is null

My experience is that this isn't really all that useful. It suggests creating indices for things like reference codes that really don't need to be indexed.

Answer 2

The information is inside the catalog tables. But it seem it's not very straightforward to do want you need, specially if there are already some indexes created (and what about multicolumn indexes...)

If you don't have any indexed FK , you could do something quick and dirty, as

 SELECT 'CREATE INDEX ' || table_name || '_' || column_name || '_idx ON ('
   || table_name || '(' || column_name || ');'
from foreign_key_tables where schema = 'public';

You'd replace with the schema you are interested, dump that to a file, edit, check, pray and feed to psql. BEWARE this procedure does not detect already existant indexes.

Ah, foreign_key_tables is an informational view created as:

CREATE VIEW foreign_key_tables AS SELECT
    n.nspname AS schema,
    cl.relname AS table_name,
    a.attname AS column_name,
    ct.conname AS key_name,
    nf.nspname AS foreign_schema,
    clf.relname AS foreign_table_name,
    af.attname AS foreign_column_name,
    pg_get_constraintdef(ct.oid) AS create_sql
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind =
'r')
JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind
= 'r')
JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
af.attnum = ct.confkey[1]);