API tools faq
paste
Advertisement
cmptrwz

Explain output for LP1400376

cmptrwz
Dec 11th, 2014
207
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.85 KB | None | 0 0
raw download clone embed print report
  1. -------------------------------------------------------------------------------
  2. CURRENT MASTER (LEFT JOINs, no check for null ATTR in next function up)
  3. -------------------------------------------------------------------------------
  4. QUERY PLAN
  5. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  6. Aggregate (cost=141.39..141.40 rows=1 width=83) (actual time=1.040..1.040 rows=1 loops=1)
  7. -> Hash Right Join (cost=124.10..141.34 rows=20 width=83) (actual time=0.360..0.755 rows=17 loops=1)
  8. Hash Cond: ((cvm.ctype = mra.attr) AND (cvm.code = mra.value))
  9. -> Seq Scan on coded_value_map cvm (cost=0.00..12.42 rows=642 width=29) (actual time=0.006..0.135 rows=642 loops=1)
  10. -> Hash (cost=123.80..123.80 rows=20 width=68) (actual time=0.334..0.334 rows=17 loops=1)
  11. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  12. -> Hash Join (cost=122.16..123.80 rows=20 width=68) (actual time=0.288..0.320 rows=17 loops=1)
  13. Hash Cond: (rad.name = mra.attr)
  14. -> Seq Scan on record_attr_definition rad (cost=0.00..1.32 rows=32 width=36) (actual time=0.007..0.016 rows=35 loops=1)
  15. -> Hash (cost=121.91..121.91 rows=20 width=64) (actual time=0.254..0.254 rows=17 loops=1)
  16. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  17. -> Subquery Scan on mra (cost=121.51..121.91 rows=20 width=64) (actual time=0.230..0.241 rows=17 loops=1)
  18. -> HashAggregate (cost=121.51..121.71 rows=20 width=20) (actual time=0.229..0.234 rows=17 loops=1)
  19. -> Append (cost=42.63..121.36 rows=20 width=20) (actual time=0.112..0.205 rows=17 loops=1)
  20. -> Nested Loop Left Join (cost=42.63..87.70 rows=10 width=17) (actual time=0.111..0.126 rows=9 loops=1)
  21. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.014..0.015 rows=1 loops=1)
  22. Index Cond: (source = 1574642)
  23. -> Bitmap Heap Scan on uncontrolled_record_attr_value m (cost=42.63..78.93 rows=10 width=17) (actual time=0.091..0.104 rows=9 loops=1)
  24. Recheck Cond: (id = ANY (v.vlist))
  25. -> Bitmap Index Scan on uncontrolled_record_attr_value_pkey (cost=0.00..42.63 rows=10 width=0) (actual time=0.079..0.079 rows=9 loops=1)
  26. Index Cond: (id = ANY (v.vlist))
  27. -> Nested Loop Left Join (cost=18.59..33.46 rows=10 width=22) (actual time=0.064..0.073 rows=8 loops=1)
  28. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.006..0.007 rows=1 loops=1)
  29. Index Cond: (source = 1574642)
  30. -> Bitmap Heap Scan on coded_value_map c (cost=18.59..24.69 rows=10 width=18) (actual time=0.054..0.058 rows=8 loops=1)
  31. Recheck Cond: (id = ANY (v.vlist))
  32. -> Bitmap Index Scan on coded_value_map_pkey (cost=0.00..18.59 rows=10 width=0) (actual time=0.044..0.044 rows=8 loops=1)
  33. Index Cond: (id = ANY (v.vlist))
  34. Total runtime: 1.183 ms
  35. (29 rows)
  36.  
  37. -------------------------------------------------------------------------------
  38. REPLACE LEFT JOINS WITH INNER JOINS
  39. -------------------------------------------------------------------------------
  40. QUERY PLAN
  41. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  42. Aggregate (cost=141.39..141.40 rows=1 width=83) (actual time=0.968..0.968 rows=1 loops=1)
  43. -> Hash Right Join (cost=124.10..141.34 rows=20 width=83) (actual time=0.312..0.709 rows=17 loops=1)
  44. Hash Cond: ((cvm.ctype = mra.attr) AND (cvm.code = mra.value))
  45. -> Seq Scan on coded_value_map cvm (cost=0.00..12.42 rows=642 width=29) (actual time=0.005..0.108 rows=642 loops=1)
  46. -> Hash (cost=123.80..123.80 rows=20 width=68) (actual time=0.286..0.286 rows=17 loops=1)
  47. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  48. -> Hash Join (cost=122.16..123.80 rows=20 width=68) (actual time=0.240..0.269 rows=17 loops=1)
  49. Hash Cond: (rad.name = mra.attr)
  50. -> Seq Scan on record_attr_definition rad (cost=0.00..1.32 rows=32 width=36) (actual time=0.006..0.012 rows=35 loops=1)
  51. -> Hash (cost=121.91..121.91 rows=20 width=64) (actual time=0.223..0.223 rows=17 loops=1)
  52. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  53. -> Subquery Scan on mra (cost=121.51..121.91 rows=20 width=64) (actual time=0.199..0.210 rows=17 loops=1)
  54. -> HashAggregate (cost=121.51..121.71 rows=20 width=20) (actual time=0.199..0.206 rows=17 loops=1)
  55. -> Append (cost=42.63..121.36 rows=20 width=20) (actual time=0.094..0.178 rows=17 loops=1)
  56. -> Nested Loop (cost=42.63..87.70 rows=10 width=17) (actual time=0.094..0.110 rows=9 loops=1)
  57. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.014..0.015 rows=1 loops=1)
  58. Index Cond: (source = 1574642)
  59. -> Bitmap Heap Scan on uncontrolled_record_attr_value m (cost=42.63..78.93 rows=10 width=17) (actual time=0.074..0.085 rows=9 loops=1)
  60. Recheck Cond: (id = ANY (v.vlist))
  61. -> Bitmap Index Scan on uncontrolled_record_attr_value_pkey (cost=0.00..42.63 rows=10 width=0) (actual time=0.065..0.065 rows=9 loops=1)
  62. Index Cond: (id = ANY (v.vlist))
  63. -> Nested Loop (cost=18.59..33.46 rows=10 width=22) (actual time=0.053..0.061 rows=8 loops=1)
  64. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.006..0.006 rows=1 loops=1)
  65. Index Cond: (source = 1574642)
  66. -> Bitmap Heap Scan on coded_value_map c (cost=18.59..24.69 rows=10 width=18) (actual time=0.044..0.051 rows=8 loops=1)
  67. Recheck Cond: (id = ANY (v.vlist))
  68. -> Bitmap Index Scan on coded_value_map_pkey (cost=0.00..18.59 rows=10 width=0) (actual time=0.038..0.038 rows=8 loops=1)
  69. Index Cond: (id = ANY (v.vlist))
  70. Total runtime: 1.109 ms
  71. (29 rows)
  72.  
  73. -------------------------------------------------------------------------------
  74. ADD CHECK FOR NULL ATTR IN RECORD_ATTR VIEW (WITH LEFT JOINS)
  75. -------------------------------------------------------------------------------
  76. QUERY PLAN
  77. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  78. Aggregate (cost=141.39..141.40 rows=1 width=83) (actual time=0.968..0.968 rows=1 loops=1)
  79. -> Hash Right Join (cost=124.10..141.34 rows=20 width=83) (actual time=0.311..0.706 rows=17 loops=1)
  80. Hash Cond: ((cvm.ctype = mra.attr) AND (cvm.code = mra.value))
  81. -> Seq Scan on coded_value_map cvm (cost=0.00..12.42 rows=642 width=29) (actual time=0.006..0.109 rows=642 loops=1)
  82. -> Hash (cost=123.80..123.80 rows=20 width=68) (actual time=0.285..0.285 rows=17 loops=1)
  83. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  84. -> Hash Join (cost=122.16..123.80 rows=20 width=68) (actual time=0.239..0.269 rows=17 loops=1)
  85. Hash Cond: (rad.name = mra.attr)
  86. -> Seq Scan on record_attr_definition rad (cost=0.00..1.32 rows=32 width=36) (actual time=0.006..0.012 rows=35 loops=1)
  87. -> Hash (cost=121.91..121.91 rows=20 width=64) (actual time=0.221..0.221 rows=17 loops=1)
  88. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  89. -> Subquery Scan on mra (cost=121.51..121.91 rows=20 width=64) (actual time=0.198..0.211 rows=17 loops=1)
  90. -> HashAggregate (cost=121.51..121.71 rows=20 width=20) (actual time=0.197..0.203 rows=17 loops=1)
  91. -> Append (cost=42.63..121.36 rows=20 width=20) (actual time=0.092..0.177 rows=17 loops=1)
  92. -> Nested Loop Left Join (cost=42.63..87.70 rows=10 width=17) (actual time=0.092..0.111 rows=9 loops=1)
  93. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.013..0.014 rows=1 loops=1)
  94. Index Cond: (source = 1574642)
  95. -> Bitmap Heap Scan on uncontrolled_record_attr_value m (cost=42.63..78.93 rows=10 width=17) (actual time=0.073..0.084 rows=9 loops=1)
  96. Recheck Cond: (id = ANY (v.vlist))
  97. -> Bitmap Index Scan on uncontrolled_record_attr_value_pkey (cost=0.00..42.63 rows=10 width=0) (actual time=0.064..0.064 rows=9 loops=1)
  98. Index Cond: (id = ANY (v.vlist))
  99. -> Nested Loop Left Join (cost=18.59..33.46 rows=10 width=22) (actual time=0.053..0.062 rows=8 loops=1)
  100. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.006..0.006 rows=1 loops=1)
  101. Index Cond: (source = 1574642)
  102. -> Bitmap Heap Scan on coded_value_map c (cost=18.59..24.69 rows=10 width=18) (actual time=0.043..0.049 rows=8 loops=1)
  103. Recheck Cond: (id = ANY (v.vlist))
  104. -> Bitmap Index Scan on coded_value_map_pkey (cost=0.00..18.59 rows=10 width=0) (actual time=0.038..0.038 rows=8 loops=1)
  105. Index Cond: (id = ANY (v.vlist))
  106. Total runtime: 1.110 ms
  107. (29 rows)
  108.  
  109. -------------------------------------------------------------------------------
  110. REMOVE LEFT JOINS AND ADD CHECK FOR NULL ATTR IN RECORD_ATTR VIEW
  111. -------------------------------------------------------------------------------
  112. QUERY PLAN
  113. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  114. Aggregate (cost=141.39..141.40 rows=1 width=83) (actual time=0.982..0.982 rows=1 loops=1)
  115. -> Hash Right Join (cost=124.10..141.34 rows=20 width=83) (actual time=0.324..0.717 rows=17 loops=1)
  116. Hash Cond: ((cvm.ctype = mra.attr) AND (cvm.code = mra.value))
  117. -> Seq Scan on coded_value_map cvm (cost=0.00..12.42 rows=642 width=29) (actual time=0.006..0.111 rows=642 loops=1)
  118. -> Hash (cost=123.80..123.80 rows=20 width=68) (actual time=0.298..0.298 rows=17 loops=1)
  119. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  120. -> Hash Join (cost=122.16..123.80 rows=20 width=68) (actual time=0.254..0.283 rows=17 loops=1)
  121. Hash Cond: (rad.name = mra.attr)
  122. -> Seq Scan on record_attr_definition rad (cost=0.00..1.32 rows=32 width=36) (actual time=0.006..0.013 rows=35 loops=1)
  123. -> Hash (cost=121.91..121.91 rows=20 width=64) (actual time=0.232..0.232 rows=17 loops=1)
  124. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  125. -> Subquery Scan on mra (cost=121.51..121.91 rows=20 width=64) (actual time=0.209..0.221 rows=17 loops=1)
  126. -> HashAggregate (cost=121.51..121.71 rows=20 width=20) (actual time=0.208..0.214 rows=17 loops=1)
  127. -> Append (cost=42.63..121.36 rows=20 width=20) (actual time=0.096..0.187 rows=17 loops=1)
  128. -> Nested Loop (cost=42.63..87.70 rows=10 width=17) (actual time=0.096..0.115 rows=9 loops=1)
  129. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.013..0.014 rows=1 loops=1)
  130. Index Cond: (source = 1574642)
  131. -> Bitmap Heap Scan on uncontrolled_record_attr_value m (cost=42.63..78.93 rows=10 width=17) (actual time=0.078..0.091 rows=9 loops=1)
  132. Recheck Cond: (id = ANY (v.vlist))
  133. -> Bitmap Index Scan on uncontrolled_record_attr_value_pkey (cost=0.00..42.63 rows=10 width=0) (actual time=0.069..0.069 rows=9 loops=1)
  134. Index Cond: (id = ANY (v.vlist))
  135. -> Nested Loop (cost=18.59..33.46 rows=10 width=22) (actual time=0.058..0.067 rows=8 loops=1)
  136. -> Index Scan using record_attr_vector_list_pkey on record_attr_vector_list v (cost=0.00..8.55 rows=1 width=91) (actual time=0.006..0.007 rows=1 loops=1)
  137. Index Cond: (source = 1574642)
  138. -> Bitmap Heap Scan on coded_value_map c (cost=18.59..24.69 rows=10 width=18) (actual time=0.049..0.055 rows=8 loops=1)
  139. Recheck Cond: (id = ANY (v.vlist))
  140. -> Bitmap Index Scan on coded_value_map_pkey (cost=0.00..18.59 rows=10 width=0) (actual time=0.042..0.042 rows=8 loops=1)
  141. Index Cond: (id = ANY (v.vlist))
  142. Total runtime: 1.133 ms
  143. (29 rows)
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!