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

0•

데이터베이스 관리

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.

주요 기능

01Identifies and resolves common performance antipatterns like N+1 queries and inefficient pagination

02Recommends optimal configuration settings for work_mem, shared_buffers, and connection pooling

03Interprets EXPLAIN and EXPLAIN ANALYZE output to pinpoint execution bottlenecks

04Provides monitoring queries for pg_stat_statements, lock contention, and transaction health

050 GitHub stars

06Guides the implementation of advanced index strategies including partial, covering, and expression indexes

사용 사례

01Auditing database schemas for missing foreign key indexes or redundant, unused indexes

02Optimizing data retrieval for large-scale applications using keyset pagination and covering indexes

03Debugging and refactoring slow-running production SQL queries

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add francanete/fran-marketplace postgres-query-optimization

For use in Claude.ai and ChatGPT

Download Skill