postgresql-query-optimization

What problem does it solve?

This Skill addresses slow-running PostgreSQL queries by providing a systematic approach to identify, diagnose, and resolve performance bottlenecks.

Core Features & Use Cases

• Identify Slow Queries: Uses pg_stat_statements and pg_stat_activity to pinpoint expensive queries.
• Analyze Execution Plans: Leverages EXPLAIN ANALYZE to understand query execution flow and identify costly operations.
• Index Optimization: Recommends and creates appropriate B-tree, GIN, GiST, or BRIN indexes, including partial and covering indexes.
• Statistics Tuning: Guides on updating table statistics and setting extended statistics for correlated columns.
• Configuration Adjustments: Provides recommendations for key PostgreSQL parameters like random_page_cost, work_mem, and effective_cache_size.
• Use Case: An application is experiencing slow response times. This Skill can analyze the database activity, identify the top offending queries, explain their execution plans, and suggest specific index creations or configuration changes to improve performance.

Quick Start

Use the postgresql-query-optimization skill to analyze and optimize the slow query SELECT * FROM orders WHERE customer_id = 123;.