Covers EXPLAIN plan analysis with key metrics (Seq Scan, Index Scan, cost, rows, execution time) and five index types (B-Tree, Hash, GIN, GiST, BRIN) for different query patterns
Includes five core optimization patterns: eliminating N+1 queries, cursor-based pagination, efficient aggregation, subquery transformation, and batch operations
Fetches sql-optimization-patterns from wshobson/agents and configures it for Cursor.
3
Select Cursor when prompted
The CLI shows a list of agents. Use arrow keys and space to select Cursor:
◆ Which agents do you want to install to?
│
│ ── Universal (.agents/skills) ────────────────
│ · Cline · Codex · Goose · Windsurf
│ ●Cursor(selected)
│ · Cursor · Aider · Continue
4
Verify installation
Confirm successful installation by checking the skill directory location:
.cursor/skills/sql-optimization-patterns
Restart Cursor to activate sql-optimization-patterns. Access via /sql-optimization-patterns in your agent's command palette.
⚠
Security Notice
We perform automated surface-level scans (Gen AI Scanner, Socket, Snyk) during installation. These checks detect common vulnerabilities but do not guarantee complete security. Always review skill source code and verify the publisher's reputation before production use.
Skills execute code in your environment. Always review source, verify the publisher, and test in isolation before production.
Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
When to Use This Skill
Debugging slow-running queries
Designing performant database schemas
Optimizing application response times
Reducing database load and costs
Improving scalability for growing datasets
Analyzing EXPLAIN query plans
Implementing efficient indexes
Resolving N+1 query problems
Core Concepts
1. Query Execution Plans (EXPLAIN)
Understanding EXPLAIN output is fundamental to optimization.
PostgreSQL EXPLAIN:
-- Basic explainEXPLAINSELECT*FROM users WHERE email ='[email protected]';-- With actual execution statsEXPLAINANALYZESELECT*FROM users WHERE email ='[email protected]';-- Verbose output with more detailsEXPLAIN(ANALYZE, BUFFERS, VERBOSE)SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >NOW()-INTERVAL'30 days';
Key Metrics to Watch:
Seq Scan: Full table scan (usually slow for large tables)
Index Scan: Using index (good)
Index Only Scan: Using index without touching table (best)
Nested Loop: Join method (okay for small datasets)
Hash Join: Join method (good for larger datasets)
Merge Join: Join method (good for sorted data)
Cost: Estimated query cost (lower is better)
Rows: Estimated rows returned
Actual Time: Real execution time
2. Index Strategies
Indexes are the most powerful optimization tool.
Index Types:
B-Tree: Default, good for equality and range queries
Hash: Only for equality (=) comparisons
GIN: Full-text search, array queries, JSONB
GiST: Geometric data, full-text search
BRIN: Block Range INdex for very large tables with correlation
-- Standard B-Tree indexCREATEINDEX idx_users_email ON users(email);-- Composite index (order matters!)CREATEINDEX idx_orders_user_status ON orders(user_id,status);-- Partial index (index subset of rows)CREATEINDEX idx_active_users ON users(email)WHEREstatus='active';-- Expression indexCREATEINDEX idx_users_lower_email ON users(LOWER(email));-- Covering index (include additional columns)CREATEINDEX idx_users_email_covering ON users(email)INCLUDE (name, created_at);-- Full-text search indexCREATEINDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title ||' '|| body));-- JSONB indexCREATEINDEX idx_metadata ON events USING GIN(metadata);
3. Query Optimization Patterns
Avoid SELECT *:
-- Bad: Fetches unnecessary columnsSELECT*FROM users WHERE id =123;-- Good: Fetch only what you needSELECT id, email, name FROM users WHERE id =123;
Use WHERE Clause Efficiently:
-- Bad: Function prevents index usageSELECT*FROM users WHERE LOWER(email)='[email protected]';-- Good: Create functional index or use exact matchCREATEINDEX idx_users_email_lower ON users(LOWER(email));-- Then:SELECT*FROM users WHERE LOWER(email)='[email protected]';-- Or store normalized dataSELECT*FROM users WHERE email ='[email protected]';
Optimize JOINs:
-- Bad: Cartesian product then filterSELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at >'2024-01-01';-- Good: Filter before joinSELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >'2024-01-01';-- Better: Filter both tablesSELECT u.name, o.total
FROM(SELECT*FROM users WHERE created_at >'2024-01-01') u
JOIN orders o ON u.id = o.user_id;
Optimization Patterns
Pattern 1: Eliminate N+1 Queries
Problem: N+1 Query Anti-Pattern
# Bad: Executes N+1 queriesusers = db.query("SELECT * FROM users LIMIT 10")for user in users: orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)# Process orders
Solution: Use JOINs or Batch Loading
-- Solution 1: JOINSELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFTJOIN orders o ON u.id = o.user_id
WHERE u.id IN(1,2,3,4,5);-- Solution 2: Batch querySELECT*FROM orders
WHERE user_id IN(1,2,3,4,5);
# Good: Single query with JOIN or batch load# Using JOINresults = db.query("""
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5)
""")# Or batch loadusers = db.query("SELECT * FROM users LIMIT 10")user_ids =[u.idfor u in users]orders = db.query("SELECT * FROM orders WHERE user_id IN (?)", user_ids
)# Group orders by user_idorders_by_user ={}for order in orders: orders_by_user.setdefault(order.user_id,[]).append(order)
Pattern 2: Optimize Pagination
Bad: OFFSET on Large Tables
-- Slow for large offsetsSELECT*FROM users
ORDERBY created_at DESCLIMIT20OFFSET100000;-- Very slow!
Good: Cursor-Based Pagination
-- Much faster: Use cursor (last seen ID)SELECT*FROM users
WHERE created_at <'2024-01-15 10:30:00'-- Last cursorORDERBY created_at DESCLIMIT20;-- With composite sortingSELECT*FROM users
WHERE(created_at, id)<
✓
Make data-driven prioritization decisions faster
Stakeholder Communication
Draft PRDs, status updates, and stakeholder presentations
›Access to product documentation and roadmap tools (Jira, Notion, etc.)
›Understanding of product management frameworks (RICE, Jobs-to-be-Done, etc.)
›Stakeholder contact information and communication channels
Time Estimate
30-60 minutes to see productivity improvements
Steps
1Install product management skill
2Start with user story generation for known feature
3Progress to competitive analysis: research 2-3 competitors
4Use for roadmap prioritization: apply RICE/ICE scoring
5Draft stakeholder communications and refine based on feedback
6Build template library for recurring PM tasks
7Share effective prompts with product team
Common Pitfalls
⚠Not validating competitive research—verify facts before sharing
⚠Accepting user stories without involving engineering team
⚠Over-relying on frameworks without qualitative judgment
⚠Not customizing outputs to company culture and communication style
⚠Skipping stakeholder validation of generated requirements
Best Practices
✓ Do
+Validate research and competitive analysis with real data
+Collaborate with engineering when generating technical requirements
+Customize frameworks and templates to your company context
+Use skill for first drafts, refine with stakeholder input
+Document successful prompt patterns for PM tasks
+Combine AI efficiency with human judgment and intuition
✗ Don't
−Don't publish competitive analysis without fact-checking
−Don't finalize user stories without engineering review
−Don't make prioritization decisions solely on AI scoring
−Don't skip customer validation of generated requirements
−Don't ignore company-specific context and culture
💡 Pro Tips
★Provide context: company goals, constraints, customer feedback
★Ask for alternatives: 'Show 3 ways to prioritize this roadmap'
★Request stakeholder-specific formatting: 'Executive summary vs. engineering spec'
★Use skill for 70% generation + 30% customization to company needs
When to Use This
✓ Use when
Use for user story writing, competitive research, roadmap prioritization, stakeholder communication, and PRD drafting. Best for reducing repetitive documentation and research work.
✗ Avoid when
Avoid for strategic product vision (requires deep customer empathy), pricing decisions (needs market and financial expertise), or when face-to-face customer discovery is more valuable than speed.
Learning Path
1Basic: user stories, feature specs, status updates
