explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nw2j : Optimization for: plan #uhX2

Settings

Optimization path:

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

Limit (cost=78,480.21..78,481.34 rows=50 width=6,190) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=78,480.21..78,927.96 rows=19,900 width=6,190) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=78,480.21..78,529.96 rows=19,900 width=6,182) (actual rows= loops=)

  • Sort Key: c.extrainfo NULLS FIRST, c.contractid
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,194.70..24,275.80 rows=19,900 width=6,182) (actual rows= loops=)

  • Hash Cond: (t1c.majortype = t1e.majortypeid)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,193.59..24,001.06 rows=19,900 width=6,120) (actual rows= loops=)

  • Hash Cond: (c.contractid = t1b.contractid)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17,934.46..23,666.84 rows=19,900 width=4,996) (actual rows= loops=)

  • Hash Cond: (c.primaryuseid = t18.primaryuseid)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17,913.68..23,514.76 rows=19,900 width=4,972) (actual rows= loops=)

  • Hash Cond: (c.employeeid = t17.id)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,878.90..20,353.52 rows=19,900 width=4,866) (actual rows= loops=)

  • Hash Cond: (t11.accountinfo5id = t14.id)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,877.76..20,277.44 rows=19,900 width=4,830) (actual rows= loops=)

  • Hash Cond: (t11.accountinfo4id = t13.id)
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,876.61..20,201.34 rows=19,900 width=4,794) (actual rows= loops=)

  • Join Filter: (c.servicetype = t1c.typeid)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,872.03..19,891.28 rows=19,900 width=4,699) (actual rows= loops=)

  • Hash Cond: (t11.accountfrequencyid = t12.accountfrequencyid)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,870.83..19,616.64 rows=19,900 width=4,631) (actual rows= loops=)

  • Hash Cond: (c.accountid = t11.id)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,423.01..18,996.45 rows=19,900 width=4,504) (actual rows= loops=)

  • Hash Cond: (vc_2.vendorcontractid = cb_2.vendorcontractid)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,421.40..18,912.08 rows=19,900 width=4,286) (actual rows= loops=)

  • Hash Cond: (c.vendorcontractid = vc_2.vendorcontractid)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,388.55..18,711.33 rows=19,900 width=3,824) (actual rows= loops=)

  • Hash Cond: (c.fixedallocid = tf.typeid)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,387.44..18,436.72 rows=19,900 width=3,654) (actual rows= loops=)

  • Hash Cond: (c.glid = te.glid)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,377.93..18,153.88 rows=19,900 width=3,620) (actual rows= loops=)

  • Hash Cond: ((tc.country)::text = (td.countrycode)::text)
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,370.58..17,872.90 rows=19,900 width=3,602) (actual rows= loops=)

  • Hash Cond: (c.locationid = tc.locationid)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,326.15..17,705.31 rows=19,900 width=3,396) (actual rows= loops=)

  • Hash Cond: (c.costcenterfkid = tb.costcenterid)
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,609.82..16,715.35 rows=19,900 width=2,800) (actual rows= loops=)

  • Hash Cond: (c.vendorid = ta.vendorid)
21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,562.82..16,394.73 rows=19,900 width=2,642) (actual rows= loops=)

  • Hash Cond: (c.lastactionuserid = t7.userid)
22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,525.32..16,083.60 rows=19,900 width=2,443) (actual rows= loops=)

  • Hash Cond: ((c.adrcountry)::text = (t6.countrycode)::text)
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,517.96..15,810.10 rows=19,900 width=2,425) (actual rows= loops=)

  • Hash Cond: (i.colocationid = t5.recordid)
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,516.33..15,733.75 rows=19,900 width=2,203) (actual rows= loops=)

  • Hash Cond: (i.localtollcontractid = vc_1.vendorcontractid)
25. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,479.13..15,536.83 rows=19,900 width=1,523) (actual rows= loops=)

  • Hash Cond: (i.localtollvendorid = t3.vendorid)
