Find the missing index behind a slow list query

-- 1. Find the slow ones (enable the pg_stat_statements extension first)
select calls, round(mean_exec_time::numeric, 1) as avg_ms, query
from pg_stat_statements
order by mean_exec_time desc
limit 10;

-- 2. Look at the plan before you add anything
explain (analyze, buffers)
select * from posts where user_id = '...' order by created_at desc limit 20;
-- "Seq Scan" on a large table means no usable index.

-- 3. Index the filter column first, then the sort column
create index concurrently if not exists posts_user_created_idx
  on posts (user_id, created_at desc);

-- 4. Re-run step 2. It should now say "Index Scan".