explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UXuu : Optimization for: plan #ByS5

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,494,057,288,599,525,120.00..19,058,577,101,544,087,552.00 rows=260,985,802,075,627,028,480 width=5,346) (actual rows= loops=)

  • Hash Cond: (recs.keypanjivacompanylocconsignee = comp_loc_1.keypanjivacompanylocation)
2.          

CTE hs_codes

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=358.42..415.07 rows=5,665 width=19) (actual rows= loops=)

  • Group Key: sub_head.keyhssubheading, sub_head.hssubheadingcode, head.hsheadingcode, chap.hschaptercode
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=64.52..301.77 rows=5,665 width=19) (actual rows= loops=)

  • Hash Cond: (head.keyhschapter = chap.keyhschapter)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=60.05..281.79 rows=5,665 width=18) (actual rows= loops=)

  • Hash Cond: (sub_head.keyhsheading = head.keyhsheading)
6. 0.000 0.000 ↓ 0.0

Seq Scan on hssubheading sub_head (cost=0.00..206.81 rows=5,665 width=15) (actual rows= loops=)

  • Filter: (updoperation < 2)
7. 0.000 0.000 ↓ 0.0

Hash (cost=44.52..44.52 rows=1,242 width=11) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on hsheading head (cost=0.00..44.52 rows=1,242 width=11) (actual rows= loops=)

  • Filter: (updoperation < 2)
9. 0.000 0.000 ↓ 0.0

Hash (cost=3.24..3.24 rows=99 width=5) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on hschapter chap (cost=0.00..3.24 rows=99 width=5) (actual rows= loops=)

  • Filter: (updoperation < 2)
11.          

CTE unlocodes

12. 0.000 0.000 ↓ 0.0

Unique (cost=17,544.76..20,605.26 rows=111,291 width=70) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=17,544.76..17,822.99 rows=111,291 width=70) (actual rows= loops=)

  • Sort Key: unlocode.keyunlocode, unlocode.unlocode, unlocode.unlocodename, unlocode.panjivacountry, unlocode.latitude, unlocode.longitude, unlocode.keyunlocodesubdivision, un_sub.subdivisioncode, un_sub.unlocodesubdivision, un_sub_level.unlocodesubdivisionlevel
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=172.05..3,648.86 rows=111,291 width=70) (actual rows= loops=)

  • Hash Cond: (un_sub.keyunlocodesubdivisionlevel = un_sub_level.keyunlocodesubdivisionlevel)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=168.70..3,340.20 rows=111,291 width=56) (actual rows= loops=)

  • Hash Cond: (unlocode.keyunlocodesubdivision = un_sub.keyunlocodesubdivision)
16. 0.000 0.000 ↓ 0.0

Seq Scan on unlocode (cost=0.00..2,879.14 rows=111,291 width=40) (actual rows= loops=)

  • Filter: (updoperation < 2)
17. 0.000 0.000 ↓ 0.0

Hash (cost=109.85..109.85 rows=4,708 width=18) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on unlocodesubdivision un_sub (cost=0.00..109.85 rows=4,708 width=18) (actual rows= loops=)

  • Filter: (updoperation < 2)
19. 0.000 0.000 ↓ 0.0

Hash (cost=2.17..2.17 rows=94 width=18) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on unlocodesubdivisionlevel un_sub_level (cost=0.00..2.17 rows=94 width=18) (actual rows= loops=)

  • Filter: (updoperation < 2)
21.          

CTE ports

22. 0.000 0.000 ↓ 0.0

Unique (cost=3,425,233,821.18..3,430,862,619.45 rows=187,626,609 width=1,198) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Sort (cost=3,425,233,821.18..3,425,702,887.70 rows=187,626,609 width=1,198) (actual rows= loops=)

  • Sort Key: tradeport.keytradeport, (COALESCE(tradeportdetail.tradeport, unlocodes.unlocodename)), (string_agg((alt_name_2.tradeportalternatename)::text, ' | '::text)), (COALESCE(tradeportdetail.panjivacountry, unlocodes.panjivacountry)), (COALESCE(tradeportdetail.latitude, unlocodes.latitude)), (COALESCE(tradeportdetail.longitude, unlocodes.longitude)), unlocodes.unlocode, unlocodes.unlocodename, unlocodes.subdivisioncode, unlocodes.unlocodesubdivision, unlocodes.unlocodesubdivisionlevel
