New: 97,000+ skills indexed from GitHub repos with 100+ stars · every skill safety-checkedBrowse top skillsTop skills
Esc to closeBrowse all skills
Submit a skillBrowse skills
Submit a skillBrowse skills

Agent skill

Query Optimization Catalog

by revfactory in revfactory/harness-100

SQL query optimization catalog. An agent skill from revfactory/harness-100.

Apache-2.0Auto-check passedDatabases

Install Query Optimization Catalog

skills CLI
$ npx skills add revfactory/harness-100 --skill query-optimization-catalog -a claude-code

Project install by default; add -g for ~/.claude/skills/.

GitHub CLI
$ gh skill install revfactory/harness-100 query-optimization-catalog --agent claude-code

Project scope by default; add --scope user for a personal install. Needs GitHub CLI 2.90.0 or later (public preview).

Manual copy
$ git clone --depth 1 https://github.com/revfactory/harness-100.git skills-src && mkdir -p .claude/skills && cp -r skills-src/en/19-database-architect/.claude/skills/query-optimization-catalog .claude/skills/query-optimization-catalog && rm -rf skills-src

Use ~/.claude/skills/ instead of .claude/skills for a personal install. The folder must contain SKILL.md.

Claude Code skills documentation · loads skills from .claude/skills/

Facts

Skill name
query-optimization-catalog
GitHub stars
1.3k
Token cost
~1.9k tokens
SKILL.md length
475 words
Files
1
Skills in repo
464
Repo updated
First seen
Licence
Apache-2.0

At a glance

SQL query optimization catalog. An agent skill from revfactory/harness-100.

  • Works in 7 steps: N+1 Problem → SELECT * → Function Invalidating Index → …
  • Performing DB performance analysis involving query optimization
  • SKILL.md covers Target Agent, Index Strategies, Slow Query Anti-Patterns &… and EXPLAIN Analysis Guide…, plus 2 more sections
  • Instructions only: no scripts, shell commands, URLs or credentials in SKILL.md

What it does

Query Optimization Catalog is an agent skill from revfactory/harness-100. SQL query optimization catalog. An extension skill for performance-analyst that provides index strategies (B-Tree/Hash/GIN/GiST), execution plan analysis, N+1 problem resolution, partitioning strategies, and per-pattern optimization techniques for slow queries. Use when performing DB performance analysis involving 'query optimization', 'index design', 'execution plans', 'N+1 problems', 'partitioning', 'slow queries', etc. Note: data modeling and security configuration are outside the scope of this skill.

Its SKILL.md is about 1.9k tokens, which your agent loads only when the skill is triggered. It is a single SKILL.md file with no bundled scripts.

It sits in Databases, covering Query optimization. It works with SQL and PostgreSQL. The licence is Apache-2.0.

When your agent uses it

  • Performing DB performance analysis involving query optimization
  • Execution plans

Example prompts

  • “query optimization”
  • “index design”
  • “execution plans”
  • “/query-optimization-catalog”

Workflow steps

7 steps, taken from the step headings in SKILL.md.

  1. N+1 Problem
  2. SELECT *
  3. Function Invalidating Index
  4. OR Condition Invalidating Index
  5. Subquery vs JOIN
  6. OFFSET Pagination
  7. Large IN Clause

What it can do on your machine

Read from SKILL.md and the folder at commit 8e8d35c. It shows what the files ask for, not the result of running them.

  • Tool permissions

    Pre-approves nothing: there is no allowed-tools line, so your agent's usual permission prompts apply.

    From allowed-tools in the SKILL.md frontmatter.

  • Runs code

    No scripts in the folder and no shell commands in SKILL.md (its code samples are sql).

    From the folder's file list and the shell code blocks in SKILL.md.

  • Network

    No URLs in SKILL.md.

    From URLs in SKILL.md, links to its own repository left out.

  • Credentials

    Names no API keys, tokens, secrets or passwords.

    From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

Context cost

Query Optimization Catalog loads about 1.9k tokens when it runs. Until then it costs about 134 tokens; SKILL.md has 475 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it
~134
When it runs · the whole SKILL.md, loaded when a task matches
~1.9k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Auto-check passed

The automated check found no risky patterns in SKILL.md.

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); files beside SKILL.md are not scanned.

SKILL.md

The full file from revfactory/harness-100 at commit 8e8d35c, republished under its Apache-2.0 licence (© revfactory). 475 words, ~1,860 tokens.

