skills.rest
Chengxufeng1994
Chengxufeng1994COMMUNITY
Explore all skills by this author →
GitHub

postgresql-optimization

Analyzes PostgreSQL queries with EXPLAIN ANALYZE and implements indexing strategies.

Updated Oct 21, 2022
Search Tags:#indexing#database#postgresql#optimization#sql#performance tuning#jsonb
One-click install
npx skills add https://github.com/Chengxufeng1994/dotfiles --skill postgresql-optimization-chengxufeng1994
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-optimization
Source: https://github.com/Chengxufeng1994/dotfiles/tree/main/claude/skills/postgresql-optimization
Command: npx skills add https://github.com/Chengxufeng1994/dotfiles --skill postgresql-optimization-chengxufeng1994

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) components.

What problem does it solve?

This Skill helps developers and database administrators optimize PostgreSQL databases for better performance, efficiency, and scalability by leveraging advanced features and best practices.

Core Features & Use Cases

  • Query Optimization: Analyze and improve slow-running SQL queries using EXPLAIN ANALYZE.
  • Indexing Strategies: Implement effective indexing for faster data retrieval.
  • Advanced Data Types: Utilize JSONB, arrays, ranges, and geometric types efficiently.
  • Performance Tuning: Configure memory, connections, and extensions for optimal database operation.
  • Use Case: Optimize a complex reporting query that is currently taking minutes to run, reducing its execution time to seconds by applying appropriate indexing and query rewriting.

Quick Start

Use the postgresql-optimization skill to analyze the performance of the following SQL query: SELECT * FROM users WHERE last_login < NOW() - INTERVAL '1 year';

Frequently Asked Questions about postgresql-optimization

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

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

PostgreSQL indexing strategies involve implementing B-tree, GIN, or GiST indexes to accelerate data retrieval. This Skill guides you in creating effective indexes for complex reporting queries, reducing execution time from minutes to seconds.

What's the best way to use JSONB and arrays for better PostgreSQL performance?▼

For better PostgreSQL performance with JSONB and arrays, utilize efficient data manipulation capabilities specific to these advanced types. This Skill demonstrates how to handle JSONB, arrays, and ranges to enhance query execution speed and scalability.

How do I configure PostgreSQL memory and connections for performance tuning?▼

PostgreSQL performance tuning requires configuring memory, connections, and extensions for optimal database operation. This Skill provides strategies to reduce resource consumption and improve overall execution speed by adjusting server configurations.

Why does my complex reporting query take so long in PostgreSQL?▼

Complex reporting queries take long in PostgreSQL due to missing indexes or inefficient execution paths. This Skill analyzes slow-running SQL using EXPLAIN ANALYZE and applies query rewriting and indexing to reduce execution time to seconds.

Do I need PostgreSQL extensions for advanced database performance tuning?▼

Advanced PostgreSQL performance tuning often requires configuring extensions for optimal database operation. This Skill helps you leverage unique PostgreSQL features, including specific extensions, to enhance data manipulation and reduce resource consumption.

Related Skills

Explore More Software Engineering Skills→
Camilo8902Camilo8902

sql-query-optimizer

Analyze SQL execution plans and recommend indexes and rewrites to optimize queries.

Community
Advanced
alexander-danilenkoalexander-danilenko
14

sql-pro

Optimize slow SQL queries and schemas with EXPLAIN plan analysis and index recommendations.

Community
Advanced
ariesanhthuariesanhthu

database-expert

Analyze EXPLAIN plans and optimize queries for PostgreSQL, MySQL, MongoDB, and SQLite.

Community
Advanced
htlin222htlin222
77

sql

Develop and optimize SQL queries, design schemas, and advise on indexing strategies.

Community
Advanced