"Which areas to review?"
Options:
- Full query optimization (recommended)
- Index usage analysis
- Join optimization
- Subquery refactoring
- N+1 detection
multiSelect: true
Detection Rules
Index Usage
Check
Recommendation
Severity
Full table scan
Add appropriate index
CRITICAL
Index not used
Check column order
HIGH
Too many indexes
Consolidate indexes
MEDIUM
Missing composite index
Add multi-column index
HIGH
-- BAD: No index on filter columns
SELECT * FROM orders
WHERE created_at > '2024-01-01'
AND status = 'pending';
-- Full table scan!
-- GOOD: Add composite index
CREATE INDEX idx_orders_status_created
ON orders(status, created_at);
-- Index order matters!
-- For WHERE status = ? AND created_at > ?
-- Index(status, created_at) ✓
-- Index(created_at, status) ✗ (less effective)
SELECT Optimization
Check
Recommendation
Severity
SELECT *
Select specific columns
HIGH
Unnecessary columns
Remove unused columns
MEDIUM
No LIMIT
Add LIMIT for large results
HIGH
-- BAD: SELECT * with large result
SELECT * FROM orders
WHERE user_id = 123;
-- Returns all columns, no limit
-- GOOD: Specific columns, limited results
SELECT id, status, total, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;
JOIN Optimization
Check
Recommendation
Severity
Cartesian product
Add join condition
CRITICAL
Join on non-indexed column
Add index
HIGH
Too many joins
Consider denormalization
MEDIUM
Implicit join
Use explicit JOIN syntax
LOW
-- BAD: Implicit join (harder to read, error-prone)
SELECT o.*, u.name
FROM orders o, users u
WHERE o.user_id = u.id;
-- GOOD: Explicit JOIN
SELECT o.id, o.total, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id;
-- BAD: Join on non-indexed column
SELECT o.*, p.name
FROM orders o
JOIN products p ON o.product_code = p.code;
-- If products.code has no index → slow!
-- FIX: Add index
CREATE INDEX idx_products_code ON products(code);
Subquery Optimization
Check
Recommendation
Severity
Correlated subquery
Convert to JOIN
HIGH
IN with subquery
Use EXISTS or JOIN
MEDIUM
Subquery in SELECT
Move to JOIN
HIGH
-- BAD: Correlated subquery (runs for each row)
SELECT *
FROM orders o
WHERE total > (
SELECT AVG(total)
FROM orders
WHERE user_id = o.user_id
);
-- GOOD: Use window function
SELECT *
FROM (
SELECT *,
AVG(total) OVER (PARTITION BY user_id) as avg_total
FROM orders
) sub
WHERE total > avg_total;
-- BAD: IN with large subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'vip');
-- GOOD: Use EXISTS or JOIN
SELECT u.* FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.status = 'vip'
);
-- Or with JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'vip';
N+1 Query Detection
Check
Recommendation
Severity
Loop with query
Batch fetch
CRITICAL
Lazy load in loop
Eager load
CRITICAL
-- N+1 Pattern (application code)
-- Query 1: Get all users
SELECT * FROM users;
-- Then for each user (N queries):
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
-- ... N more queries
-- SOLUTION 1: JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- SOLUTION 2: IN query (for separate queries)
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...);
Aggregation Optimization
Check
Recommendation
Severity
COUNT(*) on large table
Use approximate count
MEDIUM
GROUP BY without index
Add index
HIGH
HAVING vs WHERE
Filter early with WHERE
MEDIUM
-- BAD: COUNT on entire table
SELECT COUNT(*) FROM orders;
-- Scans entire table
-- GOOD: Approximate count (PostgreSQL)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';
-- BAD: WHERE in HAVING
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING status = 'completed'; -- Wrong place!
-- GOOD: Filter before grouping
SELECT user_id, COUNT(*)
FROM orders
WHERE status = 'completed' -- Filter first
GROUP BY user_id;
-- Index for GROUP BY
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);
EXPLAIN Analysis
-- PostgreSQL EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
-- Look for:
-- ✗ Seq Scan (full table scan)
-- ✗ Nested Loop with high rows
-- ✗ Hash Join with large hash
-- ✓ Index Scan
-- ✓ Index Only Scan
-- ✓ Bitmap Index Scan
Response Template
## SQL Query Optimization Results
**Database**: PostgreSQL 15
**Query Type**: SELECT with JOIN
**Estimated Impact**: ~10x improvement
### Index Usage
| Status | Issue | Recommendation |
|--------|-------|----------------|
| CRITICAL | Full table scan on orders | Add index on (status, created_at) |
### Join Analysis
| Status | Issue | Recommendation |
|--------|-------|----------------|
| HIGH | Non-indexed join column | Add index on products.code |
### Query Structure
| Status | Issue | Recommendation |
|--------|-------|----------------|
| HIGH | SELECT * with no LIMIT | Select specific columns, add LIMIT |
### Recommended Indexes
```sql
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
CREATE INDEX idx_products_code ON products(code);
Optimized Query
SELECT o.id, o.total, p.name
FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;
## Best Practices
1. **Indexes**: Add for WHERE, JOIN, ORDER BY columns
2. **SELECT**: Only needed columns, with LIMIT
3. **JOINs**: Explicit syntax, indexed columns
4. **Subqueries**: Prefer JOINs or CTEs
5. **EXPLAIN**: Always analyze query plans
## Integration
- `schema-reviewer`: Database design
- `orm-reviewer`: ORM query patterns
- `perf-analyzer`: Application performance