Summary
Add a rule to detect queries with excessive subquery nesting depth, which causes optimizer issues and maintenance nightmares.
Why This Matters
Deep subquery nesting creates multiple problems:
- Optimizer limitations — planners give up or make poor choices beyond 3-4 levels
- Exponential complexity — each level multiplies execution paths
- Unreadable code — impossible to understand or modify
- Testing difficulty — can't test individual parts easily
- Potential stack overflow — some databases have recursion limits
Optimizer Behavior at Different Depths
| Depth |
PostgreSQL |
MySQL |
SQL Server |
| 1-2 |
Full optimization |
Full optimization |
Full optimization |
| 3-4 |
Some limitations |
Suboptimal plans |
Warnings |
| 5+ |
Plan degradation |
Very slow planning |
May fail |
| 10+ |
Stack risk |
Often fails |
Error |
The Readability Problem
-- This is real code someone has to maintain:
SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders WHERE product_id IN (
SELECT id FROM products WHERE category_id IN (
SELECT id FROM categories WHERE parent_id IN (
SELECT id FROM categories WHERE name = 'Electronics'
)
)
)
)
Rule Specification
| Property |
Value |
| Rule ID |
PERF020 |
| Name |
Deeply nested subqueries |
| Category |
Performance |
| Severity |
Warning |
| Default |
Enabled |
Detection Patterns
-- Should trigger (depth > 2)
SELECT * FROM a WHERE x IN (SELECT y FROM b WHERE z IN (SELECT w FROM c WHERE ...));
-- Severity by depth:
-- 3 levels: Info
-- 4 levels: Warning
-- 5+ levels: Error
Suggested Message
"Query has N levels of subquery nesting. Deep nesting causes optimizer issues and readability problems. Consider: (1) CTEs (WITH clause) to flatten structure, (2) JOINs instead of nested IN/EXISTS, (3) breaking into multiple queries, or (4) temporary tables for intermediate results."
Refactoring Example
-- Before (4 levels deep):
SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders WHERE product_id IN (
SELECT id FROM products WHERE category_id IN (
SELECT id FROM categories WHERE name = 'Electronics'
)
)
);
-- After (CTEs, flat structure):
WITH electronics AS (
SELECT id FROM categories WHERE name = 'Electronics'
),
electronic_products AS (
SELECT id FROM products WHERE category_id IN (SELECT id FROM electronics)
),
electronic_orders AS (
SELECT user_id FROM orders WHERE product_id IN (SELECT id FROM electronic_products)
)
SELECT * FROM users WHERE id IN (SELECT user_id FROM electronic_orders);
-- Even better (JOINs):
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'Electronics';
References
Implementation Notes
- Count parentheses depth that follow SELECT keyword
- Alternative: count nested SELECT occurrences
- Thresholds: 3 (Info), 4 (Warning), 5+ (Error)
- Ignore: inline views that aren't actually nested (parallel subqueries)
- Suggest: CTEs as primary solution
Summary
Add a rule to detect queries with excessive subquery nesting depth, which causes optimizer issues and maintenance nightmares.
Why This Matters
Deep subquery nesting creates multiple problems:
Optimizer Behavior at Different Depths
The Readability Problem
Rule Specification
PERF020Detection Patterns
Suggested Message
Refactoring Example
References
Implementation Notes