explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VanP

Settings
# exclusive inclusive rows x rows loops node
1. 108.943 98,207.062 ↓ 11,856.0 11,856 1

Hash Join (cost=2,133.36..20,000,006,546.73 rows=1 width=792) (actual time=845.235..98,207.062 rows=11,856 loops=1)

  • Hash Cond: (b.offertype_id = ot.id)
  • Buffers: shared hit=7740266
2.          

Initplan (for Hash Join)

3. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on properties prop (cost=0.00..1.15 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (((propkey)::text = 'view.environment'::text) AND (value = 'INT'::text))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
4. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on properties prop_1 (cost=0.00..1.12 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: ((value IS NOT NULL) AND ((propkey)::text = 'view.cn_store_price'::text))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
5. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on properties prop_2 (cost=0.00..1.13 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: ((propkey)::text = 'view.cn_store_price'::text)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=1
6. 24.059 10,380.782 ↓ 11,856.0 11,856 1

Hash Join (cost=2,128.59..6,477.62 rows=1 width=712) (actual time=842.134..10,380.782 rows=11,856 loops=1)

  • Hash Cond: (((b.ag_offer_business_key)::text = (o.offer_number)::text) AND (b.id = ecp.bu_offer_id) AND (b.offertype_id = oto.offertype_id))
  • Buffers: shared hit=992643
7. 14.324 9,692.309 ↓ 7,398.0 7,398 1

Hash Join (cost=105.76..4,454.77 rows=1 width=577) (actual time=177.712..9,692.309 rows=7,398 loops=1)

  • Hash Cond: ((b.bu_business_key)::text = (bu.business_key)::text)
  • Buffers: shared hit=910798
8. 32.815 9,507.357 ↓ 102.8 7,398 1

Seq Scan on bu_offer b (cost=0.00..4,348.73 rows=72 width=50) (actual time=7.072..9,507.357 rows=7,398 loops=1)

  • Filter: (id = (SubPlan 10))
  • Rows Removed by Filter: 7001
  • Buffers: shared hit=903604
9.          

SubPlan (for Seq Scan)

10. 9,474.542 9,474.542 ↑ 1.0 1 14,399

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.658..0.658 rows=1 loops=14,399)

  • Buffers: shared hit=903179
11. 0.241 170.628 ↓ 294.0 294 1

Hash (cost=105.75..105.75 rows=1 width=536) (actual time=170.628..170.628 rows=294 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
  • Buffers: shared hit=7194
12. 0.166 170.387 ↓ 294.0 294 1

Hash Join (cost=12.60..105.75 rows=1 width=536) (actual time=0.890..170.387 rows=294 loops=1)

  • Hash Cond: (c.currency_id = cu.id)
  • Buffers: shared hit=7194
13. 0.179 170.203 ↓ 294.0 294 1

Hash Join (cost=10.48..103.62 rows=1 width=28) (actual time=0.869..170.203 rows=294 loops=1)

  • Hash Cond: ((co.code)::text = (c.code)::text)
  • Buffers: shared hit=7193
14. 0.147 169.999 ↓ 294.0 294 1

Hash Join (cost=7.81..100.95 rows=1 width=20) (actual time=0.837..169.999 rows=294 loops=1)

  • Hash Cond: (bu.brand_for_bu_id = br.id)
  • Buffers: shared hit=7192
15. 0.100 169.847 ↓ 294.0 294 1

Hash Join (cost=6.68..99.81 rows=1 width=28) (actual time=0.823..169.847 rows=294 loops=1)

  • Hash Cond: (co.hub_id = hub.id)
  • Buffers: shared hit=7191
16. 0.178 169.742 ↓ 294.0 294 1

Hash Join (cost=5.61..98.74 rows=1 width=36) (actual time=0.815..169.742 rows=294 loops=1)

  • Hash Cond: (bu.country_id = cl.country_id)
  • Buffers: shared hit=7190
17. 0.373 169.539 ↓ 294.0 294 1

Hash Join (cost=2.67..95.78 rows=1 width=52) (actual time=0.788..169.539 rows=294 loops=1)

  • Hash Cond: (bu.country_id = co.id)
  • Buffers: shared hit=7189
18. 0.385 169.141 ↓ 294.0 294 1

Seq Scan on business_unit bu (cost=0.00..93.12 rows=1 width=41) (actual time=0.758..169.141 rows=294 loops=1)

  • Filter: (id = (SubPlan 9))
  • Buffers: shared hit=7188
19.          

SubPlan (for Seq Scan)

20. 168.756 168.756 ↑ 1.0 1 294

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.574..0.574 rows=1 loops=294)

  • Buffers: shared hit=7175
21. 0.014 0.025 ↑ 1.0 74 1

Hash (cost=1.74..1.74 rows=74 width=19) (actual time=0.025..0.025 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
22. 0.011 0.011 ↑ 1.0 74 1

Seq Scan on country co (cost=0.00..1.74 rows=74 width=19) (actual time=0.004..0.011 rows=74 loops=1)

  • Buffers: shared hit=1
23. 0.009 0.025 ↑ 1.0 74 1

Hash (cost=2.02..2.02 rows=74 width=8) (actual time=0.025..0.025 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
24. 0.016 0.016 ↑ 1.0 74 1

Seq Scan on country_language cl (cost=0.00..2.02 rows=74 width=8) (actual time=0.006..0.016 rows=74 loops=1)

  • Filter: is_default
  • Rows Removed by Filter: 28
  • Buffers: shared hit=1
25. 0.002 0.005 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
26. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on hub (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.003 rows=3 loops=1)

  • Buffers: shared hit=1
27. 0.002 0.005 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.005..0.005 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
28. 0.003 0.003 ↑ 1.0 6 1

Seq Scan on brand_for_bu br (cost=0.00..1.06 rows=6 width=8) (actual time=0.003..0.003 rows=6 loops=1)

  • Buffers: shared hit=1
29. 0.012 0.025 ↑ 1.0 74 1

Hash (cost=1.74..1.74 rows=74 width=11) (actual time=0.025..0.025 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
30. 0.013 0.013 ↑ 1.0 74 1

Seq Scan on country c (cost=0.00..1.74 rows=74 width=11) (actual time=0.005..0.013 rows=74 loops=1)

  • Buffers: shared hit=1
31. 0.009 0.018 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=524) (actual time=0.018..0.018 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
32. 0.009 0.009 ↑ 1.0 50 1

Seq Scan on currency cu (cost=0.00..1.50 rows=50 width=524) (actual time=0.005..0.009 rows=50 loops=1)

  • Buffers: shared hit=1
33. 10.987 664.414 ↓ 25.7 24,093 1

Hash (cost=2,006.45..2,006.45 rows=936 width=157) (actual time=664.414..664.414 rows=24,093 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4778kB
  • Buffers: shared hit=81845
34. 9.615 653.427 ↓ 25.7 24,093 1

Hash Join (cost=1,159.84..2,006.45 rows=936 width=157) (actual time=641.354..653.427 rows=24,093 loops=1)

  • Hash Cond: ((ecp.cpo_business_key)::text = (cpo.business_key)::text)
  • Buffers: shared hit=81845
35. 2.468 2.468 ↑ 1.0 24,093 1

Seq Scan on end_customer_price_offer ecp (cost=0.00..746.93 rows=24,093 width=78) (actual time=0.005..2.468 rows=24,093 loops=1)

  • Buffers: shared hit=506
36. 0.589 641.344 ↓ 37.9 1,705 1

Hash (cost=1,159.28..1,159.28 rows=45 width=106) (actual time=641.344..641.344 rows=1,705 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 253kB
  • Buffers: shared hit=81339
37. 2.333 640.755 ↓ 37.9 1,705 1

Hash Join (cost=612.79..1,159.28 rows=45 width=106) (actual time=636.646..640.755 rows=1,705 loops=1)

  • Hash Cond: (cp.id = cpo.contract_period_id)
  • Buffers: shared hit=81339
38. 1.787 1.787 ↑ 1.0 19,421 1

Seq Scan on contract_period cp (cost=0.00..473.21 rows=19,421 width=34) (actual time=0.005..1.787 rows=19,421 loops=1)

  • Buffers: shared hit=279
39. 0.464 636.635 ↓ 37.9 1,705 1

Hash (cost=612.23..612.23 rows=45 width=88) (actual time=636.635..636.635 rows=1,705 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 207kB
  • Buffers: shared hit=81060
40. 1.361 636.171 ↓ 37.9 1,705 1

Hash Join (cost=375.00..612.23 rows=45 width=88) (actual time=634.073..636.171 rows=1,705 loops=1)

  • Hash Cond: (cpo.offertype_for_offer_id = oto.id)
  • Buffers: shared hit=81060
41. 0.748 0.748 ↑ 1.0 8,712 1

Seq Scan on contract_period_for_offer cpo (cost=0.00..204.12 rows=8,712 width=30) (actual time=0.005..0.748 rows=8,712 loops=1)

  • Buffers: shared hit=117
42. 0.139 634.062 ↓ 37.1 667 1

Hash (cost=374.77..374.77 rows=18 width=74) (actual time=634.062..634.062 rows=667 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
  • Buffers: shared hit=80943
43. 0.544 633.923 ↓ 37.1 667 1

Hash Join (cost=289.03..374.77 rows=18 width=74) (actual time=633.092..633.923 rows=667 loops=1)

  • Hash Cond: (oto.ag_offer_id = o.id)
  • Buffers: shared hit=80943
44. 0.296 0.296 ↑ 1.0 3,541 1

Seq Scan on offertype_for_offer oto (cost=0.00..76.41 rows=3,541 width=24) (actual time=0.004..0.296 rows=3,541 loops=1)

  • Buffers: shared hit=41
45. 0.190 633.083 ↓ 46.4 232 1

Hash (cost=288.96..288.96 rows=5 width=66) (actual time=633.083..633.083 rows=232 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=80902
46. 1.783 632.893 ↓ 46.4 232 1

Seq Scan on ag_offer o (cost=0.00..288.96 rows=5 width=66) (actual time=2.120..632.893 rows=232 loops=1)

  • Filter: (id = (SubPlan 11))
  • Rows Removed by Filter: 733
  • Buffers: shared hit=80902
47.          

SubPlan (for Seq Scan)

48. 631.110 631.110 ↑ 1.0 1 965

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.654..0.654 rows=1 loops=965)

  • Buffers: shared hit=80876
49. 0.004 0.021 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=46) (actual time=0.021..0.021 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
50. 0.017 0.017 ↑ 1.0 16 1

Seq Scan on offertype ot (cost=0.00..1.16 rows=16 width=46) (actual time=0.014..0.017 rows=16 loops=1)

  • Buffers: shared hit=1
51.          

SubPlan (for Hash Join)

52. 0.000 1,683.552 ↓ 0.0 0 11,856

Nested Loop (cost=10,000,000,000.82..10,000,000,032.02 rows=1 width=24) (actual time=0.136..0.142 rows=0 loops=11,856)

  • Buffers: shared hit=191202
53.          

Initplan (for Nested Loop)

54. 1,399.113 1,399.113 ↑ 1.0 1 2,253

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.621..0.621 rows=1 loops=2,253)

  • Buffers: shared hit=137718
55. 1,446.432 1,446.432 ↓ 0.0 0 11,856

Index Scan using marketing_content_business_key_valid_from on marketing_content mc (cost=0.28..2.30 rows=1 width=8) (actual time=0.122..0.122 rows=0 loops=11,856)

  • Index Cond: ((b.offer_business_key)::text = (business_key)::text)
  • Filter: (id = $1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=164166
56. 218.541 218.541 ↑ 1.0 1 2,253

Index Scan using unq_marketing_value_0 on marketing_value mv (cost=0.28..29.45 rows=1 width=32) (actual time=0.064..0.097 rows=1 loops=2,253)

  • Index Cond: (((locale)::text = 'en-xx'::text) AND (marketing_content_id = $1))
  • Buffers: shared hit=27036
57. 0.000 6,894.954 ↑ 1.0 1 9,603

Nested Loop (cost=10,000,000,000.82..10,000,000,032.02 rows=1 width=24) (actual time=0.672..0.718 rows=1 loops=9,603)

  • Buffers: shared hit=739137
58.          

Initplan (for Nested Loop)

59. 5,918.204 5,918.204 ↑ 1.0 1 9,454

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.626..0.626 rows=1 loops=9,454)

  • Buffers: shared hit=586911
60. 5,953.860 5,953.860 ↑ 1.0 1 9,603

Index Scan using marketing_content_business_key_valid_from on marketing_content mc_1 (cost=0.28..2.30 rows=1 width=8) (actual time=0.620..0.620 rows=1 loops=9,603)

  • Index Cond: ((o.offer_number)::text = (business_key)::text)
  • Filter: (id = $3)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=625799
61. 917.038 917.038 ↑ 1.0 1 9,454

Index Scan using unq_marketing_value_0 on marketing_value mv_1 (cost=0.28..29.45 rows=1 width=32) (actual time=0.051..0.097 rows=1 loops=9,454)

  • Index Cond: (((locale)::text = 'en-xx'::text) AND (marketing_content_id = $3))
  • Buffers: shared hit=113338
62. 79,138.800 79,138.800 ↑ 1.0 1 11,856

Function Scan on getwapfor (cost=0.25..0.26 rows=1 width=32) (actual time=6.675..6.675 rows=1 loops=11,856)

  • Buffers: shared hit=5817280
Planning time : 14.359 ms
Execution time : 98,210.069 ms