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.