26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,432.14..15,330.02 rows=19,900 width=1,365) (actual rows= loops=)

  • Hash Cond: (vc.vendorcontractid = cb.vendorcontractid)
27. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,430.53..15,245.64 rows=19,900 width=1,147) (actual rows= loops=)

  • Hash Cond: (i.localldcontractid = vc.vendorcontractid)
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,397.68..15,039.49 rows=19,900 width=685) (actual rows= loops=)

  • Hash Cond: (i.localldvendorid = t1.vendorid)
29. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,350.68..14,818.64 rows=19,900 width=527) (actual rows= loops=)

  • Hash Cond: (i.infoid = c.contractid)
30. 0.000 0.000 ↓ 0.0

Seq Scan on telephoneinventories i (cost=0.00..1,064.15 rows=54,615 width=36) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=13,101.93..13,101.93 rows=19,900 width=495) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Subquery Scan on c (cost=8.42..13,101.93 rows=19,900 width=495) (actual rows= loops=)

  • Filter: ((hashed SubPlan 1) OR (c.contractid = ANY ('{}'::integer[])))
33. 0.000 0.000 ↓ 0.0

Seq Scan on inventories i_1 (cost=0.00..12,596.01 rows=39,800 width=1,005) (actual rows= loops=)

  • Filter: (servicetype = 5)
34.          

SubPlan (for Subquery Scan)

35. 0.000 0.000 ↓ 0.0

Index Only Scan using unq_1_orderitemsordercontract on orderitems (cost=0.28..8.40 rows=7 width=4) (actual rows= loops=)

  • Index Cond: (orderid = 203)
36. 0.000 0.000 ↓ 0.0

Hash (cost=34.22..34.22 rows=1,022 width=158) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on vendor t1 (cost=0.00..34.22 rows=1,022 width=158) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=24.60..24.60 rows=660 width=462) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on vendorcontract vc (cost=0.00..24.60 rows=660 width=462) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=222) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on contractblobs cb (cost=0.00..1.27 rows=27 width=222) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=34.22..34.22 rows=1,022 width=158) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on vendor t3 (cost=0.00..34.22 rows=1,022 width=158) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=28.95..28.95 rows=660 width=680) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.61..28.95 rows=660 width=680) (actual rows= loops=)

  • Hash Cond: (vc_1.vendorcontractid = cb_1.vendorcontractid)
46. 0.000 0.000 ↓ 0.0

Seq Scan on vendorcontract vc_1 (cost=0.00..24.60 rows=660 width=462) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=222) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on contractblobs cb_1 (cost=0.00..1.27 rows=27 width=222) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=1.28..1.28 rows=28 width=222) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on colocationdict t5 (cost=0.00..1.28 rows=28 width=222) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash (cost=4.38..4.38 rows=238 width=18) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on countrynames t6 (cost=0.00..4.38 rows=238 width=18) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash (cost=31.67..31.67 rows=467 width=199) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on timsuser t7 (cost=0.00..31.67 rows=467 width=199) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=34.22..34.22 rows=1,022 width=158) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on vendor ta (cost=0.00..34.22 rows=1,022 width=158) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=664.48..664.48 rows=4,148 width=596) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on costcenter tb (cost=0.00..664.48 rows=4,148 width=596) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Hash (cost=33.08..33.08 rows=908 width=206) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on locations tc (cost=0.00..33.08 rows=908 width=206) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash (cost=4.38..4.38 rows=238 width=18) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Seq Scan on countrynames td (cost=0.00..4.38 rows=238 width=18) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=5.89..5.89 rows=289 width=34) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on gldict te (cost=0.00..5.89 rows=289 width=34) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=170) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Seq Scan on fixedallocdictionary tf (cost=0.00..1.05 rows=5 width=170) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=24.60..24.60 rows=660 width=462) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on vendorcontract vc_2 (cost=0.00..24.60 rows=660 width=462) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=222) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on contractblobs cb_2 (cost=0.00..1.27 rows=27 width=222) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=296.81..296.81 rows=12,081 width=127) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on account t11 (cost=0.00..296.81 rows=12,081 width=127) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=68) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on accountfrequencydict t12 (cost=0.00..1.09 rows=9 width=68) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Materialize (cost=4.58..11.56 rows=1 width=95) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4.58..11.56 rows=1 width=95) (actual rows= loops=)

  • Hash Cond: (t1d.glid = t1c.glaccount)
