Optimize PostgreSQL performance - EXPLAIN ANALYZE, indexing, query tuning
Optimize PostgreSQL queries using EXPLAIN ANALYZE, index design, and configuration tuning. Triggered when analyzing slow queries, creating indexes, or diagnosing performance issues with pg_stat_statements.
/plugin marketplace add pluginagentmarketplace/custom-plugin-postgresql/plugin install ultrathink@pluginagentmarketplace-postgresqlThis skill inherits all available tools. When active, it can use any tool Claude has access to.
assets/config.yamlassets/schema.jsonreferences/GUIDE.mdreferences/PATTERNS.mdscripts/validate.pyAtomic skill for query optimization
Production-ready patterns for EXPLAIN analysis, index design, and configuration tuning.
parameters:
operation:
type: string
required: true
enum: [analyze_query, create_index, tune_config, diagnose]
target_time_ms:
type: integer
default: 100
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';
| 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) |
| Metric | Healthy | Warning |
|---|---|---|
| Seq Scan rows | < 10K | > 100K |
| Buffer hit | > 99% | < 95% |
| Planning time | < 10ms | > 100ms |
-- Slow queries
SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
-- Unused indexes
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;
-- Table bloat
SELECT tablename, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 10000;
| Problem | Cause | Solution |
|---|---|---|
| Seq Scan | Missing index | Create index |
| High buffer reads | Cold cache | Increase shared_buffers |
| Wrong estimates | Stale stats | Run ANALYZE |
Skill("postgresql-performance")
Use when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.
Applies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.