Download SKILL.mdSave it as .claude/skills/query-optimization-catalog/SKILL.md (or your agent's skills folder).
name
query-optimization-catalog
description
SQL query optimization catalog. An extension skill for performance-analyst that provides index strategies (B-Tree/Hash/GIN/GiST), execution plan analysis, N+1 problem resolution, partitioning strategies, and per-pattern optimization techniques for slow queries. Use when performing DB performance analysis involving 'query optimization', 'index design', 'execution plans', 'N+1 problems', 'partitioning', 'slow queries', etc. Note: data modeling and security configuration are outside the scope of this skill.

Query Optimization Catalog — SQL Query Optimization Catalog

A reference of index strategies, execution plan analysis, and query anti-pattern resolution used by the performance-analyst agent during performance optimization.

Target Agent

performance-analyst — Directly applies the optimization techniques from this skill to performance analysis and index design.

Index Strategies

Index Type Selection Guide
Index TypeSuitable QueriesDBMSCharacteristics
B-Tree=, <, >, BETWEEN, ORDER BYAllGeneral purpose, default
Hash= equality onlyPostgreSQL, MySQLNo range searches
GINArrays, JSONB, full-text searchPostgreSQLMulti-value indexing
GiSTSpatial (geometry), rangesPostgreSQLPostGIS, range types
BRINTime-series, naturally sorted dataPostgreSQLVery small size
FulltextFull-text searchMySQL, PostgreSQLReplaces LIKE '%word%'
Composite Index Design Principles
Leftmost Prefix Rule
sql
INDEX idx_abc ON table(a, b, c)

-- Usable:
WHERE a = 1                          -- O (a only)
WHERE a = 1 AND b = 2               -- O (a, b)
WHERE a = 1 AND b = 2 AND c = 3     -- O (all columns)
WHERE a = 1 AND c = 3               -- Partial (a only, c skipped)

-- Not usable:
WHERE b = 2                          -- X (a missing)
WHERE c = 3                          -- X (a, b missing)
Column Order Decision Rules
  1. WHERE equality (=) condition columns first
  2. Sort (ORDER BY) columns next
  3. Range (<, >, BETWEEN) columns last
  4. Higher cardinality first (but rules 1-3 take priority)
Covering Indexes

Return query results using only the index (no table access needed).

sql
-- Covering index
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

-- This query responds from the index only (Index Only Scan)
SELECT status, created_at FROM orders WHERE user_id = 123;
Index Add/Remove Decision Guide
ScenarioAdd Index?Reason
Column frequently used in WHERE clauseYesImproves search speed
FK column in JOIN ON clauseYesJOIN performance
Column frequently used in ORDER BYYesAvoids sorting
Very low cardinality (boolean, etc.)NoMinimal benefit
Frequently UPDATEd columnCarefullyWrite performance degradation
Small table (under 10K rows)NoFull scan is faster

Slow Query Anti-Patterns & Solutions

1. N+1 Problem
sql
-- Anti-pattern: Individual queries in a loop
SELECT * FROM users;
-- For each user:
SELECT * FROM orders WHERE user_id = ?;  -- Repeated N times!

-- Solution: JOIN or IN
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Or batch loading
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...);
2. SELECT *
sql
-- Anti-pattern
SELECT * FROM products WHERE category = 'electronics';

-- Solution: Only needed columns
SELECT id, name, price FROM products WHERE category = 'electronics';
-- Enables covering index usage
3. Function Invalidating Index
sql
-- Anti-pattern: Applying function to indexed column
WHERE YEAR(created_at) = 2025

-- Solution: Convert to range condition
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
4. OR Condition Invalidating Index
sql
-- Anti-pattern
WHERE status = 'active' OR category = 'books'

-- Solution: UNION ALL
SELECT * FROM products WHERE status = 'active'
UNION ALL
SELECT * FROM products WHERE category = 'books' AND status != 'active'
5. Subquery vs JOIN
sql
-- Anti-pattern: Correlated subquery
SELECT * FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);

-- Solution: JOIN + aggregate
SELECT o.* FROM orders o
JOIN (SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id) a
ON o.user_id = a.user_id
WHERE o.total > a.avg_total;
6. OFFSET Pagination
sql
-- Anti-pattern: Deep OFFSET
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- Solution: Cursor-based (Keyset)
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;
7. Large IN Clause
sql
-- Anti-pattern: Thousands of IDs
WHERE id IN (1, 2, 3, ..., 10000)

-- Solution: Temporary table or JOIN
-- PostgreSQL: VALUES or ANY(ARRAY[...])
-- General: Batch processing (500 at a time)

EXPLAIN Analysis Guide (PostgreSQL)

Reading Execution Plans
sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
Key Node Types
NodeMeaningPerformance
Seq ScanFull table scanSlow (large data)
Index ScanIndex + table accessModerate
Index Only ScanResponse from index onlyFast
Bitmap Index ScanBitmap-based index scanModerate
Nested LoopNested loop joinSuitable for small datasets
Hash JoinHash table joinLarge equality joins
Merge JoinSort-merge joinLarge sorted data
SortSort operationWatch memory/disk
Show full SKILL.md (159 more words)Show less
Warning Signs
  • Seq Scan on a large table -> Index needed
  • Sort with external merge Disk -> Insufficient work_mem
  • actual rows >> estimated rows -> Stale statistics (ANALYZE needed)
  • Nested Loop with a large table -> Encourage Hash Join

