postgresql-optimization
Optimize PostgreSQL Performance
PostgreSQL databases can suffer from slow queries and poor performance without proper optimization. This skill provides a systematic workflow for query tuning, indexing, and configuration management.
下載技能 ZIP
在 Claude 中上傳
前往 設定 → 功能 → 技能 → 上傳技能
開啟並開始使用
測試它
正在使用「postgresql-optimization」。 Optimize PostgreSQL queries on our orders table
預期結果:
- Performance Assessment Results:
- - Database version: PostgreSQL 15.3
- - Identified 3 slow queries with execution time > 500ms
- - Missing indexes detected on orders.customer_id and orders.status
- Recommended Actions:
- 1. Create B-tree index on orders(customer_id)
- 2. Create partial index for active orders
- 3. Add composite index for frequent query pattern
- Configuration Suggestions:
- - Increase work_mem to 64MB for sort operations
- - Set effective_cache_size to 6GB (75% of available memory)
正在使用「postgresql-optimization」。 Design indexing strategy for analytics queries
預期結果:
- Index Strategy for Analytics Dashboard:
- Primary Indexes:
- - B-tree on events(user_id, created_at) for user activity
- - B-tree on events(event_type, created_at) for event filtering
- Composite Indexes:
- - (product_id, category_id, price) for product searches
- Partial Indexes:
- - Index on orders WHERE status = 'pending' for processing queue
- Maintenance Plan:
- - Weekly ANALYZE after bulk loads
- - Monitor index usage with pg_stat_user_indexes
安全審計
安全Static analysis detected 36 potential issues (external_commands, weak cryptographic algorithm) but all are FALSE POSITIVES. The skill file contains only markdown documentation with skill references and prompts. No actual code execution, cryptographic operations, or security risks exist. The detected patterns are markdown code blocks and skill name references misinterpreted by the scanner.
品質評分
你能建構什麼
Resolve Slow Query Performance
Systematically analyze and optimize slow-running queries in production PostgreSQL databases using EXPLAIN ANALYZE and indexing strategies.
Design Indexing Strategy
Create comprehensive indexing plans including B-tree, composite, and partial indexes to improve query performance across multiple tables.
Tune PostgreSQL Configuration
Optimize PostgreSQL server settings like shared_buffers, work_mem, and effective_cache_size for better resource utilization.
試試這些提示
Use @database-optimizer to assess PostgreSQL performance on my database. Check version, review configuration, and identify slow queries.
Use @sql-optimization-patterns to analyze and optimize the following queries. Run EXPLAIN ANALYZE and suggest improvements: ```sql SELECT * FROM orders WHERE created_at > '2024-01-01'; ```
Use @database-design to design PostgreSQL indexing strategy for our e-commerce database with tables for users, orders, products, and reviews.
Use @postgres-best-practices to tune PostgreSQL configuration for a database server with 8GB RAM serving 500 concurrent users.
最佳實務
- Always run EXPLAIN ANALYZE before and after query optimizations to measure actual improvements
- Create indexes during low-traffic periods as index creation locks tables
- Monitor query performance over time using pg_stat_statements to identify regression
避免
- Creating too many indexes - each index adds write overhead and consumes storage
- Using sequential scans when indexes are available due to misconfiguration
- Ignoring table bloat and vacuum settings - leads to performance degradation over time