skills.rest
7a336e6e
7a336e6eCOMMUNITY
Explore all skills by this author →
GitHub

Optimizing Performance

Diagnose PostgreSQL bottlenecks via EXPLAIN ANALYZE and optimize indexing.

1|2|Updated Feb 5, 2026
Search Tags:#query optimization#postgresql#performance tuning#sql optimization#database indexing#explain analyze
One-click install
npx skills add https://github.com/7a336e6e/skills --skill optimizing-performance-7a336e6e
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: Optimizing Performance
Source: https://github.com/7a336e6e/skills/tree/main/database/optimizing-performance
Command: npx skills add https://github.com/7a336e6e/skills --skill optimizing-performance-7a336e6e

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill addresses slow-running PostgreSQL queries and database performance issues, ensuring your application remains responsive and efficient.

Core Features & Use Cases

  • Performance Diagnosis: Utilizes EXPLAIN ANALYZE to pinpoint bottlenecks in query execution.
  • Index Optimization: Guides the creation of appropriate indexes (B-tree, GIN, GiST) based on query patterns.
  • Query Rewriting: Provides strategies for optimizing SQL query structure to improve performance.
  • Connection Pooling: Advises on configuring connection pools for efficient database resource management.
  • Monitoring: Recommends enabling slow query logs and using pg_stat_statements for ongoing performance tracking.
  • Use Case: An e-commerce application experiences slow product searches. This Skill can analyze the search query, identify a missing index on the product name, suggest its creation, and confirm the performance improvement.

Quick Start

Use the Optimizing Performance skill to diagnose and fix a slow query by analyzing its EXPLAIN ANALYZE output and adding the appropriate index.

Frequently Asked Questions about Optimizing Performance

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I diagnose slow PostgreSQL queries using EXPLAIN ANALYZE?▼

Diagnose slow PostgreSQL queries by running EXPLAIN ANALYZE to pinpoint execution bottlenecks. The tool analyzes the query plan to identify inefficient operations, enabling targeted performance tuning through strategic indexing and query rewriting.

What is the best way to choose between B-tree, GIN, and GiST indexes for PostgreSQL?▼

Choosing the right PostgreSQL index depends on your query patterns. B-tree handles standard equality and range searches, while GIN and GiST optimize full-text search and geometric data, ensuring efficient database indexing and faster response times.

How do I fix high database resource utilization in PostgreSQL?▼

Fix high PostgreSQL resource utilization by configuring connection pooling to manage database connections efficiently. Combine this with SQL optimization and enabling slow query logs to track and resolve resource-intensive operations.

Does PostgreSQL performance tuning require prior knowledge of execution plans?▼

PostgreSQL performance tuning requires understanding of SQL, execution plans, and indexing strategies. Analyzing EXPLAIN ANALYZE output to diagnose bottlenecks demands familiarity with database internals to implement effective query optimization.

How do I track ongoing PostgreSQL performance issues after initial tuning?▼

Track ongoing PostgreSQL performance issues by enabling slow query logs and using pg_stat_statements. This monitors query execution statistics over time, helping identify new bottlenecks and verify that indexing strategies remain effective.

Related Skills

Explore More Software Engineering Skills→
GDSDNGDSDN
3

db-explain

Analyze PostgreSQL query plans with EXPLAIN ANALYZE to identify bottlenecks.

Community
Advanced
voidrotvoidrot

qdrant-performance-optimization

Optimize Qdrant indexing strategies, query parameters, and hardware configurations.

Community
Advanced
a53alia53ali

database-optimization

Analyze EXPLAIN ANALYZE output to resolve slow PostgreSQL queries.

Community
Advanced
sorodriguezzsorodriguezz

Configure SQL Performance Options

Configures parallel query processing and runtime plan choice in InterSystems IRIS.

Community
Advanced