explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wOX1

Settings
# exclusive inclusive rows x rows loops node
1. 92,184.527 488,946.423 ↓ 1.1 3,964,297 1

Sort (cost=2,862,569.70..2,871,695.09 rows=3,650,154 width=391) (actual time=457,668.598..488,946.423 rows=3,964,297 loops=1)

  • Sort Key: tbl_offer_off.off_reference_str
  • Sort Method: external merge Disk: 1508016kB
2.          

Initplan (for Sort)

3. 0.037 0.101 ↑ 1.0 1 1

Result (cost=0.34..0.35 rows=1 width=0) (actual time=0.091..0.101 rows=1 loops=1)

4.          

Initplan (for Result)

5. 0.036 0.064 ↑ 1.0 1 1

Limit (cost=0.29..0.34 rows=1 width=8) (actual time=0.054..0.064 rows=1 loops=1)

6. 0.028 0.028 ↑ 16,751.0 1 1

Index Only Scan Backward using uk_loy22lc77fxgjol9o4utu4n19 on tbl_exchange_rate_exr exr_max (cost=0.29..923.88 rows=16,751 width=8) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: (exr_dtm IS NOT NULL)
  • Heap Fetches: 1
7. 52,389.958 396,761.795 ↓ 1.1 3,964,297 1

Hash Join (cost=2,102.32..1,167,191.21 rows=3,650,154 width=391) (actual time=912.155..396,761.795 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_buyer_program_bpr.bpr_cha_int_fk = tbl_channel_cha.cha_id_int)
8. 52,105.263 344,371.521 ↓ 1.1 3,964,297 1

Hash Join (cost=2,100.98..1,117,000.26 rows=3,650,154 width=359) (actual time=911.793..344,371.521 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_buyer_program_bpr.bpr_buy_int_fk = tbl_buyer_buy.buy_id_int)
9. 51,131.665 292,256.168 ↓ 1.1 3,964,297 1

Hash Left Join (cost=2,087.95..1,066,797.61 rows=3,650,154 width=324) (actual time=901.648..292,256.168 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_offer_off.off_bus_int_fk = tbl_business_unit_bus.bus_id_int)
10. 52,604.118 241,120.599 ↓ 1.1 3,964,297 1

Hash Left Join (cost=2,079.59..1,035,716.71 rows=3,650,154 width=311) (actual time=897.705..241,120.599 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_offer_off.off_agg_int_fk = tbl_offer_aggregate_agg.agg_id_int)
11. 52,448.033 188,086.100 ↓ 1.1 3,964,297 1

Hash Join (cost=1,046.39..961,800.88 rows=3,650,154 width=271) (actual time=467.276..188,086.100 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_offer_off.off_cur_int_fk = tbl_currency_cur.cur_id_int)
12. 51,684.936 135,636.560 ↓ 1.1 3,964,297 1

Merge Join (cost=1,006.05..911,570.93 rows=3,650,154 width=257) (actual time=465.704..135,636.560 rows=3,964,297 loops=1)

  • Merge Cond: (tjnct_sup_bpr_sbp.sbp_sup_int_fk = tbl_offer_off.off_sbp_int_fk)
13. 178.010 782.884 ↓ 1.0 9,467 1

Merge Join (cost=994.24..1,582.92 rows=9,466 width=97) (actual time=459.540..782.884 rows=9,467 loops=1)

  • Merge Cond: (tbl_supplier_sup.sup_id_int = tjnct_sup_bpr_sbp.sbp_sup_int_fk)
14. 80.386 80.386 ↑ 1.0 7,151 1

Index Scan using pk_sup on tbl_supplier_sup (cost=0.28..429.31 rows=7,215 width=32) (actual time=0.024..80.386 rows=7,151 loops=1)

15. 179.580 524.488 ↓ 1.0 9,467 1

Sort (cost=993.96..1,017.62 rows=9,466 width=65) (actual time=459.485..524.488 rows=9,467 loops=1)

  • Sort Key: tjnct_sup_bpr_sbp.sbp_sup_int_fk
  • Sort Method: quicksort Memory: 1694kB
16. 217.912 344.908 ↓ 1.0 9,467 1

