Can it help me find missing or unused indexes?

Yes, it includes specialized SQL snippets to query system catalogs for missing foreign key indexes and to identify redundant indexes that are slowing down write operations.

How does this skill help with slow PostgreSQL queries?

It provides structured methodologies for running EXPLAIN ANALYZE, interpreting metrics like shared hits vs. reads, and identifying whether a query is bottlenecked by sequential scans or disk-spilling sorts.

Does this skill help with database locking issues?

Yes, it provides scripts to monitor current locks, identify blocked PIDs, and detect long-running transactions that may be causing performance degradation.

What advanced index types are supported?

The skill provides guidance on B-tree, GIN (for JSONB and arrays), GiST (for ranges), BRIN (for large time-series data), and expression indexes for functional lookups.

Postgres Query Optimization

Name: Postgres Query Optimization
Author: francanete

byfrancanete

데이터베이스 관리

Optimizes PostgreSQL database performance by analyzing execution plans, identifying bottlenecks, and implementing advanced indexing strategies.

소개

This skill equips Claude with specialized expertise in PostgreSQL performance tuning, enabling it to diagnose slow queries and recommend precise architectural improvements. It provides structured guidance on interpreting EXPLAIN ANALYZE output, identifying inefficient scan types, and resolving systemic issues like the N+1 query pattern or stale statistics. Whether you are dealing with locking contention, memory configuration, or selecting the right index type (B-tree, GIN, BRIN), this skill offers the specific SQL patterns and best practices needed to ensure your database remains performant under load.

주요 기능

Identifies and resolves common performance antipatterns like N+1 queries and inefficient pagination
Recommends optimal configuration settings for work_mem, shared_buffers, and connection pooling
Interprets EXPLAIN and EXPLAIN ANALYZE output to pinpoint execution bottlenecks
Provides monitoring queries for pg_stat_statements, lock contention, and transaction health
0 GitHub stars
Guides the implementation of advanced index strategies including partial, covering, and expression indexes

사용 사례

Auditing database schemas for missing foreign key indexes or redundant, unused indexes
Optimizing data retrieval for large-scale applications using keyset pagination and covering indexes
Debugging and refactoring slow-running production SQL queries

소개

주요 기능

Identifies and resolves common performance antipatterns like N+1 queries and inefficient pagination
Recommends optimal configuration settings for work_mem, shared_buffers, and connection pooling
Interprets EXPLAIN and EXPLAIN ANALYZE output to pinpoint execution bottlenecks
Provides monitoring queries for pg_stat_statements, lock contention, and transaction health
0 GitHub stars
Guides the implementation of advanced index strategies including partial, covering, and expression indexes

사용 사례

Auditing database schemas for missing foreign key indexes or redundant, unused indexes
Optimizing data retrieval for large-scale applications using keyset pagination and covering indexes
Debugging and refactoring slow-running production SQL queries