explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7aLh

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

Unique (cost=72,299,000.44..72,359,922.75 rows=594,364 width=717) (actual rows= loops=)

2.          

Initplan (for Unique)

3. 0.000 0.000 ↓ 0.0

Seq Scan on property (cost=0.00..5.00 rows=1 width=7) (actual rows= loops=)

  • Filter: ((propertycode)::text = 'defaultCountryCode'::text)
4. 0.000 0.000 ↓ 0.0

Sort (cost=72,298,995.44..72,300,481.35 rows=594,364 width=717) (actual rows= loops=)

  • Sort Key: cu.customernbr, consumption.consumptionid, cu.individual, cu.customerdatastatusparamid, crm.profilecodeid, co.supplierref, co.deliverycategoryparamid, co.contractstatusparamid, ch.productcodeparamid, co.contractcategoryparamid, deliverypoint.gsrn, (COALESCE(codetranslation_1.shortdescriptionlang1, codetranslation.shortdescriptionlang1)), deliverypoint.market, generalconfig.readingfrequency, (COALESCE(logicalregister.slpid, generalconfig.slpid)), dgo_actor.actorname, generalconfig.netarea, ((SubPlan 2)), ((SubPlan 4)), message.externaltimestamp, consumption.sourceid, consumption.historical, consumption.ignored, consumptiondetail.measurementnature, consumption.fromdate, consumption.todate, consumptiondetail.value, measurementnature.unit, consumption.creator, consumption.creationdate, consumption.latestmodifier, consumption.latestmodifdate, consumptiondetail.timeframe, consumptiondetail.direction, consumptiondetail.valuelist, consumptiondetail.qualitylist, ((SubPlan 5))
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,421,710.51..72,053,059.50 rows=594,364 width=717) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,421,645.23..7,774,857.34 rows=594,364 width=701) (actual rows= loops=)

  • Hash Cond: (cdp.contractid = co.contractid)
7. 0.000 0.000 ↓ 0.0

Merge Join (cost=5,526,074.66..5,865,170.63 rows=594,364 width=663) (actual rows= loops=)

  • Merge Cond: (generalconfig.generalconfigid = ((SubPlan 7)))
8. 0.000 0.000 ↓ 0.0

Sort (cost=47,247.69..48,023.13 rows=310,176 width=44) (actual rows= loops=)

  • Sort Key: generalconfig.generalconfigid
9. 0.000 0.000 ↓ 0.0

Seq Scan on generalconfig (cost=0.00..18,955.41 rows=310,176 width=44) (actual rows= loops=)

  • Filter: ((deleted IS FALSE) AND ((direction)::text = 'CONSUMPTION'::text))
10. 0.000 0.000 ↓ 0.0

Materialize (cost=5,478,826.96..5,483,403.20 rows=915,247 width=623) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=5,478,826.96..5,481,115.08 rows=915,247 width=623) (actual rows= loops=)

  • Sort Key: ((SubPlan 7))
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,529,036.81..5,134,807.11 rows=915,247 width=623) (actual rows= loops=)

  • Hash Cond: ((deliverypointcomponent.deliverypointcomponentid = deliveryperiod.deliverypointcomponentid) AND (consumption.deliveryperiodid = deliveryperiod.deliveryperiodid))
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,514,539.50..5,095,177.93 rows=913,884 width=623) (actual rows= loops=)

  • Hash Cond: (consumption.deliverypointid = deliverypoint.deliverypointid)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,508,864.07..5,067,797.75 rows=913,884 width=601) (actual rows= loops=)

  • Hash Cond: (processmessages.processid = process.processid)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,278,130.55..4,818,786.56 rows=913,884 width=605) (actual rows= loops=)

  • Hash Cond: (consumption.deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,271,325.53..4,790,276.79 rows=913,884 width=589) (actual rows= loops=)

  • Hash Cond: ((consumptiondetail.measurementnature)::text = (measurementnature.nature)::text)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,271,324.13..4,777,709.48 rows=913,884 width=557) (actual rows= loops=)

  • Hash Cond: (consumptiondetail.logicalregisterid = logicalregister.logicalregisterid)
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,247,986.46..4,741,805.90 rows=913,884 width=529) (actual rows= loops=)

  • Hash Cond: (consumptiondetail.consumptionid = consumption.consumptionid)
19. 0.000 0.000 ↓ 0.0