Partitioning Strategy

Signs Partitioning Is Needed
  • Table size > hundreds of millions of rows
  • Time-series data (logs, events, metrics)
  • Periodic deletion/archiving of old data
  • Most queries target specific time ranges
Partitioning Types
TypeSplit CriterionSuitable ForExample
RangeValue rangeTime-seriesMonthly/yearly partitions
ListValue listCategoriesBy region, by status
HashHash valueEven distributionuser_id % N
Range Partitioning Example (PostgreSQL)
sql
CREATE TABLE events (
  id BIGINT,
  event_time TIMESTAMPTZ,
  data JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_q1 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

Caching Strategy

LevelToolSuitable DataTTL
Query cacheRedis/MemcachedFrequently read query results30s-5min
ORM cachePrisma/TypeORM cacheEntity-level1-5min
Aggregation cacheMaterialized ViewStatistics/dashboards1hr+
CDN cacheCloudFront/CloudFlareStatic API responses5-60min
Cache Invalidation Strategies
  • TTL-based: Auto-refresh after expiration time
  • Event-based: Immediate deletion on data change
  • Write-Through: Update cache on writes
  • Cache-Aside: On read miss, query DB and store in cache

© revfactory, Apache-2.0. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

Just SKILL.md in en/19-database-architect/.claude/skills/query-optimization-catalog of revfactory/harness-100.

Open the folder on GitHubat commit 8e8d35c

Compare with similar skills

Query Optimization Catalog next to the 5 skills that share the most tags, products or categories with it. Stars are the repository's; “used in” counts other GitHub owners with a copy.

Query Optimization Catalog compared with similar skills
SkillStarsUsed inTokensAuto-checkLicenceRepo updated
Query Optimization Catalog this skillrevfactory/harness-1001.3k—~1.9kAutomated safety check: PassApache-2.0
PostgreSQL Documentation Reference2025Emma/vibe-coding-cn23k1 repos~19kAutomated safety check: PassMIT
Paro Optimizerzunor/paro105—~957Automated safety check: PassApache-2.0
DB SculptorEliasOulkadi/shokunin114—~3.1kAutomated safety check: NotesMIT
Postgresql Best Practices CloudbaseTencentCloudBase/CloudBase-AI-Toolkit1.1k1 repos~1.3kAutomated safety check: PassMIT
Database OptimizerJeffallan/claude-skills12k—~1.6kAutomated safety check: PassMIT

Similar skills

  • PostgreSQL Documentation Reference

    2025Emma/vibe-coding-cn

    PostgreSQL database documentation - SQL queries, database design, administration, performance tuning, and advanced features. Use when working with PostgreSQL…

    23k GitHub starsUsed in 1 repo~19k tokens
    DatabasesAuto-check passed

  • Paro Optimizer

    zunor/paro

    Design, refactor and diagnose Paro's staged optimizer, using EXPLAIN COMPILE for planning and EXPLAIN ANALYZE for execution.

    105 GitHub stars~957 tokensUpdated 11 days ago
    DatabasesAuto-check passed

  • DB Sculptor

    EliasOulkadi/shokunin

    Design database schemas with Prisma/Drizzle, PostgreSQL index strategy (B-tree, GIN, GiST, BRIN, Hash), query optimization (EXPLAIN ANALYZE), migration safety (expand/contract, zero-downtime), and…

    114 GitHub stars~3.1k tokensUpdated 2 days ago
    DatabasesAuto-check: notes

  • Postgresql Best Practices Cloudbase

    TencentCloudBase/CloudBase-AI-Toolkit

    CloudBase PostgreSQL access-pattern and slow-query quality guidance.

    1.1k GitHub starsUsed in 1 repo~1.3k tokens
    DatabasesAuto-check passed

  • Database Optimizer

    Jeffallan/claude-skills

    Tunes PostgreSQL and MySQL performance by analyzing slow queries and execution plans, designing indexes, rewriting queries and adjusting configuration, one validated change at a time.

    12k GitHub stars~1.6k tokensUpdated 4 days ago
    DatabasesAuto-check passed

  • SQL Pro

    Jeffallan/claude-skills

    Optimizes SQL queries and designs schemas using CTEs, window functions, covering indexes and EXPLAIN ANALYZE, with notes on dialect differences between major databases.

    12k GitHub stars~1.3k tokensUpdated 4 days ago
    DatabasesAuto-check passed

More from revfactory/harness-100

All 464 skills in this repo

  • Anti Bot Analyzer

    revfactory/harness-100

    A skill for analyzing website anti-bot defense mechanisms and developing legitimate evasion strategies.

    1.3k GitHub stars~1.1k tokensUpdated 6 mo ago
    Auto-check passed

  • API Error Design Patterns

    revfactory/harness-100

    Reference for designing how an API reports failures: structured error codes, response shapes, client-friendly messages, an error catalog and retry or fallback advice.

    1.3k GitHub stars~1.6k tokensUpdated 6 mo ago
    Auto-check passed

  • API Security Checklist

    revfactory/harness-100

    Walks a backend-dev agent through OWASP API Top 10 checks, authentication and authorization patterns, and defense code during API design.

    1.3k GitHub stars~1.7k tokensUpdated 6 mo ago
    Auto-check passed

  • Arg Parser Generator

    revfactory/harness-100

    Methodology for systematically designing and generating CLI tool argument parser structures.

    1.3k GitHub stars~1.2k tokensUpdated 6 mo ago
    Auto-check passed

  • Audience Segmentation

    revfactory/harness-100

    Audience segmentation skill used by the analyst and curator agents.

    1.3k GitHub stars~1.3k tokensUpdated 6 mo ago
    Auto-check passed

  • Audio Storytelling

    revfactory/harness-100

    Audio storytelling skill used by the podcast scriptwriter and show note editor.

    1.3k GitHub stars~1.6k tokensUpdated 6 mo ago
    Auto-check passed

Works with

Categories

Questions about Query Optimization Catalog

What does Query Optimization Catalog do?

SQL query optimization catalog. An agent skill from revfactory/harness-100. Query Optimization Catalog is an agent skill from revfactory/harness-100. SQL query optimization catalog.

When should I use Query Optimization Catalog?

Query Optimization Catalog fits situations like: performing DB performance analysis involving query optimization; execution plans.

How do I install Query Optimization Catalog in Claude Code?

Run `npx skills add revfactory/harness-100 --skill query-optimization-catalog -a claude-code`. Or copy the skill folder (en/19-database-architect/.claude/skills/query-optimization-catalog in revfactory/harness-100) into .claude/skills/query-optimization-catalog in your project. Claude Code loads it when a task matches its description.

How do I install Query Optimization Catalog in Codex?

Run `npx skills add revfactory/harness-100 --skill query-optimization-catalog -a codex`. Or copy the skill folder (en/19-database-architect/.claude/skills/query-optimization-catalog in revfactory/harness-100) into .agents/skills/query-optimization-catalog in your project. Codex loads it when a task matches its description.

Can I use Query Optimization Catalog in Cursor, Gemini CLI or GitHub Copilot?

Cursor, Gemini CLI, GitHub Copilot and OpenCode also load SKILL.md folders. With the skills CLI, run `npx skills add revfactory/harness-100 --skill query-optimization-catalog -a cursor` (or -a gemini-cli, github-copilot or opencode for the others). To copy it by hand, put the folder in .cursor/skills/query-optimization-catalog, .gemini/skills/query-optimization-catalog, .github/skills/query-optimization-catalog and .opencode/skills/query-optimization-catalog in your project.

What does Query Optimization Catalog need to run?

SKILL.md names no scripts, command-line tools or credentials: Query Optimization Catalog is instructions for the agent only.

Does Query Optimization Catalog access the network?

SKILL.md contains no URLs. Any network use would come from the scripts or tools the agent runs. This is read from the text; nothing was executed.

Is Query Optimization Catalog safe to install?

Our automated static check of SKILL.md found no risky patterns, such as piping downloads into a shell, reading credential files or hidden Unicode. It is not a guarantee. Review the folder before installing.

What licence does Query Optimization Catalog use?

Query Optimization Catalog is published under the Apache-2.0 licence (the repository's licence). It allows redistribution, so the full SKILL.md is shown on this page.

How many tokens does Query Optimization Catalog use?

About 1.9k tokens (SKILL.md is roughly 7.4k characters). Agents keep only the skill's name and description in context until a task matches; then they load SKILL.md in full.

What are the alternatives to Query Optimization Catalog?

Skills that share tags, products or a category with Query Optimization Catalog: PostgreSQL Documentation Reference (2025Emma/vibe-coding-cn, 23k stars), Paro Optimizer (zunor/paro, 105 stars), DB Sculptor (EliasOulkadi/shokunin, 114 stars) and Postgresql Best Practices Cloudbase (TencentCloudBase/CloudBase-AI-Toolkit, 1.1k stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.

Who maintains Query Optimization Catalog?

revfactory (a GitHub user) maintains it in revfactory/harness-100, which has 1,290 GitHub stars. The repository holds 464 skills in this directory. The repository was last updated on March 22, 2026.

Source: revfactory/harness-100 on GitHub. Facts on this page come from the repository at the commit we read; the author's words are quoted as theirs.