I found a few queries to the TorrentState that can be pretty slow. On my (pretty fast) machine with SSD, such a query takes 4 seconds to execute after a cold start. On a machine with HDD, it can be slower in order of magnitude. This can be the reason for some CoreConnectTimeoutError errors.
The reason for the slowness is that to be fast, the query should use a partial index idx_torrentstate__last_check__partial, and for that, the query should contain exactly the following condition for the TorrentState table: has_data = 1. In that case, the SQLite query optimizer can use the index when constructing the query plan.
Several queries to the TorrentState table do not include this condition, so they do not use the index and execute slowly.
After the fix, the same query executes in 0.04 seconds, eliminating the problem.
kozlovsky
commented
3 months ago
I found a few queries to the
TorrentStatethat can be pretty slow. On my (pretty fast) machine with SSD, such a query takes 4 seconds to execute after a cold start. On a machine with HDD, it can be slower in order of magnitude. This can be the reason for someCoreConnectTimeoutErrorerrors.The reason for the slowness is that to be fast, the query should use a partial index
idx_torrentstate__last_check__partial, and for that, the query should contain exactly the following condition for theTorrentStatetable:has_data = 1. In that case, the SQLite query optimizer can use the index when constructing the query plan.Several queries to the
TorrentStatetable do not include this condition, so they do not use the index and execute slowly.After the fix, the same query executes in 0.04 seconds, eliminating the problem.