Seq Scan on consumptiondetail (cost=0.00..429,334.20 rows=4,919,680 width=450) (actual rows= loops=)

  • Filter: ((ignored IS FALSE) AND ((measurementnature)::text <> ALL ('{REACTIVE_CAPACITIVE_ENERGY,REACTIVE_CAPACITIVE_POWER,REACTIVE_INDUCTIVE_ENERGY,REACTIVE_INDUCTIVE_POWER}'::text[])))
20. 0.000 0.000 ↓ 0.0

Hash (cost=4,242,247.71..4,242,247.71 rows=459,100 width=83) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=357,062.88..4,242,247.71 rows=459,100 width=83) (actual rows= loops=)

  • Hash Cond: (consumption.messageid = processmessages.messageid)
22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.56..3,815,997.14 rows=459,100 width=83) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on consumption (cost=0.00..110,472.14 rows=459,100 width=92) (actual rows= loops=)

  • Filter: ((deleted IS FALSE) AND ((measurementfrequency)::text <> ALL ('{HOURLY,QUARTER_HOURLY}'::text[])) AND ((todate IS NULL) OR (todate > '2019-01-01'::date)) AND (fromdate <= '2019-07-15'::date) AND ((status)::text = 'ACTUAL'::text))
24. 0.000 0.000 ↓ 0.0

Index Scan using message_pk on message (cost=0.56..8.06 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (consumption.messageid = messageid)
25. 0.000 0.000 ↓ 0.0

Hash (cost=167,057.14..167,057.14 rows=11,581,214 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on processmessages (cost=0.00..167,057.14 rows=11,581,214 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=16,013.41..16,013.41 rows=585,941 width=36) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on logicalregister (cost=0.00..16,013.41 rows=585,941 width=36) (actual rows= loops=)

  • Filter: (ignored IS FALSE)
29. 0.000 0.000 ↓ 0.0

Hash (cost=1.18..1.18 rows=18 width=64) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on measurementnature (cost=0.00..1.18 rows=18 width=64) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=5,059.36..5,059.36 rows=139,653 width=20) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17.60..5,059.36 rows=139,653 width=20) (actual rows= loops=)

  • Hash Cond: ((deliverypointcomponent.componentnature)::text = (componentnature.componentnature)::text)
33. 0.000 0.000 ↓ 0.0

Seq Scan on deliverypointcomponent (cost=0.00..3,121.53 rows=139,653 width=12) (actual rows= loops=)

  • Filter: (deleted IS FALSE)
34. 0.000 0.000 ↓ 0.0

Hash (cost=17.58..17.58 rows=1 width=48) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.56..17.58 rows=1 width=48) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..9.27 rows=1 width=40) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on componentnature (cost=0.00..1.01 rows=1 width=32) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Index Scan using codetranslation_category_code_countrycode_idx on codetranslation (cost=0.28..8.25 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (((category)::text = 'componentnature'::text) AND ((code)::text = (componentnature.componentnature)::text) AND (countrycode IS NULL))
39. 0.000 0.000 ↓ 0.0

Index Scan using codetranslation_category_code_countrycode_idx on codetranslation codetranslation_1 (cost=0.28..8.30 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (((category)::text = 'componentnature'::text) AND ((code)::text = (componentnature.componentnature)::text) AND ((countrycode)::text = ($7)::text))
40. 0.000 0.000 ↓ 0.0

Hash (cost=210,306.10..210,306.10 rows=1,634,193 width=4) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=62,151.34..210,306.10 rows=1,634,193 width=4) (actual rows= loops=)

  • Hash Cond: (transactioncrossref.processid = process.processid)
42. 0.000 0.000 ↓ 0.0

Seq Scan on transactioncrossref (cost=0.00..117,701.48 rows=1,522,664 width=4) (actual rows= loops=)

  • Filter: (((type)::text = 'STANDARD'::text) AND ((counterpart)::text = 'BackOffice'::text))
43. 0.000 0.000 ↓ 0.0

Hash (cost=41,723.93..41,723.93 rows=1,634,193 width=4) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on process (cost=0.00..41,723.93 rows=1,634,193 width=4) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=3,929.08..3,929.08 rows=139,708 width=26) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on deliverypoint (cost=0.00..3,929.08 rows=139,708 width=26) (actual rows= loops=)

  • Filter: (deleted IS FALSE)
47. 0.000 0.000 ↓ 0.0

Hash (cost=12,302.11..12,302.11 rows=146,347 width=16) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=5,761.90..12,302.11 rows=146,347 width=16) (actual rows= loops=)

  • Hash Cond: (cdp.deliveryperiodid = deliveryperiod.deliveryperiodid)
