**Critical Red Flags in Execution Plans:**

1. **Sequential/Table Scans on Large Tables**

- Indicator: "Seq Scan" (PostgreSQL), "type: ALL" (MySQL), "Table Scan" (SQL Server)

- Impact: O(n) performance, reads entire table

- When acceptable: Small tables (<1000 rows), retrieving >20% of rows

- Fix: Add appropriate index on WHERE/JOIN columns

2. **High Row Count Discrepancies**

- Indicator: Estimated rows: 100, Actual rows: 100,000

- Impact: Optimizer chooses wrong join method or order

- Cause: Outdated statistics, data skew

- Fix: Run ANALYZE (PostgreSQL), ANALYZE TABLE (MySQL), UPDATE STATISTICS (SQL Server)

3. **Nested Loop Joins on Large Tables**

- Indicator: "Nested Loop" with high row counts

- Impact: O(n × m) performance, exponential slowdown

- When acceptable: Small outer table (<1000 rows), indexed inner table

- Fix: Add index on join column or force hash join

4. **Expensive Sort Operations**

- Indicator: "Sort" with high cost, "Using filesort" (MySQL)

- Impact: O(n log n) CPU and memory usage

- When acceptable: Small result sets, necessary ORDER BY

- Fix: Add index on ORDER BY columns, reduce result set size

5. **Temporary Tables and Filesorts**

- Indicator: "Using temporary" (MySQL), "Materialize" (PostgreSQL)

- Impact: Extra I/O, memory usage, slower execution

- Cause: Complex GROUP BY, DISTINCT, or subqueries

- Fix: Simplify query, add indexes, increase memory

6. **Hash Operations Spilling to Disk**

- Indicator: "Hash Batches" > 1, "Disk" in buffers

- Impact: Massive slowdown (100x+), disk I/O

- Cause: Insufficient memory for hash table

- Fix: Increase work_mem (PostgreSQL), join_buffer_size (MySQL)

7. **Multiple Scans of Same Table**

- Indicator: Same table appears multiple times in plan

- Impact: Redundant I/O and processing

- Cause: Correlated subqueries, poor query structure

- Fix: Rewrite with CTEs or JOINs

**Performance Benchmarks:**

- **Index Seek**: 0.01-0.1ms per lookup

- **Index Scan**: 1-10ms per 1000 rows

- **Table Scan**: 10-100ms per 1000 rows