skills.rest
marquesfelip
marquesfelipCOMMUNITY
Explore all skills by this author →
GitHub

indexing-strategies

Design and optimize database indexes for PostgreSQL and MySQL workloads.

Updated Mar 29, 2026
Search Tags:#indexing#database#postgresql#optimization#mysql#explain-analyze#composite-index
One-click install
npx skills add https://github.com/marquesfelip/agents-and-skills --skill indexing-strategies
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: indexing-strategies
Source: https://github.com/marquesfelip/agents-and-skills/tree/main/skills/indexing-strategies
Command: npx skills add https://github.com/marquesfelip/agents-and-skills --skill indexing-strategies

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Database indexing and query optimization to reduce latency and improve read performance by designing effective index structures.

Core Features & Use Cases

  • Map access patterns to appropriate index designs (single-column, composite, partial, covering)
  • Select index types (B-tree, HASH, GIN, GiST, BRIN) suited to workload and data characteristics
  • Identify missing or redundant indexes, verify with EXPLAIN ANALYZE, and plan maintenance
  • Analyze and tune explain plans to validate index usage and performance improvements

Quick Start

Analyze a table's access patterns and implement a tailored index strategy, starting with a composite index where applicable.

Frequently Asked Questions about indexing-strategies

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

FAQPage Schema
How do I design a composite index to speed up slow PostgreSQL queries?▼

Design a composite index by mapping your table's access patterns to the query predicates to accelerate slow PostgreSQL queries. Apply the most selective columns first and validate the structure using EXPLAIN ANALYZE.

What is the best way to find missing or redundant indexes in a MySQL database?▼

Finding missing or redundant indexes in a MySQL database requires analyzing access patterns and verifying query execution plans with EXPLAIN ANALYZE. Identify unused indexes to reduce write overhead and plan ongoing maintenance.

When should I use specialized index types like GIN or BRIN instead of a standard B-tree?▼

Use specialized index types like GIN or BRIN instead of a standard B-tree when your workload and data characteristics require it, such as handling full-text search or large sequential physical ordering in PostgreSQL.

Can I use covering or partial indexes to improve read performance without slowing down writes?▼

Covering and partial indexes improve read performance by including only necessary columns or filtered rows, minimizing write overhead. Map these access patterns to your queries and validate the reduced latency using EXPLAIN ANALYZE.

Why does EXPLAIN ANALYZE show a sequential scan instead of using my database index?▼

EXPLAIN ANALYZE shows a sequential scan instead of using your database index when the planner determines a full table scan is cheaper. Analyze the query access patterns and ensure your composite index design aligns with the predicates.

Related Skills

Explore More Software Engineering Skills→
pythoninthegrassespythoninthegrasses
1

sql-optimization-patterns

Analyze EXPLAIN plans and apply indexing patterns to optimize SQL queries.

Official
Advanced
rnavarychrnavarych
14

role-database:query-optimization

Analyze execution plans and recommend index strategies across PostgreSQL, MySQL, MongoDB, and ClickHouse.

Community
Intermediate
plurigridplurigrid
60

database-design

Design database schemas and safe migration patterns for PostgreSQL, MySQL, and NoSQL.

Official
Intermediate
nekorush14nekorush14
4

rails-database-indexes

Design and implement database indexes for ActiveRecord models in Rails migrations.

Community
Advanced