Summary
Add a rule to detect SELECT COUNT(*) queries without a WHERE clause, which causes full table scans on large tables.
Why This Matters
COUNT(*) without WHERE is deceptively expensive:
- Full table scan — database must read every row
- No index usage — even with indexes, must count all rows
- Lock contention — can block writes on busy tables (depending on isolation level)
- Scales linearly — query time grows with table size
Performance Impact
| Table Size |
Approximate Time |
| 10K rows |
~10ms |
| 1M rows |
~500ms |
| 100M rows |
~30 seconds |
| 1B rows |
~5 minutes |
Times vary by hardware and database engine
Common Misuse
-- Anti-pattern: checking if table has data
SELECT COUNT(*) FROM users;
-- Better: SELECT EXISTS(SELECT 1 FROM users LIMIT 1)
-- Anti-pattern: pagination total count
SELECT COUNT(*) FROM orders;
-- Better: use estimated counts or cache the value
Rule Specification
| Property |
Value |
| Rule ID |
PERF012 |
| Name |
COUNT(*) without WHERE |
| Category |
Performance |
| Severity |
Warning |
| Default |
Enabled |
Detection Patterns
-- Should trigger
SELECT COUNT(*) FROM users;
SELECT COUNT(1) FROM orders;
SELECT COUNT(id) FROM products;
-- Should NOT trigger (has WHERE)
SELECT COUNT(*) FROM users WHERE status = 'active';
SELECT COUNT(*) FROM orders WHERE created_at > '2024-01-01';
Suggested Message
"COUNT(*) without WHERE requires full table scan. Consider adding WHERE clause, using EXISTS for existence checks, or caching count values for large tables."
References
Implementation Notes
- Detect
COUNT(*), COUNT(1), COUNT(column) patterns
- Only trigger when no WHERE clause present
- Consider: lower severity if LIMIT is present (unlikely but possible)
- Consider: suggest
pg_class.reltuples for PostgreSQL estimated counts
Summary
Add a rule to detect
SELECT COUNT(*)queries without aWHEREclause, which causes full table scans on large tables.Why This Matters
COUNT(*) without WHERE is deceptively expensive:
Performance Impact
Times vary by hardware and database engine
Common Misuse
Rule Specification
PERF012Detection Patterns
Suggested Message
References
Implementation Notes
COUNT(*),COUNT(1),COUNT(column)patternspg_class.reltuplesfor PostgreSQL estimated counts