mirror of
https://github.com/Haidra-Org/AI-Horde.git
synced 2026-11-06 05:17:31 +00:00
tazlin
d9faa90061
- user_suspicions (713k rows) and worker_suspicions (570k rows) carried only their primary key, so every suspicion lookup was a sequential scan: User.is_suspicious/Worker.is_suspicious on request paths, and the kudos applier promotion-eligibility scan, whose per-candidate suspicion count did one full-table scan per candidate (EXPLAIN on prod: 22ms and 16.5M buffer hits per subplan loop) - index=True on UserSuspicions.user_id and WorkerSuspicions.worker_id; sql_statements/5.1.1.txt creates ix_user_suspicions_user_id and ix_worker_suspicions_worker_id for existing databases (operator-applied, matching db.create_all() names); HORDE_VERSION bumped to 5.1.1
13 lines
800 B
Plaintext
13 lines
800 B
Plaintext
-- Foreign-key indexes for the suspicion tables. db.create_all() covers fresh
|
|
-- databases; this snippet migrates existing ones. Every suspicion lookup
|
|
-- (User.is_suspicious, Worker.is_suspicious, and the kudos applier's
|
|
-- promotion-eligibility scan) filters these tables by their owner column, and
|
|
-- without an index each check is a sequential scan of the whole table.
|
|
|
|
-- CONCURRENTLY so a live apply never blocks writers. It cannot run inside a
|
|
-- transaction block (psql autocommit applies each statement on its own), and a
|
|
-- failed build leaves an INVALID index: DROP INDEX it and rerun.
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_user_suspicions_user_id ON user_suspicions (user_id);
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_worker_suspicions_worker_id ON worker_suspicions (worker_id);
|