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
This article was originally written by Caleb Meredith.
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.