PostgreSQL: debugging a slow query and optimizing it

Complete method for diagnosing a slow PostgreSQL query: EXPLAIN ANALYZE, missing indexes (B-tree, GIN, BRIN), stale statistics, pg_stat_statements. From diagnosis to production fix, no fluff.