Hash Join (cost=35.98..368.80 rows=9,466 width=65) (actual time=20.259..344.908 rows=9,467 loops=1)

  • Hash Cond: (tjnct_sup_bpr_sbp.sbp_bpr_int_fk = tbl_buyer_program_bpr.bpr_id_int)
17. 106.810 106.810 ↓ 1.0 9,467 1

Seq Scan on tjnct_sup_bpr_sbp (cost=0.00..202.66 rows=9,466 width=16) (actual time=0.017..106.810 rows=9,467 loops=1)

18. 11.055 20.186 ↑ 1.0 888 1

Hash (cost=24.88..24.88 rows=888 width=57) (actual time=20.186..20.186 rows=888 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 82kB
19. 9.131 9.131 ↑ 1.0 888 1

Seq Scan on tbl_buyer_program_bpr (cost=0.00..24.88 rows=888 width=57) (actual time=0.015..9.131 rows=888 loops=1)

20. 47,805.696 83,168.740 ↓ 1.3 4,010,550 1

Materialize (cost=0.56..879,254.49 rows=3,050,914 width=176) (actual time=0.040..83,168.740 rows=4,010,550 loops=1)

21. 35,363.044 35,363.044 ↑ 1.0 2,996,361 1

Index Scan using idx_offer_duplication_check on tbl_offer_off (cost=0.56..871,627.21 rows=3,050,914 width=176) (actual time=0.018..35,363.044 rows=2,996,361 loops=1)

22. 0.208 1.507 ↑ 1.0 21 1

Hash (cost=40.07..40.07 rows=21 width=30) (actual time=1.507..1.507 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
23. 0.490 1.299 ↑ 1.0 21 1

Hash Right Join (cost=1.76..40.07 rows=21 width=30) (actual time=0.679..1.299 rows=21 loops=1)

  • Hash Cond: (exr.exr_cur_int_fk = tbl_currency_cur.cur_id_int)
24. 0.312 0.312 ↓ 1.1 20 1

Index Scan using uk_loy22lc77fxgjol9o4utu4n19 on tbl_exchange_rate_exr exr (cost=0.29..38.35 rows=18 width=26) (actual time=0.137..0.312 rows=20 loops=1)

  • Index Cond: (exr_dtm = $1)
25. 0.292 0.497 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=12) (actual time=0.497..0.497 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
26. 0.205 0.205 ↑ 1.0 21 1

Seq Scan on tbl_currency_cur (cost=0.00..1.21 rows=21 width=12) (actual time=0.019..0.205 rows=21 loops=1)

27. 219.706 430.381 ↓ 1.0 24,109 1

Hash (cost=731.98..731.98 rows=24,098 width=48) (actual time=430.381..430.381 rows=24,109 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 2072kB
28. 210.675 210.675 ↓ 1.0 24,109 1

Seq Scan on tbl_offer_aggregate_agg (cost=0.00..731.98 rows=24,098 width=48) (actual time=0.007..210.675 rows=24,109 loops=1)

29. 2.044 3.904 ↑ 1.0 238 1

Hash (cost=5.38..5.38 rows=238 width=29) (actual time=3.904..3.904 rows=238 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
30. 1.860 1.860 ↑ 1.0 238 1

Seq Scan on tbl_business_unit_bus (cost=0.00..5.38 rows=238 width=29) (actual time=0.014..1.860 rows=238 loops=1)

31. 5.085 10.090 ↑ 1.0 357 1

Hash (cost=8.57..8.57 rows=357 width=43) (actual time=10.090..10.090 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
32. 5.005 5.005 ↑ 1.0 357 1

Seq Scan on tbl_buyer_buy (cost=0.00..8.57 rows=357 width=43) (actual time=0.026..5.005 rows=357 loops=1)

33. 0.161 0.316 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=40) (actual time=0.316..0.316 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.155 0.155 ↑ 1.0 15 1

Seq Scan on tbl_channel_cha (cost=0.00..1.15 rows=15 width=40) (actual time=0.023..0.155 rows=15 loops=1)

Planning time : 5.367 ms
Execution time : 514,493.932 ms