⚙️ Backend Engineering · Databases

Optimize the slow queries actually hurting you

Find top offenders from real traffic, read plans, add targeted indexes, verify improvements.

intermediate~35 minBackend EngineersAPI DevelopersPlatform Engineers

Steps

1Pull top-10 queries by total time from pg_stat_statements or slow log
2Read EXPLAIN (ANALYZE) for each; find seq scans on growing tables
3Index to match predicates AND sorts (composite order matters)
4Cover hot read paths with covering indexes where sensible
5Re-measure p95 after deploy; drop indexes that nobody uses
6Guard CI against new seq-scan regressions on core tables

Common Pitfalls

▲Indexing every column 'just in case', tanking write throughput
▲Functions wrapping indexed columns, silently disabling them

Commands

Install with skills CLI

$ npx skills add aniruddhaadak80/skills --skill databases-query-optimization

Install globally

$ npx skills add aniruddhaadak80/skills --skill databases-query-optimization -g

Tags

#performance#sql#indexes#backend-engineering#databases

Install this skill

$ npx skills add aniruddhaadak80/skills --skill databases-query-optimization -a opencode

SKILL.md

# Optimize the slow queries actually hurting you

> Find top offenders from real traffic, read plans, add targeted indexes, verify improvements.

## When to Use This Skill

Find top offenders from real traffic, read plans, add targeted indexes, verify improvements.

## Steps

1. Pull top-10 queries by total time from pg_stat_statements or slow log
2. Read EXPLAIN (ANALYZE) for each; find seq scans on growing tables
3. Index to match predicates AND sorts (composite order matters)
4. Cover hot read paths with covering indexes where sensible
5. Re-measure p95 after deploy; drop indexes that nobody uses
6. Guard CI against new seq-scan regressions on core tables

## Common Pitfalls

- Indexing every column 'just in case', tanking write throughput
- Functions wrapping indexed columns, silently disabling them

View source on GitHub ↗

Normalize enough to stay consistent, denormalize deliberately where reads demand it.

⚙️ Backend Engineering·~35m

Open →

Design schemas for change (MongoDB)foundation

Normalize enough to stay consistent, denormalize deliberately where reads demand it.

⚙️ Backend Engineering·~35m

Open →

Design schemas for change (MySQL)foundation

Normalize enough to stay consistent, denormalize deliberately where reads demand it.

⚙️ Backend Engineering·~35m

Open →