explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 23zx

Settings
# exclusive inclusive rows x rows loops node
1. 1,201,841.845 1,835,600.023 ↓ 28,839,648.0 28,839,648 1

Sort (cost=10,001,290,749.76..10,001,290,749.77 rows=1 width=223) (actual time=1,600,075.060..1,835,600.023 rows=28,839,648 loops=1)

  • Sort Key: ibsh.shipment_nbr, ctr.container_nbr
  • Sort Method: external merge Disk: 6248088kB
2. 7,738.289 633,758.178 ↓ 28,839,648.0 28,839,648 1

Hash Join (cost=10,001,043,333.64..10,001,290,749.75 rows=1 width=223) (actual time=46,515.402..633,758.178 rows=28,839,648 loops=1)

  • Hash Cond: (poh.status_id = pos.id)
3. 7,538.946 626,019.882 ↓ 28,839,648.0 28,839,648 1

Hash Join (cost=10,001,043,332.33..10,001,290,748.41 rows=1 width=227) (actual time=46,515.381..626,019.882 rows=28,839,648 loops=1)

  • Hash Cond: (fcx_1.company_id = comp_2.id)
4. 7,724.034 618,480.915 ↓ 28,839,648.0 28,839,648 1

Hash Join (cost=10,001,043,327.98..10,001,290,744.03 rows=1 width=220) (actual time=46,515.352..618,480.915 rows=28,839,648 loops=1)

  • Hash Cond: (fcx_1.facility_id = fac_2.id)
5. 8,380.555 610,756.872 ↓ 4,119,949.7 28,839,648 1

Hash Join (cost=10,001,043,323.63..10,001,290,739.62 rows=7 width=217) (actual time=46,515.332..610,756.872 rows=28,839,648 loops=1)

  • Hash Cond: (poh.facility_company_xref_id = fcx_1.id)
6. 549,759.547 602,376.310 ↓ 4,119,949.7 28,839,648 1

Hash Join (cost=10,001,043,321.69..10,001,290,737.44 rows=7 width=209) (actual time=46,515.315..602,376.310 rows=28,839,648 loops=1)

  • Hash Cond: (inv.container_id = ctr.id)
7. 13,615.412 27,368.177 ↓ 1.0 2,506,084 1

HashAggregate (cost=951,129.27..1,116,072.94 rows=2,443,610 width=147) (actual time=21,266.173..27,368.177 rows=2,506,084 loops=1)

  • Group Key: inv.id, fac_3.code, inv_st.description, bn.batch_nbr, bn.expiry_date, invn_attr_1.invn_attr_a, invn_attr_1.invn_attr_b, invn_attr_1.invn_attr_c, invn_attr
8. 2,105.155 13,752.765 ↓ 1.9 4,692,223 1

Hash Left Join (cost=210,534.71..816,730.72 rows=2,443,610 width=147) (actual time=2,072.496..13,752.765 rows=4,692,223 loops=1)

  • Hash Cond: (inv.batch_number_id = bn.id)
9. 3,165.162 11,515.244 ↓ 1.9 4,692,223 1

Hash Join (cost=209,260.40..715,426.75 rows=2,443,610 width=135) (actual time=1,940.103..11,515.244 rows=4,692,223 loops=1)

  • Hash Cond: (inv.invn_attr_id = invn_attr_1.id)
10. 1,135.876 8,304.937 ↓ 1.9 4,692,223 1

Hash Join (cost=203,217.21..599,421.11 rows=2,443,610 width=113) (actual time=1,894.918..8,304.937 rows=4,692,223 loops=1)

  • Hash Cond: (inv.status_id = inv_st.id)
11. 1,322.087 7,169.057 ↓ 1.9 4,692,223 1

Hash Join (cost=203,216.02..516,948.09 rows=2,443,610 width=96) (actual time=1,894.886..7,169.057 rows=4,692,223 loops=1)

  • Hash Cond: (inv.facility_id = fac_3.id)
12. 3,619.413 5,844.939 ↓ 1.9 4,692,223 1

Hash Right Join (cost=202,389.65..421,431.83 rows=2,443,610 width=89) (actual time=1,892.845..5,844.939 rows=4,692,223 loops=1)

  • Hash Cond: (al.from_inventory_id = inv.id)
13. 333.828 333.828 ↓ 1.0 2,315,040 1

Seq Scan on allocation al (cost=0.00..121,596.14 rows=2,227,338 width=12) (actual time=0.004..333.828 rows=2,315,040 loops=1)

