API tools faq
paste
Advertisement
joseph_pravato

Explain Analyze For Customer & Sales View

joseph_pravato
Feb 27th, 2013
140
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.78 KB | None | 0 0
raw download clone embed print report
  1. Nested Loop Left Join (cost=1428188.58..1453864.89 rows=11009 width=3386) (actual time=23526.187..26190.776 rows=24671 loops=1)
  2. CTE cancel
  3. -> HashAggregate (cost=3.52..6.34 rows=94 width=16) (actual time=0.173..0.252 rows=94 loops=1)
  4. -> Seq Scan on invoice_cancellation (cost=0.00..3.05 rows=95 width=16) (actual time=0.003..0.073 rows=95 loops=1)
  5. CTE invoice_base
  6. -> Nested Loop Left Join (cost=1234.15..16578.19 rows=8224 width=189) (actual time=31.509..1022.927 rows=24671 loops=1)
  7. InitPlan 2 (returns $1)
  8. -> Seq Scan on currency (cost=0.00..0.17 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)
  9. Filter: ((name)::text = 'USD'::text)
  10. Rows Removed by Filter: 1
  11. -> Nested Loop Left Join (cost=1233.99..8437.29 rows=8224 width=174) (actual time=31.464..692.301 rows=24671 loops=1)
  12. Join Filter: ((i.payment_class)::text = 'C'::text)
  13. Rows Removed by Join Filter: 9240
  14. -> Hash Left Join (cost=1233.99..4981.58 rows=8224 width=170) (actual time=31.446..525.563 rows=24671 loops=1)
  15. Hash Cond: (i.fk_coupon = n.id)
  16. -> Hash Left Join (cost=1230.70..4947.42 rows=8224 width=168) (actual time=31.343..483.167 rows=24671 loops=1)
  17. Hash Cond: (i.fk_payment = wp.id)
  18. Join Filter: ((i.payment_class)::text = 'W'::text)
  19. Rows Removed by Join Filter: 2705
  20. -> Hash Join (cost=1192.67..4867.64 rows=8224 width=164) (actual time=30.309..438.810 rows=24671 loops=1)
  21. Hash Cond: (i.fk_currency = c.id)
  22. Join Filter: ((i.entered >= e.start_date) AND ((i.entered <= e.end_date) OR (e.end_date IS NULL)))
  23. -> Hash Left Join (cost=1192.12..3911.08 rows=24671 width=160) (actual time=30.247..382.012 rows=24671 loops=1)
  24. Hash Cond: (i.fk_payment = vp.id)
  25. Join Filter: ((i.payment_class)::text = 'V'::text)
  26. Rows Removed by Join Filter: 1958
  27. -> Hash Left Join (cost=1166.32..3770.60 rows=24671 width=156) (actual time=29.587..339.361 rows=24671 loops=1)
  28. Hash Cond: (i.fk_payment = pp.id)
  29. Join Filter: ((i.payment_class)::text = 'N'::text)
  30. Rows Removed by Join Filter: 7557
  31. -> Hash Left Join (cost=912.41..3113.12 rows=24671 width=152) (actual time=23.043..283.752 rows=24671 loops=1)
  32. Hash Cond: (i.fk_payment = op.id)
  33. Join Filter: ((i.payment_class)::text = 'O'::text)
  34. -> Hash Left Join (cost=885.78..2971.69 rows=24671 width=148) (actual time=23.032..241.586 rows=24671 loops=1)
  35. Hash Cond: (i.fk_payment = ip.id)
  36. Join Filter: ((i.payment_class)::text = 'I'::text)
  37. Rows Removed by Join Filter: 6500
  38. -> Hash Left Join (cost=750.51..2596.64 rows=24671 width=144) (actual time=19.589..191.722 rows=24671 loops=1)
  39. Hash Cond: (i.fk_payment = k.id)
  40. Join Filter: ((i.payment_class)::text = 'K'::text)
  41. Rows Removed by Join Filter: 3286
  42. -> Hash Left Join (cost=682.01..2376.93 rows=24671 width=140) (actual time=17.876..146.039 rows=24671 loops=1)
  43. Hash Cond: (i.fk_payment = p.id)
  44. Join Filter: ((i.payment_class)::text = 'P'::text)
  45. Rows Removed by Join Filter: 6001
  46. -> Hash Left Join (cost=8.70..912.66 rows=24671 width=132) (actual time=0.493..76.516 rows=24671 loops=1)
  47. Hash Cond: (i.id = l.fk_invoice)
  48. -> Seq Scan on invoice i (cost=0.00..808.63 rows=24671 width=124) (actual time=0.003..25.578 rows=24671 loops=1)
  49. Filter: active
  50. -> Hash (cost=5.64..5.64 rows=94 width=16) (actual time=0.482..0.482 rows=94 loops=1)
  51. Buckets: 1024 Batches: 1 Memory Usage: 5kB
  52. -> CTE Scan on cancel l (cost=0.00..5.64 rows=94 width=16) (actual time=0.176..0.402 rows=94 loops=1)
  53. -> Hash (cost=329.43..329.43 rows=10581 width=16) (actual time=17.375..17.375 rows=10581 loops=1)
  54. Buckets: 2048 Batches: 1 Memory Usage: 496kB
  55. -> Seq Scan on po_payment p (cost=0.00..329.43 rows=10581 width=16) (actual time=0.002..8.504 rows=10581 loops=1)
  56. -> Hash (cost=33.40..33.40 rows=1080 width=12) (actual time=1.702..1.702 rows=1080 loops=1)
  57. Buckets: 1024 Batches: 1 Memory Usage: 43kB
  58. -> Seq Scan on check_payment k (cost=0.00..33.40 rows=1080 width=12) (actual time=0.003..0.835 rows=1080 loops=1)
  59. -> Hash (cost=65.66..65.66 rows=2142 width=12) (actual time=3.430..3.430 rows=2142 loops=1)
  60. Buckets: 1024 Batches: 1 Memory Usage: 101kB
  61. -> Seq Scan on invoice_payment ip (cost=0.00..65.66 rows=2142 width=12) (actual time=0.003..1.656 rows=2142 loops=1)
  62. -> Hash (cost=13.30..13.30 rows=410 width=12) (actual time=0.003..0.003 rows=0 loops=1)
  63. Buckets: 1024 Batches: 1 Memory Usage: 0kB
  64. -> Seq Scan on online_payment op (cost=0.00..13.30 rows=410 width=12) (actual time=0.001..0.001 rows=0 loops=1)
  65. -> Hash (cost=123.23..123.23 rows=4021 width=12) (actual time=6.533..6.533 rows=4021 loops=1)
  66. Buckets: 1024 Batches: 1 Memory Usage: 158kB
  67. -> Seq Scan on promo_payment pp (cost=0.00..123.23 rows=4021 width=12) (actual time=0.003..3.329 rows=4021 loops=1)
  68. -> Hash (cost=12.54..12.54 rows=408 width=12) (actual time=0.649..0.649 rows=408 loops=1)
  69. Buckets: 1024 Batches: 1 Memory Usage: 20kB
  70. -> Seq Scan on var_money_payment vp (cost=0.00..12.54 rows=408 width=12) (actual time=0.004..0.307 rows=408 loops=1)
  71. -> Hash (cost=0.49..0.49 rows=2 width=36) (actual time=0.051..0.051 rows=2 loops=1)
  72. Buckets: 1024 Batches: 1 Memory Usage: 1kB
  73. -> Merge Join (cost=0.41..0.49 rows=2 width=36) (actual time=0.039..0.047 rows=2 loops=1)
  74. Merge Cond: (e.fk_from_currency = c.id)
  75. -> Sort (cost=0.24..0.25 rows=2 width=24) (actual time=0.026..0.028 rows=2 loops=1)
  76. Sort Key: e.fk_from_currency
  77. Sort Method: quicksort Memory: 25kB
  78. -> Seq Scan on exchange_rate e (cost=0.00..0.23 rows=2 width=24) (actual time=0.015..0.018 rows=2 loops=1)
  79. Filter: (fk_to_currency = $1)
  80. Rows Removed by Filter: 2
  81. -> Sort (cost=0.17..0.18 rows=2 width=12) (actual time=0.009..0.010 rows=2 loops=1)
  82. Sort Key: c.id
  83. Sort Method: quicksort Memory: 25kB
  84. -> Seq Scan on currency c (cost=0.00..0.16 rows=2 width=12) (actual time=0.001..0.002 rows=2 loops=1)
  85. -> Hash (cost=18.46..18.46 rows=602 width=12) (actual time=1.023..1.023 rows=602 loops=1)
  86. Buckets: 1024 Batches: 1 Memory Usage: 29kB
  87. -> Seq Scan on wire_payment wp (cost=0.00..18.46 rows=602 width=12) (actual time=0.003..0.529 rows=602 loops=1)
  88. -> Hash (cost=1.63..1.63 rows=51 width=18) (actual time=0.094..0.094 rows=53 loops=1)
  89. Buckets: 1024 Batches: 1 Memory Usage: 3kB
  90. -> Seq Scan on coupon n (cost=0.00..1.63 rows=51 width=18) (actual time=0.005..0.050 rows=53 loops=1)
  91. -> Index Scan using credit_card_pkey on credit_card d (cost=0.00..0.39 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=24671)
  92. Index Cond: (i.fk_payment = id)
  93. -> Index Scan using contact_pkey on contact v (cost=0.00..0.89 rows=1 width=23) (actual time=0.003..0.003 rows=0 loops=24671)
  94. Index Cond: (i.fk_sold_by = id)
  95. Filter: (fk_var_info IS NOT NULL)
  96. Rows Removed by Filter: 0
  97. -> Nested Loop Left Join (cost=1411604.05..1433148.49 rows=11009 width=3382) (actual time=23526.164..26025.385 rows=24671 loops=1)
  98. -> Nested Loop Left Join (cost=1411604.05..1429652.48 rows=11009 width=3364) (actual time=23526.159..25927.181 rows=24671 loops=1)
  99. -> Hash Join (cost=1411604.05..1418444.76 rows=11009 width=3340) (actual time=23526.002..25265.529 rows=24671 loops=1)
  100. Hash Cond: (i.fk_contact = u.id)
  101. -> Nested Loop Left Join (cost=0.00..3978.61 rows=8224 width=3186) (actual time=31.544..1500.552 rows=24671 loops=1)
  102. Join Filter: (i.fk_handled_by = o.id)
  103. Rows Removed by Join Filter: 65397
  104. -> Nested Loop Left Join (cost=0.00..3114.89 rows=8224 width=3150) (actual time=31.526..1307.632 rows=24671 loops=1)
  105. -> CTE Scan on invoice_base i (cost=0.00..493.44 rows=8224 width=3143) (actual time=31.516..1111.758 rows=24671 loops=1)
  106. -> Index Scan using employee_pkey on employee m (cost=0.00..0.29 rows=1 width=23) (actual time=0.003..0.004 rows=1 loops=24671)
  107. Index Cond: (i.fk_sold_by = id)
  108. -> Materialize (cost=0.00..0.21 rows=3 width=52) (actual time=0.001..0.003 rows=3 loops=24671)
  109. -> Seq Scan on office o (cost=0.00..0.19 rows=3 width=52) (actual time=0.002..0.005 rows=3 loops=1)
  110. -> Hash (cost=1384787.79..1384787.79 rows=769614 width=162) (actual time=23494.415..23494.415 rows=574945 loops=1)
  111. Buckets: 16384 Batches: 8 Memory Usage: 13805kB
  112. -> Hash Right Join (cost=38280.48..1384787.79 rows=769614 width=162) (actual time=1286.273..22552.375 rows=574945 loops=1)
  113. Hash Cond: (a.id = u.fk_main_addr)
  114. -> Nested Loop Left Join (cost=126.80..1314593.69 rows=792387 width=87) (actual time=3.525..19373.239 rows=574928 loops=1)
  115. Join Filter: (((a.fk_location = r.id) AND ((a.location_class)::text = 'R'::text)) OR (c.fk_region = r.id))
  116. Rows Removed by Join Filter: 3449568
  117. -> Nested Loop Left Join (cost=126.80..1092725.01 rows=792387 width=87) (actual time=3.493..11482.670 rows=574928 loops=1)
  118. -> Hash Left Join (cost=126.15..84345.73 rows=574925 width=68) (actual time=3.463..3769.527 rows=574928 loops=1)
  119. Hash Cond: (a.fk_location = s.id)
  120. Join Filter: ((a.location_class)::text = 'S'::text)
  121. Rows Removed by Join Filter: 125366
  122. -> Merge Join (cost=117.26..70428.74 rows=574925 width=50) (actual time=3.200..2696.250 rows=574928 loops=1)
  123. Merge Cond: (cam.address_id = a.id)
  124. -> Index Only Scan using contact_address_map_address_id_key on contact_address_map cam (cost=0.00..21701.64 rows=574925 width=8) (actual time=0.018..508.352 rows=574928 loops=1)
  125. Heap Fetches: 133
  126. -> Index Scan using address_pkey on address a (cost=0.00..29357.47 rows=591955 width=50) (actual time=0.012..664.367 rows=591959 loops=1)
  127. -> Hash (cost=4.37..4.37 rows=139 width=26) (actual time=0.235..0.235 rows=139 loops=1)
  128. Buckets: 1024 Batches: 1 Memory Usage: 9kB
  129. -> Seq Scan on states s (cost=0.00..4.37 rows=139 width=26) (actual time=0.004..0.112 rows=139 loops=1)
  130. -> Bitmap Heap Scan on country c (cost=0.65..1.72 rows=1 width=27) (actual time=0.008..0.009 rows=1 loops=574928)
  131. Recheck Cond: ((a.fk_location = id) OR (s.fk_country = id))
  132. Filter: (((a.fk_location = id) AND ((a.location_class)::text = 'C'::text)) OR (s.fk_country = id))
  133. Rows Removed by Filter: 1
  134. -> BitmapOr (cost=0.65..0.65 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=574928)
  135. -> Bitmap Index Scan on country_pkey (cost=0.00..0.26 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=574928)
  136. Index Cond: (a.fk_location = id)
  137. -> Bitmap Index Scan on country_pkey (cost=0.00..0.27 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=574928)
  138. Index Cond: (s.fk_country = id)
  139. -> Materialize (cost=0.00..0.34 rows=7 width=18) (actual time=0.001..0.006 rows=7 loops=574928)
  140. -> Seq Scan on region r (cost=0.00..0.31 rows=7 width=18) (actual time=0.003..0.010 rows=7 loops=1)
  141. -> Hash (cost=18625.76..18625.76 rows=574942 width=91) (actual time=1282.474..1282.474 rows=574945 loops=1)
  142. Buckets: 32768 Batches: 4 Memory Usage: 16801kB
  143. -> Seq Scan on contact u (cost=0.00..18625.76 rows=574942 width=91) (actual time=0.003..615.269 rows=574945 loops=1)
  144. -> Nested Loop (cost=0.00..0.99 rows=1 width=40) (actual time=0.017..0.023 rows=1 loops=24671)
  145. -> Nested Loop (cost=0.00..0.67 rows=1 width=65) (actual time=0.011..0.015 rows=1 loops=24671)
  146. -> Index Scan using phone_pkey on phone p (cost=0.00..0.32 rows=1 width=65) (actual time=0.005..0.006 rows=1 loops=24671)
  147. Index Cond: (id = u.fk_main_phone)
  148. -> Index Only Scan using contact_phone_number_map_phone_number_id_key on contact_phone_number_map cpm (cost=0.00..0.31 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=24671)
  149. Index Cond: (phone_number_id = p.id)
  150. Heap Fetches: 22
  151. -> Index Scan using country_pkey on country c (cost=0.00..0.29 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=24671)
  152. Index Cond: (id = p.fk_country)
  153. -> Index Scan using account_pkey on account a (cost=0.00..0.29 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=24671)
  154. Index Cond: (id = u.fk_account)
  155. -> Index Scan using payer_pkey on payer y (cost=0.00..0.29 rows=1 width=20) (actual time=0.001..0.002 rows=0 loops=24671)
  156. Index Cond: (u.fk_payer = id)
  157. Total runtime: 26210.149 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!