ぼっちインスタンス御用達インデックス

index.md

マスター勢にいろいろ教えていただいたので、やってみました。

元のクエリ

> EXPLAIN ANALYZE
SELECT "statuses"."id", "statuses"."updated_at" FROM "statuses"
LEFT OUTER JOIN "accounts" ON "accounts"."id" = "statuses"."account_id"
WHERE ("statuses"."local" = TRUE OR "statuses"."uri" IS NULL)
AND "statuses"."visibility" = 0
AND (statuses.reblog_of_id IS NULL)
AND (statuses.reply = FALSE OR statuses.in_reply_to_account_id = statuses.account_id)
AND "accounts"."silenced" = FALSE
ORDER BY "statuses"."id" DESC LIMIT 40
;

                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.14..317.06 rows=40 width=16) (actual time=0.098..2596.563 rows=40 loops=1)
   ->  Nested Loop  (cost=0.14..27160.40 rows=3428 width=16) (actual time=0.098..2596.553 rows=40 loops=1)
         ->  Index Scan Backward using statuses_pkey on statuses  (cost=0.08..23115.30 rows=3428 width=24) (actual time=0.087..2596.246 rows=40 loops=1)
               Filter: ((local OR (uri IS NULL)) AND (reblog_of_id IS NULL) AND ((NOT reply) OR (in_reply_to_account_id = account_id)) AND (visibility = 0))
               Rows Removed by Filter: 6010
         ->  Index Scan using accounts_pkey on accounts  (cost=0.06..1.18 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=40)
               Index Cond: (id = statuses.account_id)
               Filter: (NOT silenced)
 Planning time: 594.256 ms
 Execution time: 2596.689 ms
(10 rows)

Time: 3581.940 ms

インデックスを作ってみる

> CREATE INDEX accounts_not_silenced ON accounts using btree (id)
WHERE not silenced;

Time: 5044.809 ms

> CREATE INDEX statuses_public_local ON statuses  using btree (id)
WHERE "statuses"."visibility" = 0 
AND (statuses.reblog_of_id IS NULL)
AND (statuses.reply = FALSE OR statuses.in_reply_to_account_id = statuses.account_id) 
AND ("statuses"."local" = TRUE OR "statuses"."uri" IS NULL);

Time: 69976.339 ms

インデックス作成後のクエリ

> EXPLAIN ANALYZE
SELECT "statuses"."id", "statuses"."updated_at" FROM "statuses"
LEFT OUTER JOIN "accounts" ON "accounts"."id" = "statuses"."account_id"
WHERE ("statuses"."local" = TRUE OR "statuses"."uri" IS NULL)
AND "statuses"."visibility" = 0
AND (statuses.reblog_of_id IS NULL)
AND (statuses.reply = FALSE OR statuses.in_reply_to_account_id = statuses.account_id)
AND "accounts"."silenced" = FALSE
ORDER BY "statuses"."id" DESC LIMIT 40
;

                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.11..25.88 rows=40 width=16) (actual time=0.067..0.240 rows=40 loops=1)
   ->  Nested Loop  (cost=0.11..2232.41 rows=3466 width=16) (actual time=0.066..0.236 rows=40 loops=1)
         ->  Index Scan Backward using statuses_public_local on statuses  (cost=0.06..676.86 rows=3466 width=24) (actual time=0.032..0.102 rows=40 loops=1)
         ->  Index Only Scan using accounts_not_silenced on accounts  (cost=0.06..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=40)
               Index Cond: (id = statuses.account_id)
               Heap Fetches: 40
 Planning time: 0.724 ms
 Execution time: 0.272 ms
(8 rows)

Time: 129.430 ms

すっっごく速くなった。約30倍！