API tools faq
paste
Advertisement
Guest User

Query plans

a guest
May 11th, 2016
281
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.40 KB | None | 0 0
raw download clone embed print report
  1. ========== Query 1 ==========
  2.  
  3. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE release_group IN (SELECT id FROM mb.release_group WHERE name = 'Bear');
  4. QUERY PLAN
  5. ---------------------------------------------------------------------------------------------------------------------------------------------
  6. Nested Loop (cost=0.85..50.97 rows=6 width=4) (actual time=0.060..0.094 rows=3 loops=1)
  7. Buffers: shared hit=13
  8. -> Index Scan using release_group_idx_name on release_group (cost=0.43..16.47 rows=3 width=4) (actual time=0.039..0.043 rows=2 loops=1)
  9. Index Cond: ((name)::text = 'Bear'::text)
  10. Buffers: shared hit=5
  11. -> Index Scan using release_idx_release_group on release (cost=0.43..11.48 rows=2 width=8) (actual time=0.010..0.013 rows=2 loops=2)
  12. Index Cond: (release_group = release_group.id)
  13. Buffers: shared hit=8
  14. Planning time: 0.552 ms
  15. Execution time: 0.147 ms
  16.  
  17.  
  18.  
  19. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE release_group = ANY(ARRAY(SELECT id FROM mb.release_group WHERE name = 'Bear'));
  20. QUERY PLAN
  21. -----------------------------------------------------------------------------------------------------------------------------------------------
  22. Index Scan using release_idx_release_group on release (cost=16.90..119.37 rows=19 width=4) (actual time=0.085..0.098 rows=3 loops=1)
  23. Index Cond: (release_group = ANY ($0))
  24. Buffers: shared hit=13
  25. InitPlan 1 (returns $0)
  26. -> Index Scan using release_group_idx_name on release_group (cost=0.43..16.47 rows=3 width=4) (actual time=0.048..0.052 rows=2 loops=1)
  27. Index Cond: ((name)::text = 'Bear'::text)
  28. Buffers: shared hit=5
  29. Planning time: 0.265 ms
  30. Execution time: 0.143 ms
  31.  
  32.  
  33.  
  34.  
  35.  
  36. ========== Query 2 ==========
  37.  
  38. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE release_group IN (SELECT id FROM mb.release_group WHERE name < 'Bear');
  39. QUERY PLAN
  40. ----------------------------------------------------------------------------------------------------------------------------------------------------------
  41. Hash Semi Join (cost=23245.00..82931.97 rows=264858 width=4) (actual time=503.764..5790.509 rows=163703 loops=1)
  42. Hash Cond: (release.release_group = release_group.id)
  43. Buffers: shared hit=16082 read=23866, temp read=2886 written=2884
  44. -> Seq Scan on release (cost=0.00..39750.62 rows=1575162 width=8) (actual time=0.014..2517.955 rows=1575162 loops=1)
  45. Buffers: shared hit=133 read=23866
  46. -> Hash (cost=20941.20..20941.20 rows=140384 width=4) (actual time=502.453..502.453 rows=135215 loops=1)
  47. Buckets: 131072 Batches: 2 Memory Usage: 3409kB
  48. Buffers: shared hit=15949, temp written=197
  49. -> Bitmap Heap Scan on release_group (cost=3976.40..20941.20 rows=140384 width=4) (actual time=87.192..303.230 rows=135215 loops=1)
  50. Recheck Cond: ((name)::text < 'Bear'::text)
  51. Heap Blocks: exact=15203
  52. Buffers: shared hit=15949
  53. -> Bitmap Index Scan on release_group_idx_name (cost=0.00..3941.31 rows=140384 width=0) (actual time=83.749..83.749 rows=135215 loops=1)
  54. Index Cond: ((name)::text < 'Bear'::text)
  55. Buffers: shared hit=746
  56. Planning time: 0.646 ms
  57. Execution time: 6000.449 ms
  58.  
  59.  
  60.  
  61. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE release_group = ANY(ARRAY(SELECT id FROM mb.release_group WHERE name < 'Bear'));
  62. QUERY PLAN
  63. ------------------------------------------------------------------------------------------------------------------------------------------------------
  64. Index Scan using release_idx_release_group on release (cost=20941.63..21044.10 rows=19 width=4) (actual time=728.155..2303.396 rows=163703 loops=1)
  65. Index Cond: (release_group = ANY ($0))
  66. Buffers: shared hit=499269 read=62795
  67. InitPlan 1 (returns $0)
  68. -> Bitmap Heap Scan on release_group (cost=3976.40..20941.20 rows=140384 width=4) (actual time=94.026..489.196 rows=135215 loops=1)
  69. Recheck Cond: ((name)::text < 'Bear'::text)
  70. Heap Blocks: exact=15203
  71. Buffers: shared hit=1 read=15948
  72. -> Bitmap Index Scan on release_group_idx_name (cost=0.00..3941.31 rows=140384 width=0) (actual time=90.334..90.334 rows=135215 loops=1)
  73. Index Cond: ((name)::text < 'Bear'::text)
  74. Buffers: shared read=746
  75. Planning time: 0.364 ms
  76. Execution time: 2517.406 ms
  77.  
  78.  
  79.  
  80.  
  81.  
  82. ========== Query 3 ==========
  83.  
  84. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' OR release_group IN (SELECT id FROM mb.release_group WHERE name = 'Bear');
  85. QUERY PLAN
  86. -----------------------------------------------------------------------------------------------------------------------------------------------
  87. Seq Scan on release (cost=16.48..47642.91 rows=787583 width=4) (actual time=122.779..710.886 rows=11 loops=1)
  88. Filter: (((name)::text = 'Tiger'::text) OR (hashed SubPlan 1))
  89. Rows Removed by Filter: 1575151
  90. Buffers: shared hit=14065 read=9939
  91. SubPlan 1
  92. -> Index Scan using release_group_idx_name on release_group (cost=0.43..16.47 rows=3 width=4) (actual time=0.044..0.049 rows=2 loops=1)
  93. Index Cond: ((name)::text = 'Bear'::text)
  94. Buffers: shared hit=5
  95. Planning time: 0.286 ms
  96. Execution time: 710.976 ms
  97.  
  98.  
  99.  
  100. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' OR release_group = ANY(ARRAY(SELECT id FROM mb.release_group WHERE name = 'Bear'));
  101. QUERY PLAN
  102. -----------------------------------------------------------------------------------------------------------------------------------------------
  103. Bitmap Heap Scan on release (cost=65.37..159.70 rows=24 width=4) (actual time=0.141..0.172 rows=11 loops=1)
  104. Recheck Cond: (((name)::text = 'Tiger'::text) OR (release_group = ANY ($0)))
  105. Heap Blocks: exact=9
  106. Buffers: shared hit=23
  107. InitPlan 1 (returns $0)
  108. -> Index Scan using release_group_idx_name on release_group (cost=0.43..16.47 rows=3 width=4) (actual time=0.032..0.037 rows=2 loops=1)
  109. Index Cond: ((name)::text = 'Bear'::text)
  110. Buffers: shared hit=5
  111. -> BitmapOr (cost=48.90..48.90 rows=24 width=0) (actual time=0.131..0.131 rows=0 loops=1)
  112. Buffers: shared hit=14
  113. -> Bitmap Index Scan on release_idx_name (cost=0.00..4.46 rows=5 width=0) (actual time=0.049..0.049 rows=8 loops=1)
  114. Index Cond: ((name)::text = 'Tiger'::text)
  115. Buffers: shared hit=3
  116. -> Bitmap Index Scan on release_idx_release_group (cost=0.00..44.42 rows=19 width=0) (actual time=0.074..0.074 rows=3 loops=1)
  117. Index Cond: (release_group = ANY ($0))
  118. Buffers: shared hit=11
  119. Planning time: 0.302 ms
  120. Execution time: 0.245 ms
  121.  
  122.  
  123.  
  124.  
  125.  
  126. ========== Query 4 ==========
  127.  
  128. EXPLAIN SELECT id FROM mb.release WHERE name = 'Tiger' OR release_group IN (SELECT id FROM mb.release_group WHERE name < 'Bear');
  129. QUERY PLAN
  130. -----------------------------------------------------------------------------------------------------------
  131. Seq Scan on release (cost=3976.40..14622816003.91 rows=787583 width=4)
  132. Filter: (((name)::text = 'Tiger'::text) OR (SubPlan 1))
  133. SubPlan 1
  134. -> Materialize (cost=3976.40..22192.12 rows=140384 width=4)
  135. -> Bitmap Heap Scan on release_group (cost=3976.40..20941.20 rows=140384 width=4)
  136. Recheck Cond: ((name)::text < 'Bear'::text)
  137. -> Bitmap Index Scan on release_group_idx_name (cost=0.00..3941.31 rows=140384 width=0)
  138. Index Cond: ((name)::text < 'Bear'::text)
  139.  
  140.  
  141.  
  142. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' OR release_group = ANY(ARRAY(SELECT id FROM mb.release_group WHERE name < 'Bear'));
  143. QUERY PLAN
  144. ------------------------------------------------------------------------------------------------------------------------------------------------------
  145. Bitmap Heap Scan on release (cost=20990.11..21084.43 rows=24 width=4) (actual time=1178.843..1754.903 rows=163711 loops=1)
  146. Recheck Cond: (((name)::text = 'Tiger'::text) OR (release_group = ANY ($0)))
  147. Heap Blocks: exact=23948
  148. Buffers: shared hit=402089 read=44221
  149. InitPlan 1 (returns $0)
  150. -> Bitmap Heap Scan on release_group (cost=3976.40..20941.20 rows=140384 width=4) (actual time=102.120..498.496 rows=135215 loops=1)
  151. Recheck Cond: ((name)::text < 'Bear'::text)
  152. Heap Blocks: exact=15203
  153. Buffers: shared read=15949
  154. -> Bitmap Index Scan on release_group_idx_name (cost=0.00..3941.31 rows=140384 width=0) (actual time=98.245..98.245 rows=135215 loops=1)
  155. Index Cond: ((name)::text < 'Bear'::text)
  156. Buffers: shared read=746
  157. -> BitmapOr (cost=48.90..48.90 rows=24 width=0) (actual time=1171.686..1171.686 rows=0 loops=1)
  158. Buffers: shared hit=402089 read=20273
  159. -> Bitmap Index Scan on release_idx_name (cost=0.00..4.46 rows=5 width=0) (actual time=0.108..0.108 rows=8 loops=1)
  160. Index Cond: ((name)::text = 'Tiger'::text)
  161. Buffers: shared read=3
  162. -> Bitmap Index Scan on release_idx_release_group (cost=0.00..44.42 rows=19 width=0) (actual time=1171.569..1171.569 rows=163703 loops=1)
  163. Index Cond: (release_group = ANY ($0))
  164. Buffers: shared hit=402089 read=20270
  165. Planning time: 0.402 ms
  166. Execution time: 1962.152 ms
  167.  
  168.  
  169.  
  170.  
  171.  
  172. ========== Query 5 ==========
  173.  
  174. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' AND release_group IN (SELECT id FROM mb.release_group WHERE name = 'Bear');
  175. QUERY PLAN
  176. ---------------------------------------------------------------------------------------------------------------------------------------------
  177. Nested Loop (cost=0.85..41.21 rows=1 width=4) (actual time=0.178..0.178 rows=0 loops=1)
  178. Join Filter: (release.release_group = release_group.id)
  179. Rows Removed by Join Filter: 16
  180. Buffers: shared hit=15
  181. -> Index Scan using release_group_idx_name on release_group (cost=0.43..16.47 rows=3 width=4) (actual time=0.039..0.044 rows=2 loops=1)
  182. Index Cond: ((name)::text = 'Bear'::text)
  183. Buffers: shared hit=5
  184. -> Materialize (cost=0.43..24.52 rows=5 width=8) (actual time=0.018..0.046 rows=8 loops=2)
  185. Buffers: shared hit=10
  186. -> Index Scan using release_idx_name on release (cost=0.43..24.50 rows=5 width=8) (actual time=0.028..0.052 rows=8 loops=1)
  187. Index Cond: ((name)::text = 'Tiger'::text)
  188. Buffers: shared hit=10
  189. Planning time: 0.590 ms
  190. Execution time: 0.226 ms
  191.  
  192.  
  193.  
  194. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' AND release_group = ANY(ARRAY(SELECT id FROM mb.release_group WHERE name = 'Bear'));
  195. QUERY PLAN
  196. -----------------------------------------------------------------------------------------------------------------------------------------------
  197. Index Scan using release_idx_name on release (cost=16.90..41.03 rows=1 width=4) (actual time=0.109..0.109 rows=0 loops=1)
  198. Index Cond: ((name)::text = 'Tiger'::text)
  199. Filter: (release_group = ANY ($0))
  200. Rows Removed by Filter: 8
  201. Buffers: shared hit=15
  202. InitPlan 1 (returns $0)
  203. -> Index Scan using release_group_idx_name on release_group (cost=0.43..16.47 rows=3 width=4) (actual time=0.028..0.034 rows=2 loops=1)
  204. Index Cond: ((name)::text = 'Bear'::text)
  205. Buffers: shared hit=5
  206. Planning time: 0.304 ms
  207. Execution time: 0.152 ms
  208.  
  209.  
  210.  
  211.  
  212.  
  213. ========== Query 6 ==========
  214.  
  215. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' AND release_group IN (SELECT id FROM mb.release_group WHERE name < 'Bear');
  216. QUERY PLAN
  217. ----------------------------------------------------------------------------------------------------------------------------------------
  218. Nested Loop Semi Join (cost=0.85..66.74 rows=1 width=4) (actual time=0.198..0.198 rows=0 loops=1)
  219. Buffers: shared hit=42
  220. -> Index Scan using release_idx_name on release (cost=0.43..24.50 rows=5 width=8) (actual time=0.034..0.057 rows=8 loops=1)
  221. Index Cond: ((name)::text = 'Tiger'::text)
  222. Buffers: shared hit=10
  223. -> Index Scan using release_group_pkey on release_group (cost=0.43..8.45 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=8)
  224. Index Cond: (id = release.release_group)
  225. Filter: ((name)::text < 'Bear'::text)
  226. Rows Removed by Filter: 1
  227. Buffers: shared hit=32
  228. Planning time: 0.693 ms
  229. Execution time: 0.248 ms
  230.  
  231.  
  232.  
  233. EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM mb.release WHERE name = 'Tiger' AND release_group = ANY(ARRAY(SELECT id FROM mb.release_group WHERE name < 'Bear'));
  234. QUERY PLAN
  235. ------------------------------------------------------------------------------------------------------------------------------------------------------
  236. Index Scan using release_idx_name on release (cost=20941.63..20965.76 rows=1 width=4) (actual time=491.891..491.891 rows=0 loops=1)
  237. Index Cond: ((name)::text = 'Tiger'::text)
  238. Filter: (release_group = ANY ($0))
  239. Rows Removed by Filter: 8
  240. Buffers: shared hit=15959
  241. InitPlan 1 (returns $0)
  242. -> Bitmap Heap Scan on release_group (cost=3976.40..20941.20 rows=140384 width=4) (actual time=88.227..303.389 rows=135215 loops=1)
  243. Recheck Cond: ((name)::text < 'Bear'::text)
  244. Heap Blocks: exact=15203
  245. Buffers: shared hit=15949
  246. -> Bitmap Index Scan on release_group_idx_name (cost=0.00..3941.31 rows=140384 width=0) (actual time=84.414..84.414 rows=135215 loops=1)
  247. Index Cond: ((name)::text < 'Bear'::text)
  248. Buffers: shared hit=746
  249. Planning time: 0.413 ms
  250. Execution time: 492.248 ms
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!