24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=48.77..2,908,854,894.40 rows=187,626,609 width=1,198) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=35.60..2,080,226,592.10 rows=62,542,203 width=1,171) (actual rows= loops=)

  • Join Filter: ((tradeport.keyunlocode = unlocodes.keyunlocode) OR (tradeportdetail.keyunlocodesubdivision = unlocodes.keyunlocodesubdivision))
26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=35.60..2,889.56 rows=112,195 width=47) (actual rows= loops=)

  • Hash Cond: (tradeport.keytradeportdetail = tradeportdetail.keytradeportdetail)
27. 0.000 0.000 ↓ 0.0

Seq Scan on tradeport (cost=0.00..2,559.44 rows=112,195 width=12) (actual rows= loops=)

  • Filter: (updoperation < 2)
28. 0.000 0.000 ↓ 0.0

Hash (cost=24.30..24.30 rows=904 width=43) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on tradeportdetail (cost=0.00..24.30 rows=904 width=43) (actual rows= loops=)

  • Filter: (updoperation < 2)
30. 0.000 0.000 ↓ 0.0

CTE Scan on unlocodes (cost=0.00..2,225.82 rows=111,291 width=1,136) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Unique (cost=13.17..13.19 rows=3 width=36) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=13.17..13.18 rows=3 width=36) (actual rows= loops=)

  • Sort Key: (string_agg((alt_name_2.tradeportalternatename)::text, ' | '::text))
33. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=4.30..13.15 rows=3 width=36) (actual rows= loops=)

  • Group Key: alt_name_2.keytradeport
34. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tradeportalternatename alt_name_2 (cost=4.30..13.10 rows=3 width=14) (actual rows= loops=)

  • Recheck Cond: ((tradeport.keytradeport = keytradeport) AND (updoperation < 2))
35. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ai_tradeportalternatename_25176 (cost=0.00..4.30 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (tradeport.keytradeport = keytradeport)
36. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=1,494,057,285,168,640,512.00..5,409,019,649,557,911,552.00 rows=260,985,802,075,627,028,480 width=4,739) (actual rows= loops=)

  • Merge Cond: (port_foreign_dest.keytradeport = recs.keytradeportinbondforeigndest)
37. 0.000 0.000 ↓ 0.0

Sort (cost=288,621,487.97..289,090,554.49 rows=187,626,609 width=780) (actual rows= loops=)

  • Sort Key: port_foreign_dest.keytradeport
38. 0.000 0.000 ↓ 0.0

CTE Scan on ports port_foreign_dest (cost=0.00..3,752,532.18 rows=187,626,609 width=780) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Materialize (cost=1,494,057,284,880,018,944.00..1,494,407,951,387,875,328.00 rows=70,133,301,571,227,296 width=3,963) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=1,494,057,284,880,018,944.00..1,494,232,618,133,947,136.00 rows=70,133,301,571,227,296 width=3,963) (actual rows= loops=)

  • Sort Key: recs.keytradeportinbondforeigndest
41. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,082,399,893,793.45..39,029,616,829,068,968.00 rows=70,133,301,571,227,296 width=3,963) (actual rows= loops=)

  • Hash Cond: (recs.keyhssubheading = hs_codes.keyhssubheading)
42. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,082,399,893,609.34..1,168,222,710,022,755.25 rows=52,338,114,230,047,552 width=3,907) (actual rows= loops=)

  • Hash Cond: (recs.keypanjivacompanylocshipper = comp_loc.keypanjivacompanylocation)
43. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=1,082,399,892,611.26..786,154,476,142,410.12 rows=52,338,114,230,047,552 width=3,180) (actual rows= loops=)

  • Merge Cond: (port_unlading.keytradeport = recs.keytradeportunlading)
44. 0.000 0.000 ↓ 0.0

Sort (cost=288,621,487.97..289,090,554.49 rows=187,626,609 width=780) (actual rows= loops=)

  • Sort Key: port_unlading.keytradeport
