DOLT 1.79 got Poor Performance over JOIN Operations

[abulus]

Dolt 1.79.1 (and 1.76.0) experiences severe performance degradation with JOIN queries that trigger partition scans, causing queries to hang for 60+ seconds. The same queries work fine in Dolt 1.43.11

Example Query Demonstrating the Issue

Minimal Reproducible Example

-- Setup
CREATE TABLE sku (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    sku_name VARCHAR(255) UNIQUE
);

CREATE TABLE annotation (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    shelf_image_id INTEGER,
    sku_id INTEGER,
    points JSON,
    INDEX idx_shelf_image_id (shelf_image_id),
    FOREIGN KEY (sku_id) REFERENCES sku(id)
);

INSERT INTO sku (sku_name) VALUES ('SKU_1'), ('SKU_2');
INSERT INTO annotation (shelf_image_id, sku_id, points) 
VALUES (1, 1, '{"top_left": [0,0], "bottom_right": [100,100]}');

-- ❌ PROBLEMATIC: Hangs in Dolt 1.79.1 (482+ seconds, never completes)
SELECT 
    a.id,
    a.shelf_image_id,
    a.sku_id,
    s.sku_name
FROM annotation a
JOIN sku s ON s.id = a.sku_id
WHERE a.shelf_image_id = 1;

-- ✅ WORKS: Completes in <2 seconds in both versions
SELECT 
    a.id,
    a.shelf_image_id,
    a.sku_id
FROM annotation a
WHERE a.shelf_image_id = 1;

Observed Behavior

Dolt 1.79.1:

• Query state: sku(sku (0/? partitions)) - stuck in partition scan
• Execution time: 482+ seconds (query never completes, must be killed)
• Process list shows query running indefinitely

Dolt 1.43.11:

• Query completes normally
• Execution time: <2 seconds
• Uses indexes properly

Results

Dolt Version	Query (with JOIN)	Query (without JOIN)	Notes
1.43.11	✅ <2s	✅ <2s	Works correctly
1.76.0	❌ 62s timeout	✅ <2s	JOIN causes partition scan
1.79.1	❌ 60+ seconds (hangs)	✅ 2.2s	JOIN causes partition scan

Questions

1. Why does Dolt 1.79.1 choose partition scan over index for JOIN queries when indexes exist?
2. Is this a known regression from 1.43.11 to 1.79.1?
3. How can we force index usage for JOIN queries?

Environment

• Dolt Version: 1.79.1 (also observed in 1.76.0)
• Image: dolthub/dolt-sql-server:1.79.1
• Indexes: annotation(shelf_image_id) exists, sku(id) is primary key

[angelamayxie]

This is something @nicktobey might have more knowledge of, but he is currently on vacation until Friday.

[nicktobey]

I attempted to reproduce this, using the schema in your example, and increasing the size of the tables to ~10k rows each. However, on both 1.79.1 and 1.76.0, the produced plan used a lookup join, not a table scan. It's possible that there may be some other condition required to reproduce the behavior you're seeing.

What is the result of running

DESCRIBE PLAN
SELECT 
    a.id,
    a.shelf_image_id,
    a.sku_id,
    s.sku_name
FROM annotation a
JOIN sku s ON s.id = a.sku_id
WHERE a.shelf_image_id = 1;

[timsehn]

Resolving since we did not hear back.