Many `sqlite` statements are uncacheable
- 主要语言
- Rust
- 星标
- 104
- 派生
- 138
- 平均合并
- 1 天 13 小时
- 30 天内合并 PR
- 56
描述
Local node profiling revealed that many of our sqlite queries are not cached, leading to statement parsing being a non-trivial amount of processing, and heavy contention of a mutex internal to `sqlite`. This kneecaps performance.
The internal `mutex` we can disable via some `sqlite` setting. This should be step one.
Step two would be fixing all our queries. Unfortunately this looks to be a lot harder.
The main pain point is that statements containing `IN (..N)` are uncacheable, though we _can_ cache such statements if `N` is kept constant. Unfortunately, many of our queries use this.
Another problem appears to be that `diesel` eagerly translates several other constructs into using `IN` even though at the surface these don't appear to be required.
--------------------
@kkovaacs was able to identify such cases using a patched `diesel` to emit the statement cacheability.
Some cases are also.. strange. For example, its unclear why the following statement is considered uncacheable:
```sql
Query is not safe to cache:
SELECT ...
FROM (`notes` LEFT OUTER JOIN `note_scripts` ON
(`notes`.`script_root` = `note_scripts`.`script_root`))
WHERE (
(
(
(
(`notes`.`network_note_type` = ?) AND
(`notes`.`target_account_id` = ?)
) AND
(`notes`.`committed_at` <= ?)
) AND
(
(`notes`.`consumed_at` IS NULL) OR
(`notes`.`consumed_at` > ?)
)
) AND
notes.rowid >= ?
)
ORDER BY notes.rowid ASC LIMIT ?
```
贡献指南
评估
这个 Issue 还没有评估数据。