explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HG7R

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 422,691.195 ↑ 1.0 10 1

Limit (cost=1,248,718.99..1,248,719.02 rows=10 width=391) (actual time=422,691.013..422,691.195 rows=10 loops=1)

2.          

Initplan (for Limit)

3. 0.049 0.172 ↑ 1.0 1 1

Result (cost=0.34..0.35 rows=1 width=0) (actual time=0.163..0.172 rows=1 loops=1)

4.          

Initplan (for Result)

5. 0.034 0.123 ↑ 1.0 1 1

Limit (cost=0.29..0.34 rows=1 width=8) (actual time=0.113..0.123 rows=1 loops=1)

6. 0.089 0.089 ↑ 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.089..0.089 rows=1 loops=1)

  • Index Cond: (exr_dtm IS NOT NULL)
  • Heap Fetches: 1
7. 30,211.886 422,691.047 ↑ 365,015.4 10 1

Sort (cost=1,248,718.64..1,257,844.03 rows=3,650,154 width=391) (actual time=422,690.988..422,691.047 rows=10 loops=1)

  • Sort Key: tbl_offer_off.off_reference_str
  • Sort Method: top-N heapsort Memory: 30kB
8. 52,867.855 392,479.161 ↓ 1.1 3,964,297 1

Hash Join (cost=1,175.87..1,169,840.13 rows=3,650,154 width=391) (actual time=507.480..392,479.161 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_buyer_program_bpr.bpr_cha_int_fk = tbl_channel_cha.cha_id_int)
9. 52,987.975 339,610.860 ↓ 1.1 3,964,297 1

Hash Join (cost=1,174.53..1,119,649.17 rows=3,650,154 width=359) (actual time=506.944..339,610.860 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_buyer_program_bpr.bpr_buy_int_fk = tbl_buyer_buy.buy_id_int)
10. 52,057.425 286,614.015 ↓ 1.1 3,964,297 1

Hash Left Join (cost=1,161.50..1,069,446.52 rows=3,650,154 width=324) (actual time=497.779..286,614.015 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_offer_off.off_bus_int_fk = tbl_business_unit_bus.bus_id_int)
11. 53,259.418 234,553.465 ↓ 1.1 3,964,297 1

Hash Left Join (cost=1,153.15..1,038,365.62 rows=3,650,154 width=311) (actual time=494.618..234,553.465 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_offer_off.off_agg_int_fk = tbl_offer_aggregate_agg.agg_id_int)
12. 53,165.902 180,824.546 ↓ 1.1 3,964,297 1

Hash Join (cost=119.94..964,449.79 rows=3,650,154 width=271) (actual time=24.877..180,824.546 rows=3,964,297 loops=1)

  • Hash Cond: (tbl_offer_off.off_cur_int_fk = tbl_currency_cur.cur_id_int)
13. 52,308.758 127,656.096 ↓ 1.1 3,964,297 1

Merge Join (cost=79.60..914,219.84 rows=3,650,154 width=257) (actual time=22.249..127,656.096 rows=3,964,297 loops=1)

  • Merge Cond: (tjnct_sup_bpr_sbp.sbp_sup_int_fk = tbl_offer_off.off_sbp_int_fk)
14. 261.929 761.863 ↓ 1.0 9,467 1

Nested Loop (cost=0.84..4,231.83 rows=9,466 width=97) (actual time=3.196..761.863 rows=9,467 loops=1)

15. 183.034 329.528 ↓ 1.0 9,467 1

Merge Join (cost=0.57..1,260.70 rows=9,466 width=48) (actual time=3.157..329.528 rows=9,467 loops=1)

  • Merge Cond: (tbl_supplier_sup.sup_id_int = tjnct_sup_bpr_sbp.sbp_sup_int_fk)
16. 53.571 53.571 ↑ 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.066..53.571 rows=7,151 loops=1)

17. 92.923 92.923 ↓ 1.0 9,467 1

Index Only Scan using uk_regt4agw5xwqlwn5i5o46lmdj on tjnct_sup_bpr_sbp (cost=0.29..695.40 rows=9,466 width=16) (actual time=3.042..92.923 rows=9,467 loops=1)

  • Heap Fetches: 9,467
18. 170.406 170.406 ↑ 1.0 1 9,467

Index Scan using pk_bpr on tbl_buyer_program_bpr (cost=0.28..0.30 rows=1 width=57) (actual time=0.011..0.018 rows=1 loops=9,467)

  • Index Cond: (bpr_id_int = tjnct_sup_bpr_sbp.sbp_bpr_int_fk)
19. 48,810.879 74,585.475 ↓ 1.3 4,010,550 1

Materialize (cost=0.56..879,254.49 rows=3,050,914 width=176) (actual time=0.041..74,585.475 rows=4,010,550 loops=1)

20. 25,774.596 25,774.596 ↑ 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.014..25,774.596 rows=2,996,361 loops=1)

21. 0.429 2.548 ↑ 1.0 21 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
22. 1.025 2.119 ↑ 1.0 21 1

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

  • Hash Cond: (exr.exr_cur_int_fk = tbl_currency_cur.cur_id_int)
23. 0.514 0.514 ↓ 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.231..0.514 rows=20 loops=1)

  • Index Cond: (exr_dtm = $1)
24. 0.224 0.580 ↑ 1.0 21 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
25. 0.356 0.356 ↑ 1.0 21 1

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

26. 238.754 469.501 ↓ 1.0 24,109 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 2,072kB
27. 230.747 230.747 ↓ 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.026..230.747 rows=24,109 loops=1)

28. 1.600 3.125 ↑ 1.0 238 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
29. 1.525 1.525 ↑ 1.0 238 1

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

30. 4.976 8.870 ↑ 1.0 357 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
31. 3.894 3.894 ↑ 1.0 357 1

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

32. 0.212 0.446 ↑ 1.0 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.234 0.234 ↑ 1.0 15 1

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

Planning time : 9.884 ms
Execution time : 422,779.624 ms