postgresql-performance

name	postgresql-performance
description	Optimize PostgreSQL performance - EXPLAIN ANALYZE, indexing, query tuning
version	3.0.0
sasmp_version	1.3.0
bonded_agent	03-postgresql-performance
bond_type	PRIMARY_BOND
category	database
difficulty	advanced
estimated_time	4h

PostgreSQL Performance Skill

Atomic skill for query optimization

Overview

Production-ready patterns for EXPLAIN analysis, index design, and configuration tuning.

Prerequisites

• PostgreSQL 16+
• pg_stat_statements extension

Parameters

parameters:
  operation:
    type: string
    required: true
    enum: [analyze_query, create_index, tune_config, diagnose]
  target_time_ms:
    type: integer
    default: 100

Quick Reference

EXPLAIN Commands

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = '[e-mail]';

Index Types

Use Case	Type	Example
Equality	B-tree	CREATE INDEX idx ON t(col)
JSONB	GIN	USING GIN(data jsonb_path_ops)
Time-series	BRIN	USING BRIN(created_at)

Key Metrics

Metric	Healthy	Warning
Seq Scan rows	< 10K	> 100K
Buffer hit	> 99%	< 95%
Planning time	< 10ms	> 100ms

Diagnostic Queries

 query, mean_exec_time  pg_stat_statements   mean_exec_time  LIMIT ;


 indexrelname, idx_scan  pg_stat_user_indexes  idx_scan  ;


 tablename, n_dead_tup  pg_stat_user_tables  n_dead_tup  ;