---
name: "postgres-pg-stat-statements-profiling"
description: "Identify slow queries, buffer cache misses, and lock contention using pg_stat_statements."
metadata:
  author: "PostgreSQL"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "find slow queries postgres"
  - "query pg_stat_statements"
  - "calculate buffer cache hit ratio"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

# PostgreSQL Query Optimization via pg_stat_statements

Profiles PostgreSQL database workloads: tracking total_exec_time, mean_exec_time, shared_blks_read vs shared_blks_hit ratios, query normalization hashes, and diagnosing missing indexes.

## When to Invoke
Invoke this skill whenever you need to:
- find slow queries postgres
- query pg_stat_statements
- calculate buffer cache hit ratio

## Execution SOP & Rules
1. Add pg_stat_statements to shared_preload_libraries in postgresql.conf and restart the instance.
2. Query pg_stat_statements ordered by total_exec_time DESC with normalized query hashes.
3. Calculate cache hit ratio: shared_blks_hit / (shared_blks_hit + shared_blks_read) to verify >99% efficiency.
4. Run EXPLAIN (ANALYZE, BUFFERS) on offending queries to identify sequence scans and optimize index coverage.

## Verification Checklist
- Run local linting, syntax validation, and type-checks before reporting completion.
- Ensure all created or modified files adhere to workspace boundaries and avoid uncommitted secrets.
- Verify tests or build commands exit cleanly with return code 0.