45. 0.000 0.000 ↓ 0.0

CTE Scan on ports port_unlading (cost=0.00..3,752,532.18 rows=187,626,609 width=780) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Materialize (cost=1,082,111,271,123.29..1,082,834,997,480.70 rows=144,745,271,481 width=2,404) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Sort (cost=1,082,111,271,123.29..1,082,473,134,302.00 rows=144,745,271,481 width=2,404) (actual rows= loops=)

  • Sort Key: recs.keytradeportunlading
48. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=311,977,136.95..2,483,629,717.43 rows=144,745,271,481 width=2,404) (actual rows= loops=)

  • Merge Cond: (recs.keytradeportlading = port_lading.keytradeport)
49. 0.000 0.000 ↓ 0.0

Sort (cost=23,355,648.99..23,357,215.05 rows=626,423 width=1,628) (actual rows= loops=)

  • Sort Key: recs.keytradeportlading
50. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=13,985,313.93..21,965,709.05 rows=626,423 width=1,628) (actual rows= loops=)

  • Hash Cond: (port_dest.keytradeport = recs.keytradeportinbonddestination)
51. 0.000 0.000 ↓ 0.0

CTE Scan on ports port_dest (cost=0.00..3,752,532.18 rows=187,626,609 width=780) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=13,985,313.73..13,985,313.73 rows=16 width=852) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.01..13,985,313.73 rows=16 width=852) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.01..29.35 rows=1 width=523) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.87..21.07 rows=1 width=405) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Index Scan using pk_traderecord on traderecord recs (cost=0.57..8.59 rows=1 width=167) (actual rows= loops=)

  • Index Cond: (keytraderecord = '74309393888911742'::bigint)
  • Filter: (updoperation < 2)
57. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..12.47 rows=1 width=240) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Index Scan using pk_transportmethod on transportmethod rec_method (cost=0.15..8.17 rows=1 width=122) (actual rows= loops=)

  • Index Cond: (recs.keytransportmethod = keytransportmethod)
  • Filter: (updoperation < 2)
59. 0.000 0.000 ↓ 0.0

Index Scan using pk_transportmethodtype on transportmethodtype rec_method_type (cost=0.15..4.25 rows=1 width=120) (actual rows= loops=)

  • Index Cond: (rec_method.keytransportmethodtype = keytransportmethodtype)
  • Filter: (updoperation < 2)
60. 0.000 0.000 ↓ 0.0

Index Scan using pk_traderecordtype on traderecordtype rec_type (cost=0.15..8.17 rows=1 width=120) (actual rows= loops=)

  • Index Cond: (keytraderecordtype = recs.keytraderecordtype)
  • Filter: (updoperation < 2)
61. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.00..13,985,284.06 rows=16 width=337) (actual rows= loops=)

  • Group Key: comps.keytraderecord
62. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..13,985,282.43 rows=33 width=343) (actual rows= loops=)

  • Join Filter: (comps.keyhssubheading = hs_codes_1.keyhssubheading)
63. 0.000 0.000 ↓ 0.0

Seq Scan on traderecordcomponent comps (cost=0.00..13,983,186.38 rows=16 width=287) (actual rows= loops=)

  • Filter: ((updoperation < 2) AND (recs.keytraderecord = keytraderecord))
64. 0.000 0.000 ↓ 0.0

CTE Scan on hs_codes hs_codes_1 (cost=0.00..113.30 rows=5,665 width=64) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Materialize (cost=288,621,487.97..289,559,621.01 rows=187,626,609 width=780) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=288,621,487.97..289,090,554.49 rows=187,626,609 width=780) (actual rows= loops=)

  • Sort Key: port_lading.keytradeport
67. 0.000 0.000 ↓ 0.0

CTE Scan on ports port_lading (cost=0.00..3,752,532.18 rows=187,626,609 width=780) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash (cost=997.51..997.51 rows=46 width=735) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12.95..997.51 rows=46 width=735) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8.60..393.88 rows=23 width=675) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..205.28 rows=23 width=643) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on panjivacompanylocation comp_loc (cost=0.00..10.88 rows=23 width=588) (actual rows= loops=)

  • Filter: (updoperation < 2)
