Diagnosing Slow Queries After Storage Upgrade

Problem Description

A platform engineering team recently migrated their primary PostgreSQL 16 reporting database from spinning-disk HDD storage to high-performance NVMe SSDs. They expected significant query speedups across the board, but after the migration, a number of analytical queries — particularly those joining an orders table with a users table and filtering on both created_at and status — are still running slower than expected. The team isn't sure whether the issue is configuration, statistics, or something else entirely.

The CTO has asked for a written investigation kit: a runnable SQL diagnostic script and a findings document that explains what to look for and what changes to make. This will be used both now (to debug the current problem) and as a template for future performance investigations on this cluster.

The cluster is self-hosted on Linux with NVMe storage. The team has full superuser access. PostgreSQL 16 is the current version.

Output Specification

Produce two files:

investigate.sql — A SQL script containing all the diagnostic queries needed to identify the root causes of slow query performance. Include comments explaining what each section is looking for. The script should be self-contained and runnable against a live PostgreSQL 16 cluster.

recommendations.md — A findings document with specific, actionable recommendations for configuration changes and schema improvements to address the most common root causes of slow queries in this scenario. Include the exact SQL or configuration changes needed to implement each recommendation.