49. 0.000 0.000 ↓ 0.0

Seq Scan on contractdelivery cdp (cost=0.00..3,064.47 rows=146,347 width=12) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash (cost=3,935.29..3,935.29 rows=146,129 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on deliveryperiod (cost=0.00..3,935.29 rows=146,129 width=8) (actual rows= loops=)

  • Filter: (deleted IS FALSE)
52.          

SubPlan (for Hash Left Join)

53. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.27 rows=1 width=0) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash (cost=1,893,751.24..1,893,751.24 rows=145,546 width=46) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=26,065.90..1,893,751.24 rows=145,546 width=46) (actual rows= loops=)

  • Hash Cond: (cu.customerid = crm.customerid)
56. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=23,165.25..1,887,575.80 rows=145,546 width=46) (actual rows= loops=)

  • Hash Cond: (co.customerid = cu.customerid)
57. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=18,566.59..1,879,702.36 rows=145,546 width=35) (actual rows= loops=)

  • Merge Cond: (co.contractid = ch.contractid)
  • Join Filter: (ch.contracthistentry = (SubPlan 9))
58. 0.000 0.000 ↓ 0.0

Index Scan using contract_pkey on contract co (cost=0.42..422,906.20 rows=145,546 width=31) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Sort (cost=18,566.12..18,990.65 rows=169,810 width=12) (actual rows= loops=)

  • Sort Key: ch.contractid
60. 0.000 0.000 ↓ 0.0

Seq Scan on contracthistory ch (cost=0.00..3,815.10 rows=169,810 width=12) (actual rows= loops=)

61.          

SubPlan (for Merge Left Join)

62. 0.000 0.000 ↓ 0.0

Limit (cost=8.44..8.45 rows=1 width=0) (actual rows= loops=)

63.          

Initplan (for Limit)

64. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using "Key4" on contracthistory ch2 (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((contractid = co.contractid) AND (contracthistentry IS NOT NULL))
66. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=3,797.18..3,797.18 rows=64,118 width=15) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on customer cu (cost=0.00..3,797.18 rows=64,118 width=15) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=2,099.18..2,099.18 rows=64,118 width=8) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on crmdata crm (cost=0.00..2,099.18 rows=64,118 width=8) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=65.29..67.10 rows=1 width=28) (actual rows= loops=)

  • Hash Cond: (dgo_actor.actorid = actor_dgo.actorid)
72. 0.000 0.000 ↓ 0.0

Seq Scan on actor dgo_actor (cost=0.00..1.58 rows=58 width=24) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Hash (cost=65.28..65.28 rows=1 width=12) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=36.08..65.28 rows=1 width=12) (actual rows= loops=)

  • Join Filter: (((actor_brp.externalid)::text = (deliveryperiodbrpjournal.brp)::text) AND ((actor_brp.marketcode)::text = (dp.market)::text))
75. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=36.08..63.52 rows=1 width=17) (actual rows= loops=)

  • Join Filter: (((actor_sup.externalid)::text = (deliveryperiod_1.supplier)::text) AND ((actor_sup.marketcode)::text = (dp.market)::text))
76. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=36.08..61.75 rows=1 width=28) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=18.34..29.41 rows=1 width=26) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=9.46..13.24 rows=1 width=11) (actual rows= loops=)

  • Hash Cond: (((actor_dgo.marketcode)::text = (dp.market)::text) AND ((actor_dgo.externalid)::text = (dp.gridoperator)::text))
  • Join Filter: ((dp.regulator)::text = ((SubPlan 10))::text)
79. 0.000 0.000 ↓ 0.0

Seq Scan on actor actor_dgo (cost=0.00..1.73 rows=52 width=19) (actual rows= loops=)

  • Filter: ((role)::text = ANY ('{D,T}'::text[]))
80. 0.000 0.000 ↓ 0.0

Hash (cost=9.45..9.45 rows=1 width=29) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.42..9.45 rows=1 width=29) (actual rows= loops=)

  • Join Filter: ((actor_reg.externalid)::text = (dp.regulator)::text)
82. 0.000 0.000 ↓ 0.0

