Verified:GitHub

database-optimizer

Optimize slow PostgreSQL and MySQL queries with EXPLAIN ANALYZE validation.

2|Updated Apr 18, 2021

Search Tags:#postgresql #mysql #performance analysis #query tuning #database optimization #index design #execution plans

Ready to run

npx skills add https://github.com/rabbicse/go-projects --skill database-optimizer-rabbicse

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: database-optimizer
Source: https://github.com/rabbicse/go-projects/tree/main/projects/movie-ticket-booking/.claude/skills/database-optimizer
Command: npx skills add https://github.com/rabbicse/go-projects --skill database-optimizer-rabbicse

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill helps diagnose and fix slow database workloads by turning query plans, indexing choices, and configuration settings into measurable performance gains.

Core Features & Use Cases

Query Analysis: Inspect execution plans and identify sequential scans, bad joins, stale statistics, and buffer bottlenecks.
Index and Schema Tuning: Recommend covering, partial, expression, and multi-column indexes, plus partitioning and schema changes.
Operational Performance Work: Reduce lock contention, improve cache usage, and tune PostgreSQL or MySQL settings for real workloads.

Quick Start

Analyze the attached query or execution plan, identify the main bottleneck, and recommend the safest optimization with a before-and-after validation plan.

Frequently Asked Questions about database-optimizer

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

FAQPage Schema

Why does my PostgreSQL query execution plan show a sequential scan and how do I fix it?▼

A sequential scan in your execution plan indicates the query is scanning the entire table instead of using an index. You can fix this slow PostgreSQL query by analyzing the plan to identify the bottleneck and designing a covering or multi-column index to optimize the lookup.

How do I tune slow MySQL queries and improve database performance?▼

To tune slow MySQL queries, you need to inspect the execution plan to identify bad joins, stale statistics, or buffer bottlenecks. Optimization involves adjusting index design, improving the schema, and tuning database settings for real workloads.

What is the best way to reduce database lock contention during heavy workloads?▼

The best way to reduce database lock contention is to analyze execution plans and operational performance to identify conflicting queries. Improving cache usage, tuning settings, and optimizing schema and partitioning can significantly reduce lock conflicts.

Do I need baseline EXPLAIN ANALYZE measurements before starting database optimization?▼

Yes, baseline EXPLAIN ANALYZE measurements are required before starting database optimization. You must establish initial performance metrics, apply incremental validation during tuning, and measure before-and-after results to ensure safe query optimization.

When should I use partial or expression indexes for query tuning?▼

You should use partial or expression indexes for query tuning when specific queries filter on a subset of data or use computed columns. Analyzing the execution plan helps identify these scenarios to reduce index size and improve scan performance.

Can schema improvements and partitioning help resolve buffer bottlenecks?▼

Yes, schema improvements and partitioning can resolve buffer bottlenecks by reducing the amount of data scanned during query execution. Redesigning the schema and distributing data across partitions improves cache usage and minimizes memory pressure.