SQL Optimization Patterns
ynulihao/AgentSkillOS
Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries.
Advanced query tuning: join algorithms, skip index selection, EXPLAIN interpretation, ProfileEvents profiling, and optimizer settings.
Install Query Optimization
$ npx skills add chmonitor/chmonitor --skill query-optimization -a claude-codeProject install by default; add -g for ~/.claude/skills/.
$ gh skill install chmonitor/chmonitor query-optimization --agent claude-codeProject scope by default; add --scope user for a personal install. Needs GitHub CLI 2.90.0 or later (public preview).
$ git clone --depth 1 https://github.com/chmonitor/chmonitor.git skills-src && mkdir -p .claude/skills && cp -r skills-src/.agents/skills/query-optimization .claude/skills/query-optimization && rm -rf skills-srcUse ~/.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/
Install the "query-optimization" agent skill from https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization into .claude/skills/query-optimization/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "query-optimization", then confirm the skill loads.Claude Code copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.
$skill-installer install https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimizationType this inside Codex. $skill-installer <name> installs a curated skill from openai/skills. The installer writes to $CODEX_HOME/skills (default ~/.codex/skills). Restart Codex if the skill does not show up.
$ npx skills add chmonitor/chmonitor --skill query-optimization -a codexProject install goes to .agents/skills/; add -g for ~/.codex/skills/.
$ gh skill install chmonitor/chmonitor query-optimization --agent codexProject scope by default (.agents/skills/); add --scope user for a personal install.
$ git clone --depth 1 https://github.com/chmonitor/chmonitor.git skills-src && mkdir -p .agents/skills && cp -r skills-src/.agents/skills/query-optimization .agents/skills/query-optimization && rm -rf skills-srcUse ~/.agents/skills/ instead of .agents/skills for a personal install.
Codex skills documentation · loads skills from .agents/skills/
Install the "query-optimization" agent skill from https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization into .agents/skills/query-optimization/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "query-optimization", then confirm the skill loads.Codex copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.
$ npx skills add chmonitor/chmonitor --skill query-optimization -a cursorProject install goes to .agents/skills/; add -g for ~/.cursor/skills/.
$ gh skill install chmonitor/chmonitor query-optimization --agent cursorProject scope by default (.agents/skills/); add --scope user for a personal install.
$ git clone --depth 1 https://github.com/chmonitor/chmonitor.git skills-src && mkdir -p .cursor/skills && cp -r skills-src/.agents/skills/query-optimization .cursor/skills/query-optimization && rm -rf skills-srcUse ~/.cursor/skills/ instead of .cursor/skills for a personal install.
Cursor skills documentation · loads skills from .cursor/skills/, .agents/skills/, .claude/skills/, .codex/skills/
Install the "query-optimization" agent skill from https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization into .cursor/skills/query-optimization/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "query-optimization", then confirm the skill loads.Cursor copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.
$ gemini skills install https://github.com/chmonitor/chmonitor.git --path .agents/skills/query-optimization--scope user (default) or --scope workspace; --path is the subfolder of the repo that holds the skill; --consent skips the security confirmation prompt.
$ npx skills add chmonitor/chmonitor --skill query-optimization -a gemini-cliProject install goes to .agents/skills/; add -g for ~/.gemini/skills/.
$ gh skill install chmonitor/chmonitor query-optimization --agent gemini-cliProject scope by default (.agents/skills/); add --scope user for a personal install.
$ git clone --depth 1 https://github.com/chmonitor/chmonitor.git skills-src && mkdir -p .gemini/skills && cp -r skills-src/.agents/skills/query-optimization .gemini/skills/query-optimization && rm -rf skills-srcUse ~/.gemini/skills/ instead of .gemini/skills for a personal install, then run /skills reload.
Gemini CLI skills documentation · loads skills from .gemini/skills/, .agents/skills/
Install the "query-optimization" agent skill from https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization into .gemini/skills/query-optimization/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "query-optimization", then confirm the skill loads.Gemini CLI copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.
$ gh skill install chmonitor/chmonitor query-optimizationInstalls for Copilot at project scope by default; add --scope user for a personal install. Preview a skill first with gh skill preview. Needs GitHub CLI 2.90.0 or later (public preview).
$ npx skills add chmonitor/chmonitor --skill query-optimization -a github-copilotProject install goes to .agents/skills/; add -g for ~/.copilot/skills/.
$ git clone --depth 1 https://github.com/chmonitor/chmonitor.git skills-src && mkdir -p .github/skills && cp -r skills-src/.agents/skills/query-optimization .github/skills/query-optimization && rm -rf skills-srcUse ~/.copilot/skills/ instead of .github/skills for a personal install. Commit .github/skills so cloud agent and code review can use it.
GitHub Copilot skills documentation · loads skills from .github/skills/, .claude/skills/, .agents/skills/
Install the "query-optimization" agent skill from https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization into .github/skills/query-optimization/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "query-optimization", then confirm the skill loads.GitHub Copilot copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.
$ npx skills add chmonitor/chmonitor --skill query-optimization -a opencodeOpenCode documents no install command of its own. Project install goes to .agents/skills/; add -g for ~/.config/opencode/skills/.
$ gh skill install chmonitor/chmonitor query-optimization --agent opencodeProject scope by default (.agents/skills/); add --scope user for a personal install.
$ git clone --depth 1 https://github.com/chmonitor/chmonitor.git skills-src && mkdir -p .opencode/skills && cp -r skills-src/.agents/skills/query-optimization .opencode/skills/query-optimization && rm -rf skills-srcUse ~/.config/opencode/skills/ instead of .opencode/skills for a personal install.
OpenCode skills documentation · loads skills from .opencode/skills/, .claude/skills/, .agents/skills/
Install the "query-optimization" agent skill from https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization into .opencode/skills/query-optimization/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "query-optimization", then confirm the skill loads.OpenCode copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.
Facts
- Skill name
query-optimization- GitHub stars
- 298
- Token cost
- ~1.1k tokens
- SKILL.md length
- 528 words
- Files
- 1
- Skills in repo
- 53
- Repo updated
- First seen
- Licence
- GPL-3.0
At a glance
Advanced query tuning: join algorithms, skip index selection, EXPLAIN interpretation, ProfileEvents profiling, and optimizer settings.
- Works in 5 steps: List slow patterns — call… → EXPLAIN the worst pattern — take… → Get ranked recommendations — call… → …
- Tasks that involve Query optimization
- SKILL.md covers Autonomous Diagnose Loop ("why…, JOIN Strategies, EXPLAIN Analysis and Index Usage, plus 2 more sections
- Instructions only: no scripts, shell commands, URLs or credentials in SKILL.md
What it does
Query Optimization is an agent skill from chmonitor/chmonitor. Advanced query tuning: join algorithms, skip index selection, EXPLAIN interpretation, ProfileEvents profiling, and optimizer settings. Also the autonomous diagnose-loop for open-ended questions like why is my database slow?
Its SKILL.md is about 1.1k 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. The repository describes itself as: Open-source operational advisor for ClickHouse — real-time monitoring plus AI-driven index/partition/materialized-view recommendations. The licence is GPL-3.0.
When your agent uses it
- Tasks that involve Query optimization
Example prompts
- “/query-optimization”
Workflow steps
5 steps, taken from the first numbered list in SKILL.md.
- List slow patterns — call list_slow_query_patterns (not
- EXPLAIN the worst pattern — take normalized_query from step 1 and call
- Get ranked recommendations — call get_optimization_recommendations
- Propose, don't apply — present the top 1-2 recommendations with their
- Repeat for the next pattern only if the user wants more than one addressed,
What it can do on your machine
Read from SKILL.md and the folder at commit fc39ef0. 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.
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 loads about 1.1k tokens when it runs. Until then it costs about 61 tokens; SKILL.md has 528 words of instructions outside code blocks.
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 chmonitor/chmonitor at commit fc39ef0, republished under its GPL-3.0 licence (© chmonitor). 528 words, ~1,095 tokens.
Download SKILL.mdSave it as
.claude/skills/query-optimization/SKILL.md (or your agent's skills folder).- name
- query-optimization
- description
- Advanced query tuning: join algorithms, skip index selection, EXPLAIN interpretation, ProfileEvents profiling, and optimizer settings. Also the autonomous diagnose-loop for open-ended questions like why is my database slow?
Query Optimization
Autonomous Diagnose Loop ("why is my database slow?")
Use this loop when the question is open-ended — no specific query was named —
and the agent must find what's slow on its own, not just tune a query the user
already pasted (for that, load query-tuning-advisor instead).
Recommend-only, same as advisor-tools and control-tools: this loop never
executes DDL, never rewrites a query in place, and never runs an optimize/kill
action on its own. Every step below only reads system tables; the final output
is DDL/rewrite text for the user to review and run themselves.
- List slow patterns — call
list_slow_query_patterns(notget_slow_queries, which returns individual runs, not grouped patterns). Defaults to the last 24h, ranked by total duration. Identify the top 1-3 patterns bytotal_duration(overall cost) and separately note any pattern with a highp99_durationrelative top50_duration(tail-latency outlier) or a nonzeroerrorscount. - EXPLAIN the worst pattern — take
normalized_queryfrom step 1 and callexplain_querywithtype: 'indexes'first (are granules being pruned?), thentype: 'plan'if the indexes output doesn't explain the cost (e.g. a large JOIN build side or heavy aggregation step). See "EXPLAIN Analysis" below for what to look for. - Get ranked recommendations — call
get_optimization_recommendationswith the same query (sqlor aqueryIdfromsystem.query_log) to get ranked skip-index / projection / partition-key / PREWHERE suggestions with DDL text, rationale, risk, and effort. - Propose, don't apply — present the top 1-2 recommendations with their
DDL/rewrite text, expected impact, and risk. If a pattern also looks like a
good materialized-view/projection candidate (same GROUP BY shape recurring
often — see
callsin step 1), also callrecommend_materialized_viewand present that as an alternative. Explicitly tell the user these are recommendations to review and run themselves — mirror the control-tools confirmation posture, just for read-only advice instead of a destructive action. - Repeat for the next pattern only if the user wants more than one addressed, or if step 1 flagged both a total-duration offender and a separate tail-latency/error offender.
Show full SKILL.md (198 more words)Show less
JOIN Strategies
join_algorithmsetting:hash(default, in-memory),partial_merge(spills to disk for large right table),auto(lets ClickHouse decide)JOIN ... USINGavoids repeated column names vsONfor same-name columns- Filter both sides before joining to reduce intermediate data
GLOBAL JOINbroadcasts the right table to all shards for distributed queries
EXPLAIN Analysis
EXPLAIN PLAN— logical plan, shows projection/pushdown transformationsEXPLAIN PIPELINE— physical execution with parallelism info and port countsEXPLAIN INDEXES— which indexes fire, granules selected vs total- Look for: full table scans, missing index usage, excessive granule reads
Index Usage
- Skip index types with use-cases:
minmax— range queries on numeric/date columnsset(N)— equality on low-cardinality columns, stores N unique values per granulebloom_filter— equality on high-cardinality stringstokenbf_v1— tokenized text search (logs, URLs)
- Check effectiveness via
ProfileEvents['SelectedRows']vs result size
Query Profiling
ProfileEventsmap counters:SelectedRows,MergedRows,FileOpen,SeekCountnormalized_query_hashto group parameterized query variantssystem.query_logcolumns:query_duration_ms,memory_usage,read_bytes
Optimizer Settings
enable_optimizer = 1— activates ClickHouse's new cost-based query optimizer (v22.6+)max_threads— controls query parallelism; higher = faster but more memory; lower for concurrent workloadsprefer_localhost_replica = 1— avoids network round-trip by reading from local replica on distributed queriessystem.query_plan(v23.6+) — persisted query plans for analysis across runs
© chmonitor, GPL-3.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 .agents/skills/query-optimization of chmonitor/chmonitor.
Open the folder on GitHubat commit fc39ef0
Compare with similar skills
Query Optimization 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.
| Skill | Stars | Used in | Tokens | Auto-check | Licence | Repo updated |
|---|---|---|---|---|---|---|
| Query Optimization this skillchmonitor/chmonitor | 298 | — | ~1.1k | Automated safety check: Pass | GPL-3.0 | |
| SQL Optimization Patternsynulihao/AgentSkillOS | 617 | 10 repos | ~3.3k | Automated safety check: Pass | None | |
| Query Engine Designrevfactory/claude-code-harness | 120 | — | ~474 | Automated safety check: Pass | None | |
| Query Plan Snapshot CLIeclipse-rdf4j/rdf4j | 420 | — | ~1.5k | Automated safety check: Pass | BSD-3-Clause | |
| Wp Acf And Content Modelingjorgerosal/wordpress-skills | 100 | — | ~3.2k | Automated safety check: Pass | MIT | |
| Jpa Patternsaffaan-m/ECC | 274k | 5 repos | ~1.2k | Automated safety check: Pass | MIT |
Similar skills
Query Engine Design
revfactory/claude-code-harness
SQL query engine design and implementation guide. An agent skill from revfactory/claude-code-harness.
Query Plan Snapshot CLI
eclipse-rdf4j/rdf4j
Use QueryPlanSnapshotCli to capture and compare RDF4J query plans, then assess likely performance improvements/regressions from execution verification and semantic plan diffs.
Wp Acf And Content Modeling
jorgerosal/wordpress-skills
WordPress ACF and content modeling review. An agent skill from jorgerosal/wordpress-skills.
Jpa Patterns
affaan-m/ECC
JPA/Hibernate patterns for entity design, relationships, query optimization, transactions, auditing, indexing, pagination, and pooling in Spring Boot.
Postgres Patterns
ThibautBaissac/rails_ai_agents
PostgreSQL database patterns for query optimization, schema design, indexing, and security.
More from chmonitor/chmonitor
All 53 skills in this repoHyperframes Creative
chmonitor/chmonitor
Non-animation creative direction for HyperFrames videos. An agent skill from chmonitor/chmonitor.
Hyperframes Media
chmonitor/chmonitor
Audio and media assets for HyperFrames compositions, produced by one shared audio engine (scripts/audio.mjs) — multi-provider TTS (HeyGen / ElevenLabs / Kokoro local), background music + sound…
Remotion To Hyperframes
chmonitor/chmonitor
Port an existing Remotion (React) composition to HyperFrames HTML.
Music To Video
chmonitor/chmonitor
A skill your agent uses when the user has a music track (an audio file, or a video to pull audio from) and wants a beat-synced HyperFrames video, calm to hard-hitting.
Hyperframes Animation
chmonitor/chmonitor
All animation knowledge for HyperFrames — atomic motion rules, multi-phase scene blueprints, scene transitions, broader motion-design techniques, AND the seven runtime adapters (GSAP default, plus…
Faceless Explainer
chmonitor/chmonitor
turn arbitrary text — an article, notes, a topic, a brief — into a faceless explainer video, up to ~3 min (sweet spot 30-90s), where every visual is invented (typography, abstract graphics…
Related
Categories
Questions about Query Optimization
What does Query Optimization do?
Advanced query tuning: join algorithms, skip index selection, EXPLAIN interpretation, ProfileEvents profiling, and optimizer settings. Query Optimization is an agent skill from chmonitor/chmonitor. Advanced query tuning: join algorithms, skip index selection, EXPLAIN interpretation, ProfileEvents profiling, and optimizer settings.
When should I use Query Optimization?
Query Optimization fits situations like: tasks that involve Query optimization.
How do I install Query Optimization in Claude Code?
Run `npx skills add chmonitor/chmonitor --skill query-optimization -a claude-code`. Or copy the skill folder (.agents/skills/query-optimization in chmonitor/chmonitor) into .claude/skills/query-optimization in your project. Claude Code loads it when a task matches its description.
How do I install Query Optimization in Codex?
Run `npx skills add chmonitor/chmonitor --skill query-optimization -a codex`. Or copy the skill folder (.agents/skills/query-optimization in chmonitor/chmonitor) into .agents/skills/query-optimization in your project. Codex loads it when a task matches its description.
Can I use Query Optimization 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 chmonitor/chmonitor --skill query-optimization -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, .gemini/skills/query-optimization, .github/skills/query-optimization and .opencode/skills/query-optimization in your project.
What does Query Optimization need to run?
SKILL.md names no scripts, command-line tools or credentials: Query Optimization is instructions for the agent only.
Does Query Optimization 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 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 use?
Query Optimization is published under the GPL-3.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 use?
About 1.1k tokens (SKILL.md is roughly 4.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?
Skills that share tags, products or a category with Query Optimization: SQL Optimization Patterns (ynulihao/AgentSkillOS, 617 stars), Query Engine Design (revfactory/claude-code-harness, 120 stars), Query Plan Snapshot CLI (eclipse-rdf4j/rdf4j, 420 stars) and Wp Acf And Content Modeling (jorgerosal/wordpress-skills, 100 stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.
Who maintains Query Optimization?
chmonitor (a GitHub organization) maintains it in chmonitor/chmonitor, which has 298 GitHub stars. The repository holds 53 skills in this directory. The repository was last updated on October 5, 2026.
Source: chmonitor/chmonitor on GitHub. Facts on this page come from the repository at the commit we read; the author's words are quoted as theirs.