max / makenotwork

739 B · 18 lines History Blame Raw

1	-- Denormalize reply_count onto threads to eliminate LEFT JOIN + COUNT on listings.
2	-- reply_count = total posts minus 1 (the OP), floored at 0.
3	ALTER TABLE threads ADD COLUMN reply_count INTEGER NOT NULL DEFAULT 0;
4
5	-- Backfill from current data.
6	UPDATE threads SET reply_count = GREATEST(
7	(SELECT COUNT(*) FROM posts WHERE posts.thread_id = threads.id) - 1,
8	0
9	);
10
11	-- Add CHECK so it never goes negative.
12	ALTER TABLE threads ADD CONSTRAINT reply_count_non_negative CHECK (reply_count >= 0);
13
14	-- Partial index for the hot-path listing query (category thread list, active threads).
15	CREATE INDEX IF NOT EXISTS idx_threads_category_active
16	ON threads (category_id, pinned DESC, last_activity_at DESC)
17	WHERE deleted_at IS NULL;
18