Skip to content

Instantly share code, notes, and snippets.

@tateisu

tateisu/union使うやつの実行計画.md

Last active May 24, 2018 14:21
Show Gist options
  • Save tateisu/a7de7a8ff816e83ea2894e599499849d to your computer and use it in GitHub Desktop.
Save tateisu/a7de7a8ff816e83ea2894e599499849d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
union使うやつの実行計画.md 
postgres=# explain analyze SELECT 
postgres-#     "statuses"."id" as status_id,
postgres-#     "statuses"."updated_at"
postgres-# FROM "statuses" 
postgres-# LEFT OUTER JOIN "accounts"
postgres-# ON
postgres-#     "accounts"."id" = "statuses"."account_id"
postgres-# WHERE
postgres-#     statuses.account_id = 1
postgres-#     AND "statuses"."visibility"= 3
postgres-#     AND "accounts"."silenced" = false
postgres-# UNION
postgres-# SELECT 
postgres-#     "statuses"."id" as status_id,
postgres-#     "statuses"."updated_at"
postgres-# FROM "statuses" 
postgres-# INNER JOIN "mentions" 
postgres-# ON
postgres-#     "statuses".id = mentions.status_id
postgres-#     AND "mentions".account_id = 1
postgres-# LEFT OUTER JOIN "accounts"
postgres-# ON
postgres-#     "accounts"."id" = "statuses"."account_id"
postgres-# WHERE
postgres-#     "statuses"."visibility"= 3
postgres-#     AND "accounts"."silenced" = false
postgres-# ORDER BY status_id DESC LIMIT 20;

Limit  (cost=1485.99..1486.00 rows=2 width=16) (actual time=58.542..58.764 rows=20 loops=1)
   ->  Sort  (cost=1485.99..1486.00 rows=2 width=16) (actual time=58.532..58.606 rows=20 loops=1)
         Sort Key: statuses.id DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Unique  (cost=1485.97..1485.98 rows=2 width=16) (actual time=53.041..57.077 rows=364 loops=1)
               ->  Sort  (cost=1485.97..1485.97 rows=2 width=16) (actual time=53.033..54.374 rows=365 loops=1)
                     Sort Key: statuses.id, statuses.updated_at
                     Sort Method: quicksort  Memory: 42kB
                     ->  Append  (cost=0.85..1485.96 rows=2 width=16) (actual time=0.099..51.338 rows=365 loops=1)
                           ->  Nested Loop  (cost=0.85..46.20 rows=1 width=16) (actual time=0.092..9.475 rows=224 loops=1)
                                 ->  Index Only Scan using index_statuses_20180106 on statuses  (cost=0.56..37.89 rows=1 width=24) (actual time=0.059..3.477 rows=224 loops=1)
                                       Index Cond: ((account_id = 1) AND (visibility = 3))
                                       Heap Fetches: 125
                                 ->  Index Only Scan using accounts_not_silenced on accounts  (cost=0.29..8.31 rows=1 width=8) (actual time=0.008..0.012 rows=1 loops=224)
                                       Index Cond: (id = 1)
                                       Heap Fetches: 224
                           ->  Nested Loop  (cost=0.99..1439.73 rows=1 width=16) (actual time=0.727..39.209 rows=141 loops=1)
                                 ->  Nested Loop  (cost=0.70..1434.22 rows=1 width=24) (actual time=0.680..34.925 rows=141 loops=1)
                                       ->  Index Only Scan using index_mentions_on_account_id_and_status_id on mentions  (cost=0.43..393.52 rows=161 width=8) (actual time=0.072..12.942 rows=1476 loops=1)
                                             Index Cond: (account_id = 1)
                                             Heap Fetches: 1188
                                       ->  Index Only Scan using statuses_dm on statuses statuses_1  (cost=0.28..6.45 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=1476)
                                             Index Cond: (id = mentions.status_id)
                                             Heap Fetches: 76
                                 ->  Index Only Scan using accounts_not_silenced on accounts accounts_1  (cost=0.29..5.50 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=141)
                                       Index Cond: (id = statuses_1.account_id)
                                       Heap Fetches: 116
 Planning time: 1.968 ms
 Execution time: 59.194 ms
(29 rows)

unionの前後にlimitを入れる

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

