gistfile1.md

クエリ例

explain analyze SELECT "statuses"."id", "statuses"."updated_at" FROM "statuses"     
LEFT OUTER JOIN mentions ON statuses.id = mentions.status_id AND mentions.account_id = 1    
LEFT OUTER JOIN "accounts" ON "accounts"."id" = "statuses"."account_id"       
WHERE (mentions.account_id = 1 OR statuses.account_id = 1) AND "statuses"."visibility" = 3  
AND "accounts"."silenced" = false ORDER BY "statuses"."id" DESC LIMIT 40;

素の状態

QUERY PLAN                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=233143.00..233157.88 rows=1 width=16) (actual time=5349.084..5402.147 rows=40 loops=1)
   ->  Nested Loop  (cost=233143.00..233157.88 rows=1 width=16) (actual time=5349.076..5401.838 rows=40 loops=1)
         ->  Merge Left Join  (cost=233142.71..233152.86 rows=1 width=24) (actual time=5343.187..5345.125 rows=40 loops=1)
               Merge Cond: (statuses.id = mentions.status_id)
               Filter: ((mentions.account_id = 1) OR (statuses.account_id = 1))
               Rows Removed by Filter: 48
               ->  Sort  (cost=232743.28..232747.95 rows=1869 width=24) (actual time=5062.042..5062.448 rows=88 loops=1)
                     Sort Key: statuses.id DESC
                     Sort Method: quicksort  Memory: 103kB
                     ->  Index Only Scan using index_statuses_20180106 on statuses  (cost=0.56..232641.72 rows=1869 width=24) (actual time=0.050..5055.802 rows=1001 loops=1)
                           Index Cond: (visibility = 3)
                           Heap Fetches: 595
               ->  Sort  (cost=399.42..399.82 rows=161 width=16) (actual time=280.831..281.397 rows=148 loops=1)
                     Sort Key: mentions.status_id DESC
                     Sort Method: quicksort  Memory: 117kB
                     ->  Index Only Scan using index_mentions_on_account_id_and_status_id on mentions  (cost=0.43..393.52 rows=161 width=16) (actual time=15.044..273.821 rows=1470 loops=1)
                           Index Cond: (account_id = 1)
                           Heap Fetches: 1182
         ->  Index Only Scan using accounts_not_silenced on accounts  (cost=0.29..5.00 rows=1 width=8) (actual time=1.396..1.401 rows=1 loops=40)
               Index Cond: (id = statuses.account_id)
               Heap Fetches: 39
 Planning time: 2.229 ms
 Execution time: 5402.699 ms
(23 rows)

素の状態で使われてるインデクス

\d index_statuses_20180106
        Index "public.index_statuses_20180106"
   Column   |            Type             | Definition 
------------+-----------------------------+------------
 account_id | bigint                      | account_id
 id         | bigint                      | id
 visibility | integer                     | visibility
 updated_at | timestamp without time zone | updated_at
btree, for table "public.statuses"

このクエリにこのインデクスが使われているのは良くありません。以下の問題があります。

• 出力順序が異なるので後からソートしないとダメです
• 公開範囲全てを含むので探索対象はタンスにある全トゥートです

DM用の部分インデクスを作成

postgres=# create index statuses_dm on statuses(id,account_id,updated_at) where visibility=3;
CREATE INDEX

• 公開範囲がDirectのトゥートだけを保持する部分インデクスです。
• DMの割合が少ないタンスほど効果が高くなります。

インデクス追加後のクエリ

QUERY PLAN                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=399.99..3372.87 rows=1 width=16) (actual time=21.193..25.817 rows=40 loops=1)
   ->  Nested Loop  (cost=399.99..3372.87 rows=1 width=16) (actual time=21.183..25.517 rows=40 loops=1)
         ->  Merge Left Join  (cost=399.70..3367.86 rows=1 width=24) (actual time=21.129..24.350 rows=40 loops=1)
               Merge Cond: (statuses.id = mentions.status_id)
               Filter: ((mentions.account_id = 1) OR (statuses.account_id = 1))
               Rows Removed by Filter: 48
               ->  Index Only Scan Backward using statuses_dm on statuses  (cost=0.28..2962.95 rows=1869 width=24) (actual time=0.389..2.157 rows=88 loops=1)
                     Heap Fetches: 88
               ->  Sort  (cost=399.42..399.82 rows=161 width=16) (actual time=20.504..21.093 rows=148 loops=1)
                     Sort Key: mentions.status_id DESC
                     Sort Method: quicksort  Memory: 117kB
                     ->  Index Only Scan using index_mentions_on_account_id_and_status_id on mentions  (cost=0.43..393.52 rows=161 width=16) (actual time=0.055..13.303 rows=1470 loops=1)
                           Index Cond: (account_id = 1)
                           Heap Fetches: 1182
         ->  Index Only Scan using accounts_not_silenced on accounts  (cost=0.29..5.00 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=40)
               Index Cond: (id = statuses.account_id)
               Heap Fetches: 39
 Planning time: 21.167 ms
 Execution time: 26.263 ms
(19 rows)