Skip to main content
Version: Next

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.

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. By default, PostGraphile will not add this "reverse lookup" field to the GraphQL schema unless there is an index on it for this reason (though you can force it with the +select behavior on the foreign key constraint).

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.

Our pgRITA.com tool will also help you spot this kind of issue and more; it has a free usage tier with some essentials and is also included in certain sponsorship tiers.