菜单
Optimize slow SQL queries with indexing, query rewriting, and execution plan analysis. Outputs before/after metrics and index strategies.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/kalilurrahman/kr-claudiator-skills-original-prompts --skill sql-optimization命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
基于 SOC 职业分类
正在显示 SKILL.md
| name | sql-optimization |
| description | Optimize slow SQL queries with indexing, query rewriting, and execution plan analysis. Outputs before/after metrics and index strategies. |
| argument-hint | ["slow query","database type","table sizes"] |
| allowed-tools | Read, Write, Bash |
Transform slow queries into fast ones through indexing, rewriting, and execution plan tuning. Not guesswork — systematic analysis using EXPLAIN, index strategies, and measurable improvements.
Database: PostgreSQL 15
Table Size: 5M rows
Before: 8.5 seconds
After: 45 milliseconds
Improvement: 189x faster
Composite index + query rewrite
Slow:
SELECT * FROM orders WHERE user_id = 123;
-- Execution time: 2500ms (scans all 5M rows)
EXPLAIN Output:
Seq Scan on orders (cost=0.00..125000.00 rows=100 width=200)
Filter: (user_id = 123)
Fix: Add Index
CREATE INDEX idx_orders_user_id ON orders(user_id);
Fast:
SELECT * FROM orders WHERE user_id = 123;
-- Execution time: 15ms
EXPLAIN Output:
Index Scan using idx_orders_user_id on orders (cost=0.42..12.50 rows=100)
Index Cond: (user_id = 123)
Improvement: 167x faster
Slow:
SELECT * FROM orders WHERE status = 'pending';
-- Returns 50 columns, 100KB per row
Fast:
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'pending';
-- Returns 4 columns, 200 bytes per row
Improvement: 500x less data transferred
Slow:
# Django ORM
orders = Order.objects.all() # 1 query
for order in orders:
print(order.user.name) # N queries (1 per order)
# Total: 1 + 1000 = 1001 queries
Fast:
orders = Order.objects.select_related('user').all() # 1 query with JOIN
for order in orders:
print(order.user.name) # No additional queries
# Total: 1 query
SQL Generated:
SELECT orders.*, users.*
FROM orders
INNER JOIN users ON orders.user_id = users.id;
Improvement: 1001 queries → 1 query
Slow:
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';
-- Index on status not used efficiently
Fast:
SELECT * FROM orders
WHERE status IN ('pending', 'processing');
-- Index scan
Even Better:
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE status = 'processing';
-- Uses index twice
Slow:
SELECT * FROM users
WHERE LOWER(email) = 'user@example.com';
-- Index on email not used (function applied)
Fix: Functional Index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
Or: Rewrite Query
SELECT * FROM users
WHERE email = 'user@example.com' -- Assume already lowercase
OR email = 'USER@EXAMPLE.COM';
Slow:
SELECT * FROM products
WHERE name LIKE '%phone%';
-- Cannot use index (leading wildcard)
Fix: Full-Text Search
-- PostgreSQL
CREATE INDEX idx_products_name_fts ON products
USING GIN (to_tsvector('english', name));
SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('phone');
Or: Trigram Index
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products
USING GIN (name gin_trgm_ops);
SELECT * FROM products
WHERE name ILIKE '%phone%';
-- Now uses GIN index
Query:
SELECT * FROM orders
WHERE user_id = 123
AND status = 'pending'
ORDER BY created_at DESC;
Optimal Index:
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);
Order Matters:
Wrong Index:
CREATE INDEX idx_orders_bad
ON orders(created_at, user_id, status);
-- Less efficient: created_at first is not selective
Query:
SELECT id, total, created_at
FROM orders
WHERE user_id = 123;
Index-Only Scan:
CREATE INDEX idx_orders_user_covering
ON orders(user_id)
INCLUDE (id, total, created_at);
Benefit: No need to access table (index contains all data)
Query:
SELECT * FROM orders WHERE status = 'pending';
Smaller Index:
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- Index only pending orders (10% of table)
Benefit: Smaller index, faster updates
Query:
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-15';
Index:
CREATE INDEX idx_orders_date
ON orders(DATE(created_at));
Slow:
SELECT users.name, COUNT(orders.id)
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;
Fast:
SELECT users.name,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;
Slow:
SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE country = 'US'
);
-- Subquery executed for each row
Fast:
SELECT orders.*
FROM orders
INNER JOIN users ON orders.user_id = users.id
WHERE users.country = 'US';
Slow:
SELECT users.*, orders.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
ORDER BY users.created_at DESC
LIMIT 10;
-- Joins all users then limits
Fast:
SELECT users.*, orders.*
FROM (
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10
) users
LEFT JOIN orders ON users.id = orders.user_id;
-- Limits first, then joins
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;
Output:
Index Scan using idx_orders_user_id on orders
(cost=0.42..12.50 rows=100 width=200)
(actual time=0.025..0.138 rows=95 loops=1)
Index Cond: (user_id = 123)
Planning Time: 0.123 ms
Execution Time: 0.165 ms
Key Metrics:
cost=0.42..12.50 — Estimated cost (startup..total)rows=100 — Estimated rowsactual time=0.025..0.138 — Actual time (ms)rows=95 — Actual rows returnedExecution Time: 0.165 ms — Total query timeRed Flags:
Seq Scan — Table scan (add index)rows=100 vs rows=10000 — Bad cardinality estimate (run ANALYZE)loops=1000 — Nested loop executed many times| Scan Type | Speed | When Used |
|---|---|---|
| Index Scan | Fast | Small result set with index |
| Index Only Scan | Fastest | Covering index |
| Bitmap Index Scan | Medium | Multiple index conditions |
| Seq Scan | Slow | No index or full table needed |
| Nested Loop | Varies | Small join, good for small tables |
| Hash Join | Medium | Large join |
| Merge Join | Fast | Sorted inputs |
-- Manual analyze
ANALYZE orders;
-- Verbose output
ANALYZE VERBOSE orders;
-- Auto-vacuum settings (postgresql.conf)
autovacuum = on
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
When: After bulk inserts, deletes, or updates
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS external_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;
Slow:
SELECT
DATE(created_at) as date,
COUNT(*) as orders,
SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at);
-- Scans 5M rows every query
Fast: Materialized View
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
DATE(created_at) as date,
COUNT(*) as orders,
SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at);
CREATE INDEX idx_daily_revenue_date ON daily_revenue(date);
-- Refresh daily
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
Query:
SELECT * FROM daily_revenue WHERE date = '2024-01-15';
-- 5ms instead of 2500ms
-- Range partition by date
CREATE TABLE orders (
id BIGSERIAL,
created_at TIMESTAMPTZ NOT NULL,
...
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Indexes on each partition
CREATE INDEX idx_orders_2024_01_user ON orders_2024_01(user_id);
CREATE INDEX idx_orders_2024_02_user ON orders_2024_02(user_id);
Benefit: Queries on recent data only scan recent partition
-- Enable extension
CREATE EXTENSION pg_stat_statements;
-- Find slowest queries
SELECT
query,
calls,
total_exec_time / 1000 AS total_seconds,
mean_exec_time / 1000 AS avg_seconds,
max_exec_time / 1000 AS max_seconds
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries > 1s
-- Analyze with pt-query-digest
pt-query-digest /var/log/mysql/slow.log
-- Before optimization
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- Note execution time: 2500ms
-- Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- After optimization
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- Note execution time: 15ms
-- Improvement: 167x faster