Limit  (cost=1485.99..1486.00 rows=2 width=16) (actual time=13.642..13.845 rows=20 loops=1)
   ->  Sort  (cost=1485.99..1486.00 rows=2 width=16) (actual time=13.634..13.699 rows=20 loops=1)
         Sort Key: statuses.id DESC
         Sort Method: quicksort  Memory: 26kB
         ->  Unique  (cost=1485.97..1485.98 rows=2 width=16) (actual time=12.982..13.452 rows=40 loops=1)
               ->  Sort  (cost=1485.97..1485.97 rows=2 width=16) (actual time=12.973..13.152 rows=40 loops=1)
                     Sort Key: statuses.id, statuses.updated_at
                     Sort Method: quicksort  Memory: 26kB
                     ->  Append  (cost=0.85..1485.96 rows=2 width=16) (actual time=0.172..12.729 rows=40 loops=1)
                           ->  Limit  (cost=0.85..46.20 rows=1 width=16) (actual time=0.153..1.192 rows=20 loops=1)
                                 ->  Nested Loop  (cost=0.85..46.20 rows=1 width=16) (actual time=0.146..1.037 rows=20 loops=1)
                                       ->  Index Only Scan using index_statuses_20180106 on statuses  (cost=0.56..37.89 rows=1 width=24) (actual time=0.089..0.453 rows=20 loops=1)
                                             Index Cond: ((account_id = 1) AND (visibility = 3))
                                             Heap Fetches: 20
                                       ->  Index Only Scan using accounts_not_silenced on accounts  (cost=0.29..8.31 rows=1 width=8) (actual time=0.009..0.013 rows=1 loops=20)
                                             Index Cond: (id = 1)
                                             Heap Fetches: 20
                           ->  Limit  (cost=0.99..1439.73 rows=1 width=16) (actual time=0.418..11.205 rows=20 loops=1)
                                 ->  Nested Loop  (cost=0.99..1439.73 rows=1 width=16) (actual time=0.410..11.061 rows=20 loops=1)
                                       ->  Nested Loop  (cost=0.70..1434.22 rows=1 width=24) (actual time=0.310..10.224 rows=20 loops=1)
                                             ->  Index Only Scan Backward using index_mentions_on_account_id_and_status_id on mentions  (cost=0.43..393.52 rows=161 width=8) (actual time=0.110..3.564 rows=281 loops=1)
                                                   Index Cond: (account_id = 1)
                                                   Heap Fetches: 233
                                             ->  Index Only Scan using statuses_dm on statuses statuses_1  (cost=0.28..6.45 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=281)
                                                   Index Cond: (id = mentions.status_id)
                                                   Heap Fetches: 20
                                       ->  Index Only Scan using accounts_not_silenced on accounts accounts_1  (cost=0.29..5.50 rows=1 width=8) (actual time=0.016..0.023 rows=1 loops=20)
                                             Index Cond: (id = statuses_1.account_id)
                                             Heap Fetches: 19
 Planning time: 2.396 ms
 Execution time: 14.306 ms
(31 rows)

statuses にインデクスを追加

create index statuses_dm_account on statuses (account_id,id,updated_at) where visibility=3;

クエリは上と同じ

 Limit  (cost=1460.40..1460.40 rows=2 width=16) (actual time=10.905..11.105 rows=20 loops=1)
   ->  Sort  (cost=1460.40..1460.40 rows=2 width=16) (actual time=10.897..10.964 rows=20 loops=1)
         Sort Key: statuses.id DESC
         Sort Method: quicksort  Memory: 26kB
         ->  Unique  (cost=1460.37..1460.39 rows=2 width=16) (actual time=10.224..10.698 rows=40 loops=1)
               ->  Sort  (cost=1460.37..1460.38 rows=2 width=16) (actual time=10.216..10.361 rows=40 loops=1)
                     Sort Key: statuses.id, statuses.updated_at
                     Sort Method: quicksort  Memory: 26kB
                     ->  Append  (cost=0.56..1460.36 rows=2 width=16) (actual time=0.481..9.966 rows=40 loops=1)
                           ->  Limit  (cost=0.56..16.61 rows=1 width=16) (actual time=0.473..1.344 rows=20 loops=1)
                                 ->  Nested Loop  (cost=0.56..16.61 rows=1 width=16) (actual time=0.467..1.198 rows=20 loops=1)
                                       ->  Index Only Scan Backward using statuses_dm_account on statuses  (cost=0.28..8.29 rows=1 width=24) (actual time=0.427..0.652 rows=20 loops=1)
                                             Index Cond: (account_id = 1)
                                             Heap Fetches: 20
                                       ->  Index Only Scan using accounts_not_silenced on accounts  (cost=0.29..8.31 rows=1 width=8) (actual time=0.009..0.012 rows=1 loops=20)
                                             Index Cond: (id = 1)
                                             Heap Fetches: 20
                           ->  Limit  (cost=0.99..1443.73 rows=1 width=16) (actual time=0.309..8.321 rows=20 loops=1)
                                 ->  Nested Loop  (cost=0.99..1443.73 rows=1 width=16) (actual time=0.302..8.171 rows=20 loops=1)
                                       ->  Nested Loop  (cost=0.70..1438.22 rows=1 width=24) (actual time=0.254..7.412 rows=20 loops=1)
                                             ->  Index Only Scan Backward using index_mentions_on_account_id_and_status_id on mentions  (cost=0.43..393.52 rows=161 width=8) (actual time=0.064..3.054 rows=281 loops=1)
                                                   Index Cond: (account_id = 1)
                                                   Heap Fetches: 233
                                             ->  Index Only Scan using statuses_dm on statuses statuses_1  (cost=0.28..6.48 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=281)
                                                   Index Cond: (id = mentions.status_id)
                                                   Heap Fetches: 20
                                       ->  Index Only Scan using accounts_not_silenced on accounts accounts_1  (cost=0.29..5.50 rows=1 width=8) (actual time=0.018..0.022 rows=1 loops=20)
                                             Index Cond: (id = statuses_1.account_id)
                                             Heap Fetches: 19
 Planning time: 2.544 ms
 Execution time: 11.453 ms
(31 rows)

このあたりになるとウチの環境では負荷が低すぎて有効なのかどうか分からない

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment