skills.rest
marketiv-id
marketiv-idOFFICIAL
Explore all skills by this author →
GitHub

database-optimizer

Analyzes and optimizes PostgreSQL and MySQL query performance, indexes, and configuration.

Updated Feb 24, 2026
Search Tags:#indexing#postgresql#query performance#performance tuning#mysql#database optimization#explain analyze
One-click install
npx skills add https://github.com/marketiv-id/marketiv-web --skill database-optimizer-marketiv-id
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-optimizer
Source: https://github.com/marketiv-id/marketiv-web/tree/main/00_BACKEND/.agents/skills/database-optimizer
Command: npx skills add https://github.com/marketiv-id/marketiv-web --skill database-optimizer-marketiv-id

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Slow database queries, missing indexes, and misconfigured servers degrade application performance, and diagnosing root causes requires deep expertise in execution plans, statistics, and engine-specific tuning. ## Core Features & Use Cases - Query Analysis: Capture and interpret EXPLAIN ANALYZE output to identify sequential scans, bad join strategies, stale statistics, and low buffer cache hit rates. - Index Design: Create covering, partial, expression, GIN, and GiST indexes with correct column ordering, and detect redundant or unused indexes. - Configuration Tuning: Optimize PostgreSQL settings (shared_buffers, work_mem, autovacuum, WAL) and MySQL settings (InnoDB buffer pool, I/O capacity, slow query log). - Use Case: A dashboard endpoint takes 8 seconds to load. Use this Skill to capture the baseline execution plan, discover a missing composite index on the orders table, create it concurrently, and validate the query drops to under 100ms. ## Quick Start Analyze this slow PostgreSQL query, explain the execution plan bottlenecks, and recommend indexes and configuration changes with before-and-after validation queries.

Frequently Asked Questions about database-optimizer

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

FAQPage Schema
How do I find slow queries in PostgreSQL?▼

Enable the pg_stat_statements extension and query it ordered by mean_exec_time or total_exec_time to find the slowest queries. Then run EXPLAIN (ANALYZE, BUFFERS) on each candidate to see actual execution time, row estimates, and buffer usage.

How do I read a PostgreSQL EXPLAIN ANALYZE execution plan?▼

Look for sequential scans on large tables, nested loops with large row counts, and mismatches between estimated and actual rows. Low buffer hit ratios indicate cache problems, while external merge sorts suggest work_mem is too small.

What is a covering index and when should I use one?▼

A covering index includes all columns a query needs, allowing index-only scans without heap fetches. In PostgreSQL use the INCLUDE clause; in MySQL add the columns to the index. Use them for frequent queries filtering and projecting the same columns.

Does creating an index lock the table in PostgreSQL?▼

A standard CREATE INDEX blocks writes, but CREATE INDEX CONCURRENTLY avoids table locks and is safe for production. It takes longer to build and cannot run inside a transaction block.

Why is my query not using the index I created?▼

Common causes include stale table statistics, functions wrapping the indexed column, implicit type conversions, or the planner judging a sequential scan cheaper for small tables. Run ANALYZE on the table and match the query expression to the index definition.

When should I not add more indexes to a table?▼

Avoid indexes on write-heavy tables where each index amplifies write cost, and never create redundant indexes where a wider composite index already covers the same leftmost columns. Drop indexes showing zero scans in pg_stat_user_indexes after a monitoring period.

Related Skills

Explore More Software Engineering Skills→
grafanagrafana
213

database-observability

Diagnoses MySQL and PostgreSQL query performance via visual explain plans and metrics.

Official
Advanced
ArMaTeCArMaTeC

database-optimizer

Analyzes execution plans and optimizes query performance for PostgreSQL and MySQL databases.

Community
Advanced
InteligentsensingsolutionsInteligentsensingsolutions
1

postgres-best-practices

Optimize PostgreSQL performance with EXPLAIN ANALYZE, indexing, and connection pooling guidance.

Official
Advanced
wpankwpank
11

caching

Implement caching strategies with invalidation, eviction policies, and HTTP caching configurations.

Community
Intermediate