Query is 1.63x slower in Dolt than MySQL

## Summary

Dolt median latency is 1.63x MySQL

MySQL 8.4.11 median: **4.902 ms**
Dolt 2.3.1 median: **7.990 ms**
Dolt/MySQL ratio: **1.63x**

## Reproduction

Standalone reproduction: [repro.sql](https://gist.github.com/fulghum/ba4092b8608ad31c090175a5a1d685a4)

The generated script is standalone and creates `querylab_repro_6c072095368f0342`, loads 3250 deterministic rows, analyzes the retained tables, and runs the query. It preserves all rows and indexes for tables referenced by the query; unrelated tables were removed.

```sql
WITH category_sales AS (SELECT p.category, o.customer_id, SUM(i.quantity*i.unit_price) sales FROM order_items i JOIN products p ON p.id=i.product_id JOIN orders o ON o.id=i.order_id GROUP BY p.category,o.customer_id), category_avg AS (SELECT category, AVG(sales) avg_sales FROM category_sales GROUP BY category) SELECT s.category, COUNT(*) above_average FROM category_sales s JOIN category_avg a ON a.category=s.category WHERE s.sales>a.avg_sales GROUP BY s.category ORDER BY s.category
```

## Plan difference summary

- Likely hotspot: MySQL 8.4.11 materializes category_sales once and reuses it; Dolt 2.3.1 shows the full orders/items/products join and category-sales aggregation twice, once for each CTE reference.
- Join strategy differs: MySQL 8.4.11 uses nested-loop join; Dolt 2.3.1 uses merge join.
- Join input order differs: MySQL 8.4.11 accesses temporary → s → temporary → i → o → a → temporary → category_sales; Dolt 2.3.1 accesses order_items → orders → order_items → orders.
- Scan targets differ: MySQL 8.4.11 scans temporary, s, temporary, temporary, category_sales; Dolt 2.3.1 scans products, products.
- Index choices differ: MySQL 8.4.11 uses idx_products_category, idx_items_product, PRIMARY; Dolt 2.3.1 uses order_items.order_id, orders.id, order_items.order_id, orders.id.
- Check index selectivity: Dolt 2.3.1 shows unbounded index ranges, which can behave like a full index scan.
- Aggregation strategy differs: MySQL 8.4.11 materializes an intermediate result; Dolt 2.3.1 does not show materialization.

MySQL 8.4.11 explain plan

```text
-> Sort: s.category
-> Table scan on
-> Aggregate using temporary table
-> Nested loop inner join (cost=10002 rows=0)
-> Table scan on s (cost=2.5..2.5 rows=0)
-> Materialize CTE category_sales if needed (cost=0..0 rows=0)
-> Table scan on
-> Aggregate using temporary table
-> Nested loop inner join (cost=1425 rows=2000)
-> Nested loop inner join (cost=725 rows=2000)
-> Covering index scan on p using idx_products_category (cost=25.2 rows=250)
-> Index lookup on i using idx_items_product (product_id=p.id) (cost=2 rows=8)
-> Single-row index lookup on o using PRIMARY (id=i.order_id) (cost=0.25 rows=1)
-> Filter: (s.sales > a.avg_sales) (cost=0.75..5 rows=6.67)
-> Covering index lookup on a using (category=s.category) (cost=0.25..5 rows=20)
-> Materialize CTE category_avg (cost=0..0 rows=0)
-> Table scan on
-> Aggregate using temporary table
-> Table scan on category_sales (cost=2.5..2.5 rows=0)
-> Materialize CTE category_sales if needed (query plan printed elsewhere) (cost=0..0 rows=0)
```

Dolt 2.3.1 explain plan

```text
Project
├─ columns: [s.category, count(1) as above_average]
└─ Sort(s.category ASC)
└─ GroupBy
├─ select: COUNT(1), s.category
├─ group: s.category
└─ Filter
├─ (s.sales > a.avg_sales)
└─ HashJoin
├─ (a.category = s.category)
├─ SubqueryAlias
│ ├─ name: s
│ ├─ outerVisibility: false
│ ├─ isLateral: false
│ ├─ cacheable: true
│ ├─ colSet: (27-29)
│ ├─ tableId: 8
│ └─ Project
│ ├─ columns: [p.category, o.customer_id, sum((i.quantity * i.unit_price)) as sales]
│ └─ GroupBy
│ ├─ select: SUM((i.quantity * i.unit_price)), p.category, o.customer_id
│ ├─ group: p.category, o.customer_id
│ └─ HashJoin
│ ├─ (p.id = i.product_id)
│ ├─ MergeJoin
│ │ ├─ cmp: (i.order_id = o.id)
│ │ ├─ TableAlias(i)
│ │ │ └─ IndexedTableAccess(order_items)
│ │ │ ├─ index: [order_items.order_id]
│ │ │ ├─ filters: [{[NULL, ∞)}]
│ │ │ └─ columns: [order_id product_id quantity unit_price]
│ │ └─ TableAlias(o)
│ │ └─ IndexedTableAccess(orders)
│ │ ├─ index: [orders.id]
│ │ ├─ filters: [{[NULL, ∞)}]
│ │ └─ columns: [id customer_id]
│ └─ HashLookup
│ ├─ left-key: (i.product_id)
│ ├─ right-key: (p.id)
│ └─ TableAlias(p)
│ └─ Table
│ ├─ name: products
│ └─ columns: [id category]
└─ HashLookup
├─ left-key: (s.category)
├─ right-key: (a.category)
└─ CachedResults
└─ SubqueryAlias
├─ name: a
├─ outerVisibility: false
├─ isLateral: false
├─ cacheable: true
└─ Project
├─ columns: [category_sales.category, avg(category_sales.sales) as avg_sales]
└─ GroupBy
├─ select: AVG(category_sales.sales), category_sales.category
├─ group: category_sales.category
└─ SubqueryAlias
├─ name: category_sales
├─ outerVisibility: false
├─ isLateral: false
├─ cacheable: true
├─ colSet: (20-22)
├─ tableId: 5
└─ Project
├─ columns: [p.category, o.customer_id, sum((i.quantity * i.unit_price)) as sales]
└─ GroupBy
├─ select: SUM((i.quantity * i.unit_price)), p.category, o.customer_id
├─ group: p.category, o.customer_id
└─ HashJoin
├─ (p.id = i.product_id)
├─ MergeJoin
│ ├─ cmp: (i.order_id = o.id)
│ ├─ TableAlias(i)
│ │ └─ IndexedTableAccess(order_items)
│ │ ├─ index: [order_items.order_id]
│ │ ├─ filters: [{[NULL, ∞)}]
│ │ └─ columns: [order_id product_id quantity unit_price]
│ └─ TableAlias(o)
│ └─ IndexedTableAccess(orders)
│ ├─ index: [orders.id]
│ ├─ filters: [{[NULL, ∞)}]
│ └─ columns: [id customer_id]
└─ HashLookup
├─ left-key: (i.product_id)
├─ right-key: (p.id)
└─ TableAlias(p)
└─ Table
├─ name: products
└─ columns: [id category]
```

QueryLab finding: `6c072095368f0342`

Start with the standalone repro.sql linked in the issue and run the supplied query and EXPLAIN plans against Dolt 2.3.1 and MySQL 8.4.11. Investigate the CTE, join, index-range, and aggregation differences described in the plan summaries. Done means the cause of the repeated or inefficient work is identified and the reproduction shows a validated improvement without changing query results.

Written by the indexing model from the issue text.