77. 0.000 0.000 ↓ 0.0

Seq Scan on gldict t1d (cost=0.00..5.89 rows=289 width=34) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash (cost=4.56..4.56 rows=1 width=61) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Seq Scan on servicetypes t1c (cost=0.00..4.56 rows=1 width=61) (actual rows= loops=)

  • Filter: (typeid = 5)
80. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=7 width=36) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Seq Scan on accountinfo4dict t13 (cost=0.00..1.07 rows=7 width=36) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=6 width=36) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Seq Scan on accountinfo5dict t14 (cost=0.00..1.06 rows=6 width=36) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Hash (cost=1,962.68..1,962.68 rows=85,768 width=106) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Seq Scan on employee t17 (cost=0.00..1,962.68 rows=85,768 width=106) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Hash (cost=12.57..12.57 rows=657 width=24) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Seq Scan on primaryuse t18 (cost=0.00..12.57 rows=657 width=24) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Hash (cost=257.95..257.95 rows=94 width=1,124) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Subquery Scan on t1b (cost=256.07..257.95 rows=94 width=1,124) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

HashAggregate (cost=256.07..257.01 rows=94 width=1,124) (actual rows= loops=)

  • Group Key: cf.contractid
91. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=21.02..232.33 rows=94 width=66) (actual rows= loops=)

  • Hash Cond: (cf.customfieldoptionid = cfo.customfieldoptionid)
92. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18.65..229.60 rows=94 width=56) (actual rows= loops=)

  • Hash Cond: (cf.customfieldid = b.customfieldid)
93. 0.000 0.000 ↓ 0.0

Nested Loop (cost=13.50..224.09 rows=94 width=44) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on customfieldsdict cfd (cost=8.64..21.06 rows=2 width=4) (actual rows= loops=)

  • Recheck Cond: ((servicetypeid = 5) OR (servicetypeid = '-3000'::integer))
  • Filter: (((ordering IS NULL) OR (ordering = 0)) AND (customfielddictid = ANY ('{222664,233684,246207,246264,209244,209284,209285,209286,209287,209288,209289,209305,246225,247204,247205,247206,247207,247208,247209,247210,247211,247212,247213,247214,247215,247216,247217,247218,247219,247220,247221,247222,247223,247224,247225,247226,247227,247228}'::integer[])))
95. 0.000 0.000 ↓ 0.0

BitmapOr (cost=8.64..8.64 rows=13 width=0) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ind1_customfieldsdict (cost=0.00..4.34 rows=9 width=0) (actual rows= loops=)

  • Index Cond: (servicetypeid = 5)
97. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (servicetypeid = '-3000'::integer)
98. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on customfield cf (cost=4.86..100.95 rows=56 width=44) (actual rows= loops=)

  • Recheck Cond: ((customfielddictid = cfd.customfielddictid) AND (contractid IS NOT NULL))
99. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on unq1_customfield (cost=0.00..4.85 rows=56 width=0) (actual rows= loops=)

  • Index Cond: ((customfielddictid = cfd.customfielddictid) AND (contractid IS NOT NULL))
100. 0.000 0.000 ↓ 0.0

Hash (cost=3.40..3.40 rows=140 width=28) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Seq Scan on customfieldfilename b (cost=0.00..3.40 rows=140 width=28) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Hash (cost=1.61..1.61 rows=61 width=14) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Seq Scan on customfieldsoptions cfo (cost=0.00..1.61 rows=61 width=14) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=62) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Seq Scan on majortypes t1e (cost=0.00..1.05 rows=5 width=62) (actual rows= loops=)