Many `sqlite` statements are uncacheable
- 主要言語
- Rust
- スター
- 104
- フォーク
- 138
- 平均マージ
- 1日 13時間
- マージ済み PR(30日)
- 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 はまだ評価されていません。