Skip to content
Skills Library
Data & analytics · Analysis

query-optimizer

/query-optimizer

Optimize SQL queries for performance with indexing strategies, query rewriting, and execution plan analysis. Use when queries are slow, optimizing database performance, or analyzing query execution.

Licence
MIT
Last repository change
27.07.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.07.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Optimize SQL queries for performance with indexing strategies, query rewriting, and execution plan analysis. Use when queries are slow, optimizing database performance, or analyzing query execution.

Original author description

Optimize SQL queries for performance with indexing strategies, query rewriting, and execution plan analysis. Use when queries are slow, optimizing database performance, or analyzing query execution.

How it works

  1. Quick Start
  2. Instructions
  3. Common Patterns
  4. Performance Monitoring
  5. Best Practices
  6. Troubleshooting
Original SKILL.md headings · 431 lines, 1,221 words
SKILL.md preview
---
name: query-optimizer
description: Optimize SQL queries for performance with indexing strategies, query rewriting, and execution plan analysis. Use when queries are slow, optimizing database performance, or analyzing query execution.
---

# Query Optimizer

Optimize SQL queries for better performance through indexing, rewriting, and analysis.

## Quick Start

Use EXPLAIN to analyze queries, add indexes on WHERE/JOIN columns, avoid SELECT *, limit results.

## Instructions

### Query Analysis with EXPLAIN

**Basic EXPLAIN:**
```sql
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
```

**EXPLAIN ANALYZE (actual execution):**
```sql
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
```

**Key metrics to check:**
- Seq Scan (bad) vs Index Scan (good)
- Rows: Estimated vs actual
- Cost: Lower is better
- Execution time

### Common Performance Issues

**1. Missing Indexes**

Problem:
```sql
-- Seq Scan on users (cost=0.00..1234.56)
SELECT * FROM users WHERE email = 'user@example.com';
```

Solution:
```sql
CREATE INDEX idx_users_email ON users(email);
-- Now: Index Scan using idx_users_email
```

**2. SELECT ***

Problem:
```sql
SELECT * FROM posts;  -- Fetches all columns
```

Solution:
```sql
SELECT id, title, created_at FROM posts;  -- Only needed columns
```

**3. N+1 Queries**

Problem:
```sql
-- Fetches posts
SELECT * FROM posts;
-- Then for each post:
SELECT * FROM users WHERE id = ?;
```

Solution:
```sql
-- Single query with JOIN
SELECT posts.*, users.name 
FROM posts 
JOIN users ON posts.user_id = users.id;
```

**4. No LIMIT**

Problem:
```sql
SELECT * FROM posts ORDER BY created_at DESC;  -- Returns all rows
```

Solution:
```sql
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
```

### Indexing Strategies

**Single column index:**
```sql
CREATE INDEX idx_users_email ON users(email);
```

**Composite index (order matters):**
```sql
-- For: WHERE user_id = ? AND created_at > ?
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);
```

**Covering index (includes all needed columns):**
```sql
-- For: SELECT id, title FROM posts WHERE user_id = ?
CREATE INDEX idx_posts_user_id_title ON posts(user_id) INCLUDE (title);
```

**Partial index (filtered):**
```sql
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
```

**Index on expressions:**
```sql
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- For: WHERE LOWER(email) = 'user@example.com'
```

### Query Rewriting

**Use EXISTS instead of IN for large sets:**
```sql
-- Slow
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

-- Faster
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.user_id = u.id
);
```

**Use JOIN instead of subquery:**
```sql
-- Slow
SELECT * FROM posts WHERE user_id IN (
    SELECT id FROM users WHERE is_active = true
);

-- Faster
SELECT p.* FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.is_active = true;
```

**Avoid functions on indexed columns:**
```sql
-- Bad: Can't use index
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Good: Can use index
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';
```

**Use UNION ALL instead of UNION:**
```sql
-- Slow: Removes duplicates
SELECT id FROM posts UNION SELECT id FROM drafts;

-- Fast: No duplicate removal
SELECT id FROM posts UNION ALL SELECT id FROM drafts;
```

### JOIN Optimization

**Order matters - filter early:**
```sql
-- Bad: Large intermediate result
SELECT * FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.created_at > '2024-01-01';

-- Good: Filter first
SELECT * FROM posts p
WHERE p.created_at > '2024-01-01'
JOIN users u ON p.user_id = u.id;
```

**Use appropriate JOIN type:**
```sql
-- INNER JOIN: Only matching rows
SELECT * FROM posts p
INNER JOIN users u ON p.user_id = u.id;

-- LEFT JOIN: All posts, even without user
SELECT * FROM posts p
LEFT JOIN users u ON p.user_id = u.id;
```

**Index JOIN columns:**
```sql
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_users_id ON users(id);  -- Usually PK already indexed
```

### Pagination Optimization

**Offset pagination (slow for large offsets):**
```sql
-- Slow for page 1000
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 20000;
```

**Cursor pagination (faster):**
```sql
-- First page
SELECT * FROM posts 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

-- Next page (using last created_at and id)
SELECT * FROM posts 
WHERE (created_at, id) < ('2024-01-01 12:00:00', 12345)
ORDER BY created_at DESC, id DESC 
LIMIT 20;
```