Index Scan using deliverypoint_pkey on deliverypoint dp (cost=0.42..7.71 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
  • Filter: (deleted IS FALSE)
83. 0.000 0.000 ↓ 0.0

Seq Scan on actor actor_reg (cost=0.00..1.73 rows=1 width=16) (actual rows= loops=)

  • Filter: ((role)::text = 'RR'::text)
84.          

SubPlan (for Hash Right Join)

85. 0.000 0.000 ↓ 0.0

Seq Scan on gridoperator dgo (cost=0.00..1.65 rows=1 width=12) (actual rows= loops=)

  • Filter: (gridoperatorid = actor_dgo.actorid)
86. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_1 (cost=8.87..16.16 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (deliveryperiodid = (SubPlan 11))
  • Filter: (deleted IS FALSE)
87.          

SubPlan (for Index Scan)

88. 0.000 0.000 ↓ 0.0

Limit (cost=8.45..8.45 rows=1 width=8) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Sort (cost=8.45..8.45 rows=1 width=8) (actual rows= loops=)

  • Sort Key: deliveryperiod_3.todate
90. 0.000 0.000 ↓ 0.0

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_3 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (deliverypointid = dp.deliverypointid)
  • Filter: (deleted IS FALSE)
91. 0.000 0.000 ↓ 0.0

Nested Loop (cost=17.74..32.33 rows=1 width=10) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiodbrpjournal_pk on deliveryperiodbrpjournal (cost=17.32..24.61 rows=1 width=14) (actual rows= loops=)

  • Index Cond: (deliveryperiodbrpjournalid = (SubPlan 12))
93.          

SubPlan (for Index Scan)

94. 0.000 0.000 ↓ 0.0

Limit (cost=16.90..16.90 rows=1 width=8) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Sort (cost=16.90..16.90 rows=1 width=8) (actual rows= loops=)

  • Sort Key: deliveryperiodbrpjournal_1.todate
96. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Index Scan using "IX_deliveryperiodbrpjournal_delivetyperiodid_deliveryperiodbrpj" on deliveryperiodbrpjournal deliveryperiodbrpjournal_1 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
98. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_4 (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
  • Filter: (deleted IS FALSE)
99. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_2 (cost=0.42..7.71 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (deliveryperiodid = deliveryperiodbrpjournal.deliveryperiodid)
  • Filter: (deleted IS FALSE)
100. 0.000 0.000 ↓ 0.0

Seq Scan on actor actor_sup (cost=0.00..1.73 rows=3 width=15) (actual rows= loops=)

  • Filter: ((role)::text = 'S'::text)
101. 0.000 0.000 ↓ 0.0

Seq Scan on actor actor_brp (cost=0.00..1.73 rows=2 width=19) (actual rows= loops=)

  • Filter: ((role)::text = 'B'::text)
102.          

SubPlan (for Nested Loop Left Join)

103. 0.000 0.000 ↓ 0.0

Index Only Scan using decentralizedproduction_physicalconfig_idx on decentralizedproduction (cost=0.41..12.45 rows=2 width=0) (actual rows= loops=)

  • Index Cond: (physicalconfigid = $1)
104.          

Initplan (for Index Only Scan)

105. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.27 rows=1 width=0) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.10..17.15 rows=1 width=0) (actual rows= loops=)

107.          

Initplan (for Nested Loop)

108. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.27 rows=1 width=0) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Index Scan using meter_physicalconfigid_idx on meter m (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (physicalconfigid = $3)
  • Filter: ((metertype)::text = 'SMART_METER'::text)
110. 0.000 0.000 ↓ 0.0

Index Scan using physicalconfig_pk on physicalconfig (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (physicalconfigid = $3)
  • Filter: (deleted IS FALSE)
111. 0.000 0.000 ↓ 0.0

Limit (cost=82.74..82.75 rows=1 width=16) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Sort (cost=82.74..82.75 rows=3 width=16) (actual rows= loops=)

  • Sort Key: (COALESCE(id.invdocref, (i.invoiceid)::character varying))
113. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.29..82.73 rows=3 width=16) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..81.14 rows=3 width=4) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

Index Scan using idx_invoicedconsumption_consumptionid on invoicedconsumption ic (cost=0.43..13.46 rows=8 width=4) (actual rows= loops=)

  • Index Cond: (consumptionid = consumption.consumptionid)
  • Filter: ((status)::text !~~ 'deprecated%'::text)
116. 0.000 0.000 ↓ 0.0

Index Only Scan using invoice_pkey on invoice i (cost=0.43..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (invoiceid = ic.invoiceid)
117. 0.000 0.000 ↓ 0.0

Index Scan using invdoc_invoiceid on invdoc id (cost=0.43..0.51 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (invoiceid = i.invoiceid)