73. 0.000 0.000 ↓ 0.0

Index Scan using pk_panjivacompany on panjivacompany comp (cost=0.43..8.45 rows=1 width=59) (actual rows= loops=)

  • Index Cond: (comp_loc.keypanjivacompany = keypanjivacompany)
  • Filter: (updoperation < 2)
74. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.17..8.18 rows=1 width=32) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using ai_panjivacompanyalternatename_25846 on panjivacompanyalternatename alt_name (cost=0.14..8.16 rows=1 width=516) (actual rows= loops=)

  • Index Cond: (comp_loc.keypanjivacompany = keypanjivacompany)
  • Filter: (updoperation < 2)
76. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=4.35..26.20 rows=2 width=68) (actual rows= loops=)

  • Group Key: comp_tr_type.keypanjivacompany
77. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.35..26.14 rows=2 width=224) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on panjivacompanytotradingtype comp_tr_type (cost=4.20..13.68 rows=2 width=6) (actual rows= loops=)

  • Recheck Cond: (keypanjivacompany = comp_loc.keypanjivacompany)
  • Filter: (updoperation < 2)
79. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ai_panjivacompanytotradingtype_25850 (cost=0.00..4.20 rows=6 width=0) (actual rows= loops=)

  • Index Cond: (keypanjivacompany = comp_loc.keypanjivacompany)
80. 0.000 0.000 ↓ 0.0

Index Scan using pk_panjivacompanytradingtype on panjivacompanytradingtype tr_type (cost=0.15..6.17 rows=1 width=220) (actual rows= loops=)

  • Index Cond: (keypanjivacompanytradingtype = comp_tr_type.keypanjivacompanytradingtype)
  • Filter: (updoperation < 2)
81. 0.000 0.000 ↓ 0.0

Hash (cost=113.30..113.30 rows=5,665 width=64) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

CTE Scan on hs_codes (cost=0.00..113.30 rows=5,665 width=64) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash (cost=997.51..997.51 rows=46 width=735) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12.95..997.51 rows=46 width=735) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8.60..393.88 rows=23 width=675) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..205.28 rows=23 width=643) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Seq Scan on panjivacompanylocation comp_loc_1 (cost=0.00..10.88 rows=23 width=588) (actual rows= loops=)

  • Filter: (updoperation < 2)
88. 0.000 0.000 ↓ 0.0

Index Scan using pk_panjivacompany on panjivacompany comp_1 (cost=0.43..8.45 rows=1 width=59) (actual rows= loops=)

  • Index Cond: (comp_loc_1.keypanjivacompany = keypanjivacompany)
  • Filter: (updoperation < 2)
89. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.17..8.18 rows=1 width=32) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Index Scan using ai_panjivacompanyalternatename_25846 on panjivacompanyalternatename alt_name_1 (cost=0.14..8.16 rows=1 width=516) (actual rows= loops=)

  • Index Cond: (comp_loc_1.keypanjivacompany = keypanjivacompany)
  • Filter: (updoperation < 2)
91. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=4.35..26.20 rows=2 width=68) (actual rows= loops=)

  • Group Key: comp_tr_type_1.keypanjivacompany
92. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.35..26.14 rows=2 width=224) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on panjivacompanytotradingtype comp_tr_type_1 (cost=4.20..13.68 rows=2 width=6) (actual rows= loops=)

  • Recheck Cond: (keypanjivacompany = comp_loc_1.keypanjivacompany)
  • Filter: (updoperation < 2)
94. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ai_panjivacompanytotradingtype_25850 (cost=0.00..4.20 rows=6 width=0) (actual rows= loops=)

  • Index Cond: (keypanjivacompany = comp_loc_1.keypanjivacompany)
95. 0.000 0.000 ↓ 0.0

Index Scan using pk_panjivacompanytradingtype on panjivacompanytradingtype tr_type_1 (cost=0.15..6.17 rows=1 width=220) (actual rows= loops=)

  • Index Cond: (keypanjivacompanytradingtype = comp_tr_type_1.keypanjivacompanytradingtype)