14. 901.589 1,891.698 ↓ 1.0 2,506,084 1

Hash (cost=122,972.32..122,972.32 rows=2,443,610 width=81) (actual time=1,891.698..1,891.698 rows=2,506,084 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 305600kB
15. 990.109 990.109 ↓ 1.0 2,506,084 1

Seq Scan on inventory inv (cost=0.00..122,972.32 rows=2,443,610 width=81) (actual time=0.010..990.109 rows=2,506,084 loops=1)

  • Filter: (facility_id = ANY ('{3,1}'::integer[]))
16. 0.853 2.031 ↑ 1.0 5,286 1

Hash (cost=654.58..654.58 rows=5,286 width=11) (actual time=2.031..2.031 rows=5,286 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 239kB
17. 1.178 1.178 ↑ 1.0 5,286 1

Seq Scan on facility fac_3 (cost=0.00..654.58 rows=5,286 width=11) (actual time=0.003..1.178 rows=5,286 loops=1)

18. 0.001 0.004 ↑ 1.0 3 1

Hash (cost=1.09..1.09 rows=3 width=21) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on inventory_status inv_st (cost=0.00..1.09 rows=3 width=21) (actual time=0.003..0.003 rows=3 loops=1)

20. 25.764 45.145 ↓ 1.0 80,597 1

Hash (cost=3,459.73..3,459.73 rows=79,491 width=30) (actual time=45.145..45.145 rows=80,597 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 5021kB
21. 19.381 19.381 ↓ 1.0 80,597 1

Seq Scan on inventory_attribute invn_attr_1 (cost=0.00..3,459.73 rows=79,491 width=30) (actual time=0.005..19.381 rows=80,597 loops=1)

22. 3.245 132.366 ↑ 1.0 15,237 1

Hash (cost=779.11..779.11 rows=15,237 width=16) (actual time=132.366..132.366 rows=15,237 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 734kB
23. 129.121 129.121 ↑ 1.0 15,237 1

Seq Scan on batch_number bn (cost=0.00..779.11 rows=15,237 width=16) (actual time=5.668..129.121 rows=15,237 loops=1)

24. 15,138.084 25,248.586 ↓ 14,419,476.0 28,838,952 1

Hash (cost=10,000,092,192.36..10,000,092,192.36 rows=2 width=198) (actual time=25,248.586..25,248.586 rows=28,838,952 loops=1)

  • Buckets: 1024 Batches: 8 (originally 1) Memory Usage: 1048577kB
25. 7,216.123 10,110.502 ↓ 14,419,476.0 28,838,952 1

Hash Join (cost=10,000,046,208.04..10,000,092,192.36 rows=2 width=198) (actual time=1,180.377..10,110.502 rows=28,838,952 loops=1)

  • Hash Cond: (ctr.rcvd_shipment_id = ibsd.ib_shipment_id)
26. 72.917 1,714.128 ↓ 3,055.4 366,653 1

Hash Join (cost=8.47..45,992.28 rows=120 width=47) (actual time=0.105..1,714.128 rows=366,653 loops=1)

  • Hash Cond: (ctr.status_id = cs.id)
27. 113.727 1,641.196 ↓ 3,055.4 366,653 1

Hash Join (cost=6.34..45,986.10 rows=120 width=41) (actual time=0.079..1,641.196 rows=366,653 loops=1)

  • Hash Cond: (ctr.facility_company_xref_id = fcx.id)
28. 1,527.412 1,527.412 ↓ 1.0 643,784 1

Seq Scan on container ctr (cost=0.00..42,340.98 rows=632,830 width=42) (actual time=0.008..1,527.412 rows=643,784 loops=1)

  • Filter: (facility_company_xref_id = ANY ('{1,276}'::integer[]))
  • Rows Removed by Filter: 1
29. 0.001 0.057 ↑ 1.0 1 1

Hash (cost=6.31..6.31 rows=1 width=11) (actual time=0.057..0.057 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
30. 0.006 0.056 ↑ 1.0 1 1

Merge Join (cost=6.18..6.31 rows=1 width=11) (actual time=0.055..0.056 rows=1 loops=1)

  • Merge Cond: (fcx.company_id = comp.id)
31. 0.010 0.043 ↑ 1.0 1 1

Sort (cost=5.90..5.90 rows=1 width=15) (actual time=0.043..0.043 rows=1 loops=1)

  • Sort Key: fcx.company_id
  • Sort Method: quicksort Memory: 25kB
32. 0.009 0.033 ↑ 1.0 1 1

Hash Join (cost=4.35..5.89 rows=1 width=15) (actual time=0.031..0.033 rows=1 loops=1)

  • Hash Cond: (fcx.facility_id = fac.id)
33. 0.005 0.005 ↑ 1.0 15 1

Seq Scan on facility_company_xref fcx (cost=0.00..1.45 rows=15 width=12) (actual time=0.003..0.005 rows=15 loops=1)

34. 0.019 0.019 ↑ 1.0 1 1

Hash (cost=4.32..4.32 rows=1 width=11) (actual time=0.019..0.019 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • -> Index Scan using facility_ukey on facility fac (cost=0.28..4.32 rows=1 width=11) (actual time=0.017..0.018 rows=
  • Index Cond: ((code)::text = 'CD11'::text)
35. 0.007 0.007 ↑ 440.0 3 1

Index Only Scan using company_pkey on company comp (cost=0.28..124.48 rows=1,320 width=4) (actual time=0.007..0.007 rows=3 loops=1)

  • Heap Fetches: 0
36. 0.005 0.015 ↑ 1.0 18 1

Hash (cost=1.54..1.54 rows=18 width=14) (actual time=0.015..0.015 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.010 0.010 ↑ 1.0 18 1

Seq Scan on container_status cs (cost=0.00..1.54 rows=18 width=14) (actual time=0.008..0.010 rows=18 loops=1)

38. 9.351 1,180.251 ↓ 7,583.3 22,750 1

Hash (cost=10,000,046,199.48..10,000,046,199.48 rows=3 width=155) (actual time=1,180.251..1,180.251 rows=22,750 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4062kB
39. 8.446 1,170.900 ↓ 7,583.3 22,750 1

Hash Right Join (cost=10,000,046,044.84..10,000,046,199.48 rows=3 width=155) (actual time=1,136.930..1,170.900 rows=22,750 loops=1)

  • Hash Cond: (ven.id = poh.vendor_id)
40. 78.141 78.141 ↑ 1.0 1,320 1

Seq Scan on company ven (cost=0.00..149.60 rows=1,320 width=27) (actual time=0.003..78.141 rows=1,320 loops=1)

41. 8.904 1,084.313 ↓ 7,583.3 22,750 1

Hash (cost=10,000,046,044.74..10,000,046,044.74 rows=3 width=136) (actual time=1,084.313..1,084.313 rows=22,750 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3609kB
42. 6.926 1,075.409 ↓ 7,583.3 22,750 1

Hash Join (cost=10,000,045,929.79..10,000,046,044.74 rows=3 width=136) (actual time=1,041.551..1,075.409 rows=22,750 loops=1)

  • Hash Cond: (poh.id = pod.purchase_order_id)
43. 27.012 27.012 ↓ 1.0 1,211 1

Seq Scan on purchase_order_hdr poh (cost=0.00..110.33 rows=1,210 width=38) (actual time=0.006..27.012 rows=1,211 loops=1)

  • Filter: (facility_company_xref_id = ANY ('{1,276}'::integer[]))
44. 9.063 1,041.471 ↓ 7,583.3 22,750 1

Hash (cost=10,000,045,929.69..10,000,045,929.69 rows=3 width=114) (actual time=1,041.471..1,041.471 rows=22,750 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3076kB
45. 300.322 1,032.408 ↓ 7,583.3 22,750 1

Hash Join (cost=10,000,045,268.54..10,000,045,929.69 rows=3 width=114) (actual time=766.662..1,032.408 rows=22,750 loops=1)

  • Hash Cond: (pod.id = ibsd.po_dtl_id)
  • -> Seq Scan on purchase_order_dtl pod (cost=0.00..616.16 rows=11972 width=12) (actual time=12.924..283.752 rows=10215 loo
46. 15.939 732.086 ↓ 4,550.0 22,750 1

Hash (cost=10,000,045,268.38..10,000,045,268.38 rows=5 width=102) (actual time=732.086..732.086 rows=22,750 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2787kB
  • -> Nested Loop (cost=10000044306.33..10000045268.38 rows=5 width=102) (actual time=707.606..726.252 rows=33008 loop
47. 639.428 716.147 ↓ 6,601.6 33,008 1

Merge Join (cost=44,306.05..45,263.90 rows=5 width=91) (actual time=707.571..716.147 rows=33,008 loops=1)

  • Merge Cond: (ibsh.id = ibsd.ib_shipment_id)
  • -> Sort (cost=44004.27..44480.92 rows=190662 width=29) (actual time=617.927..626.815 rows=120343 loops=
48. 0.065 76.719 ↓ 156.0 156 1

Sort (cost=301.78..301.79 rows=1 width=62) (actual time=76.704..76.719 rows=156 loops=1)

  • Sort Key: ibsh.id
  • Sort Method: quicksort Memory: 46kB
  • Sort Key: ibsd.ib_shipment_id
  • Sort Method: quicksort Memory: 21366kB
  • -> Hash Join (cost=5813.93..27282.58 rows=190662 width=29) (actual time=20.201..568.451 rows=1948
49. 76.646 76.654 ↓ 156.0 156 1

Hash Join (cost=56.47..301.77 rows=1 width=62) (actual time=76.492..76.654 rows=156 loops=1)

  • Hash Cond: (ibsh.status_id = ibs_stat.id)
  • -> Merge Join (cost=54.97..300.24 rows=1 width=51) (actual time=76.468..76.591 rows=156 loo
  • Merge Cond: (ibsh.load_id = load.id)
  • -> Sort (cost=54.69..54.70 rows=1 width=55) (actual time=75.686..75.697 rows=156 loop
  • Sort Key: ibsh.load_id
  • Sort Method: quicksort Memory: 46kB
  • -> Hash Join (cost=4.63..54.68 rows=1 width=55) (actual time=38.739..75.591 row
  • Hash Cond: (ibsh.facility_id = fac_1.id)
  • -> Index Scan using ib_shipment_shipped_date_idx on ib_shipment ibsh (cos
  • Index Cond: (shipped_date = '2019-04-29'::date)
  • Filter: ((facility_id = ANY ('{3,1}'::integer[])) AND (company_id = 1
  • -> Hash (cost=4.32..4.32 rows=1 width=11) (actual time=0.019..0.019 rows=
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • -> Index Scan using facility_ukey on facility fac_1 (cost=0.28..4.3
  • Index Cond: ((code)::text = 'CD11'::text)
  • -> Index Only Scan using load_pkey on load (cost=0.28..240.02 rows=3995 width=4) (act
  • Heap Fetches: 1446
  • Hash Cond: (ibsd.invn_attr_id = invn_attr.id)
  • -> Seq Scan on ib_shipment_dtl ibsd (cost=0.00..12888.86 rows=190662 width=33) (actual time
  • -> Hash (cost=3230.48..3230.48 rows=79491 width=4) (actual time=20.154..20.154 rows=80597 l
50. 0.008 0.008 ↑ 1.0 8 1

Hash (cost=1.24..1.24 rows=8 width=19) (actual time=0.008..0.008 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • -> Seq Scan on ib_shipment_status ibs_stat (cost=0.00..1.24 rows=8 width=19) (actual
  • Buckets: 8192 Batches: 1 Memory Usage: 2834kB
  • -> Index Only Scan using inventory_attribute_pkey on inventory_attribute invn_attr (c
  • Heap Fetches: 2002
51. 0.000 0.000 ↑ 1.0 1 33,008

Materialize (cost=0.28..4.32 rows=1 width=15) (actual time=0.000..0.000 rows=1 loops=33,008)

  • -> Index Scan using company_ukey1 on company comp_1 (cost=0.28..4.32 rows=1 width=15) (actual time=0.02
  • Index Cond: ((code)::text = '1000'::text)
  • Filter: (id = 1)
52. 0.002 0.007 ↑ 1.0 15 1

Hash (cost=1.45..1.45 rows=15 width=12) (actual time=0.007..0.007 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
53. 0.005 0.005 ↑ 1.0 15 1

Seq Scan on facility_company_xref fcx_1 (cost=0.00..1.45 rows=15 width=12) (actual time=0.003..0.005 rows=15 loops=1)

54. 0.000 0.009 ↑ 1.0 1 1

Hash (cost=4.32..4.32 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
55. 0.009 0.009 ↑ 1.0 1 1

Index Scan using facility_ukey on facility fac_2 (cost=0.28..4.32 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((code)::text = 'CD11'::text)
56. 0.000 0.021 ↑ 1.0 1 1

Hash (cost=4.32..4.32 rows=1 width=15) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
57. 0.021 0.021 ↑ 1.0 1 1

Index Scan using company_ukey1 on company comp_2 (cost=0.28..4.32 rows=1 width=15) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: ((code)::text = '1000'::text)
58. 0.003 0.007 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=4) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
59. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on purchase_order_status pos (cost=0.00..1.15 rows=5 width=4) (actual time=0.004..0.004 rows=5 loops=1)