search

rust-lang / crater

Run experiments across parts of the Rust ecosystem!
https://crater.rust-lang.org
643 stars 90 forks source link

Optimize run_by query to avoid full scan of experiment_crates table #601

Closed Mark-Simulacrum closed 2 years ago

Mark-Simulacrum commented 2 years ago

The query plan was previously:

QUERY PLAN
|--SCAN TABLE experiment_crates
`--SEARCH TABLE experiments USING INDEX sqlite_autoindex_experiments_1 (name=?)

and now is:

QUERY PLAN
|--SEARCH TABLE experiments USING INDEX sqlite_autoindex_experiments_1 (name=?)
`--LIST SUBQUERY 2
|--SEARCH TABLE experiment_crates USING INDEX experiment_crates__experiment_skipped (experiment=? AND skipped=?)
`--LIST SUBQUERY 1
    `--SCAN TABLE experiments

While the new query does search more, it does so in a way that is significantly more efficient, as it can avoid scanning the whole experiment_crates table (which is quite large).

Mark-Simulacrum commented 2 years ago

@bors r+

bors commented 2 years ago

:pushpin: Commit 2946f199943b85e257b9bd7ffd694125fa40d5f4 has been approved by Mark-Simulacrum

bors commented 2 years ago

:hourglass: Testing commit 2946f199943b85e257b9bd7ffd694125fa40d5f4 with merge 599c5d7407b251e547d12ddc0eeb6392f10d167b...

bors commented 2 years ago

:sunny: Test successful - checks-actions Approved by: Mark-Simulacrum Pushing 599c5d7407b251e547d12ddc0eeb6392f10d167b to master...