dolthub / dolthub/dolt

Dolt doesn't use indexed lookup on top N query. Very slow query resulting in DoltHub timeouts

Open
#6,227 1 comment 0 reactions 0 assignees View on GitHub
analyzer bug performance sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Dolt `v1.3.0` on Hosted Dolt.

DoltHub uses a table called `password_attempts` with the following definition:

```
| password_attempts | CREATE TABLE `password_attempts` (
`id` varchar(36) NOT NULL,
`user_id_fk` varchar(36) NOT NULL,
`event_type` int,
`ip_address` varchar(255),
`user_agent` varchar(3096),
`created_at` timestamp(6),
PRIMARY KEY (`id`),
KEY `password_attempts_user_id_fk` (`user_id_fk`,`created_at`),
KEY `user_id_fk` (`user_id_fk`),
CONSTRAINT `1jjafe08` FOREIGN KEY (`user_id_fk`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
```

This table currently has ~480k rows.

This query takes > 4 seconds to complete in Dolt:

```
SELECT * FROM password_attempts WHERE (user_id_fk = 'some-id') ORDER BY created_at DESC LIMIT 3;
```

This query take < 1ms in Postgres (a replica of the Hosted Dolt instance).

Here is the plan output:

```
mysql> EXPLAIN SELECT * FROM password_attempts WHERE (user_id_fk = '60deee19-e995-4250-b3f8-f9eb4308b909') ORDER BY created_at DESC LIMIT 3;
+---------------------------------------------------------------------------------------------------------------+
| plan |
+---------------------------------------------------------------------------------------------------------------+
| Limit(3) |
| └─ TopN(Limit: [3]; password_attempts.created_at DESC) |
| └─ Filter |
| ├─ (password_attempts.user_id_fk = 'some-id') |
| └─ IndexedTableAccess(password_attempts) |
| ├─ index: [password_attempts.user_id_fk] |
| ├─ filters: [{[some-id, some-id]}] |
| └─ columns: [id user_id_fk event_type ip_address user_agent created_at] |
+---------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.