PostgreSQL Indexes

It’s important that your queries stay fast for your users, this section outlines some resources to help you optimize you queries with indexes.

• Heroku’s Efficient Use of PostgreSQL Indexes outlines how to best use indexes to optimize you queries. The entire article is a helpful read, but if nothing else read the last section Managing and Maintaining Indexes for a better understanding of how indexes work.

• The PostgreSQL documentation has a great article describing the relationship between Indexes and ORDER BY.

This article was originally written by Caleb Meredith.

Advice - Foreign Key Indexes

Many people don't realise that when you create a foreign key relation, PostgreSQL does NOT automatically create an index on the referencing column(s). That can mean that when you query based on that relation, which PostGraphile does a lot when traversing relationships, it can involve a full table scan which is very expensive.

Cameron Ellis has written a short article on finding missing indexes on foreign keys which utilises SQL similar to the following to automatically detect missing foreign key indexes:

WITH indexed_tables AS (
  select
      ns.nspname,
      t.relname as table_name,
      i.relname as index_name,
      array_to_string(array_agg(a.attname), ', ') as column_names,
      ix.indrelid,
      string_to_array(ix.indkey::text, ' ')::smallint[] as indkey
  FROM pg_class i
  JOIN pg_index ix ON i.OID = ix.indrelid
  JOIN pg_class t ON ix.indrelid = t.oid
  JOIN pg_namespace ns ON ns.oid = t.relnamespace
  JOIN pg_attribute a ON a.attrelid = t.oid
  where a.attnum = ANY(ix.indkey)
  and t.relkind = 'r'
  and nspname not in ('pg_catalog')
  group by
      ns.nspname,
      t.relname,
      i.relname,
      ix.indrelid,
      ix.indkey
  order by
      ns.nspname,
      t.relname,
      i.relname,
      ix.indrelid,
      ix.indkey
)
SELECT
  conrelid::regclass
  ,conname
  ,reltuples::bigint
FROM pg_constraint pgc
JOIN pg_class ON (conrelid = pg_class.oid)
WHERE contype = 'f'
AND NOT EXISTS(
  SELECT 1
  FROM indexed_tables
  WHERE indrelid = conrelid
  AND conkey = indkey
  OR (array_length(indkey, 1) > 1 AND indkey @> conkey)
)
ORDER BY reltuples DESC;

You should consider integrating something like this into your CI tests to ensure that all your foreign keys are indexed.