When a query is slow, adding an index feels like the obvious fix. Sometimes it is. Often the planner was never going to use it, and the table now pays for it on every write.
Ask the planner first
EXPLAIN shows the plan PostgreSQL intends to use. EXPLAIN ANALYZE runs the query and shows what actually happened, including real row counts and timings for every step.
Look for the gap between estimate and reality
The most useful number on the plan is the difference between estimated rows and actual rows. When they differ by orders of magnitude, the planner chose its strategy for a different query than the one you ran. Fresh statistics often fix that without any new index.