### Aggregation Optimization

**Use indexes for GROUP BY:**
```sql
CREATE INDEX idx_posts_user_id ON posts(user_id);

SELECT user_id, COUNT(*) 
FROM posts 
GROUP BY user_id;
```

**Filter before aggregating:**
```sql
-- Good
SELECT user_id, COUNT(*) 
FROM posts 
WHERE created_at > '2024-01-01'
GROUP BY user_id;
```

**Use HAVING for aggregate filters:**
```sql
SELECT user_id, COUNT(*) as post_count
FROM posts 
GROUP BY user_id
HAVING COUNT(*) > 10;
```

### Subquery Optimization

**Correlated subqueries (slow):**
```sql
-- Bad: Runs subquery for each row
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM posts WHERE user_id = u.id) > 10;
```

**JOIN instead:**
```sql
-- Good: Single query
SELECT u.* FROM users u
JOIN (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
    HAVING COUNT(*) > 10
) p ON u.id = p.user_id;
```

### Caching Strategies

**Materialized views:**
```sql
CREATE MATERIALIZED VIEW user_post_counts AS
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW user_post_counts;
```

**Query result caching (application level):**
```python
# Cache expensive queries
@cache(ttl=300)
def get_popular_posts():
    return db.query("SELECT * FROM posts ORDER BY views DESC LIMIT 10")
```

## Common Patterns

### Full-text Search

**PostgreSQL:**
```sql
-- Add tsvector column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Update with trigger
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Search
SELECT * FROM posts 
WHERE search_vector @@ to_tsquery('postgresql & optimization');
```

**Use dedicated search engine for complex needs:**
- Elasticsearch
- Algolia
- Meilisearch

### Batch Operations

**Bulk insert:**
```sql
-- Bad: Multiple inserts
INSERT INTO users (name) VALUES ('User 1');
INSERT INTO users (name) VALUES ('User 2');

-- Good: Single insert
INSERT INTO users (name) VALUES 
('User 1'),
('User 2'),
('User 3');
```

**Bulk update:**
```sql
-- Use CASE for conditional updates
UPDATE posts 
SET status = CASE 
    WHEN views > 1000 THEN 'popular'
    WHEN views > 100 THEN 'normal'
    ELSE 'new'
END;
```

### Connection Pooling

```python
# Use connection pool
from sqlalchemy import create_engine

engine = create_engine(
    'postgresql://user:pass@localhost/db',
    pool_size=20,
    max_overflow=10
)
```

## Performance Monitoring

**Check slow queries:**
```sql
-- PostgreSQL: Enable slow query log
ALTER DATABASE mydb SET log_min_duration_statement = 1000;  -- 1 second

-- View pg_stat_statements
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
```

**Check index usage:**
```sql
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Unused indexes
ORDER BY pg_relation_size(indexrelid) DESC;
```

**Check table statistics:**
```sql
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
```

## Best Practices

**Always:**
- Use EXPLAIN ANALYZE for slow queries
- Index foreign keys
- Index WHERE/JOIN columns
- Limit result sets
- Use prepared statements

**Avoid:**
- SELECT *
- Functions on indexed columns in WHERE
- Correlated subqueries
- Large OFFSET values
- Over-indexing

**Monitor:**
- Slow query log
- Index usage
- Table statistics
- Connection pool

## Troubleshooting

**Query still slow after indexing:**
- Check if index is being used (EXPLAIN)
- Verify index column order for composite indexes
- Consider covering index
- Check for stale statistics (ANALYZE table)

**Too many indexes:**
- Remove unused indexes
- Combine similar indexes
- Monitor write performance

**High memory usage:**
- Reduce work_mem
- Optimize sort operations
- Use streaming instead of loading all data

Versions

RepositoryScoreLicence
This page · armanzeroeight/fastagent-plugins100MIT
sdebruyn/fabric-dw-mcp-cli100MIT

Database snapshot: 13.11.2026 · package version 7390ba3263f6 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100query-optimization
    Diagnose and optimize existing slow SQL queries using execution plans, indexing strategies, query rewriting, and ORM tuning. Use when the user provides a query, performance symptom, or EXPLAIN plan; use sql-query-generation when creating a new query from requirements.
  • 100flux-query
    Optimize slow database queries — analyze execution plans, add indexes, rewrite queries. Use when asked about "slow query", "optimize SQL", "query performance", or "explain this query".
  • 100sql-optimizer
    Analyze and optimize SQL queries for performance. Use when a user asks to optimize a query, speed up a slow query, analyze a query plan, add indexes, fix N+1 queries, reduce query time, tune database performance, or rewrite SQL for efficiency. Supports PostgreSQL, MySQL, and SQLite.
  • 100database-optimizer
    Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies,…
  • 100sql-query-optimizer
    Optimize sql query optimizer operations. Auto-activating skill for Data Analytics. Triggers on: sql query optimizer, sql query optimizer Part of the Data Analytics skill category. Use when working with sql query optimizer functionality. Trigger with phrases like "sql query…
  • 100database-optimizer
    Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies,…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.