explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0WbG

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

Aggregate (cost=37,788.15..37,788.16 rows=1 width=8) (actual rows= loops=)

2.          

CTE maxcontracthistentry

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=474.65..578.75 rows=10,411 width=8) (actual rows= loops=)

  • Group Key: contracthistory.contractid
4. 0.000 0.000 ↓ 0.0

Seq Scan on contracthistory (cost=0.00..388.43 rows=17,243 width=8) (actual rows= loops=)

5.          

CTE componentnature

6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6.26..12.33 rows=11 width=64) (actual rows= loops=)

  • Hash Cond: ((componentnature.componentnature)::text = (codetranslation.code)::text)
7.          

Initplan (for Hash Left Join)

8. 0.000 0.000 ↓ 0.0

Index Scan using property_pkey on property (cost=0.27..2.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((propertycode)::text = 'defaultCountryCode'::text)
9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1.53..7.55 rows=11 width=40) (actual rows= loops=)

  • Hash Cond: ((codetranslation_1.code)::text = (componentnature.componentnature)::text)
10. 0.000 0.000 ↓ 0.0

Index Scan using codetranslation_category_code_countrycode_idx on codetranslation codetranslation_1 (cost=0.28..6.27 rows=5 width=20) (actual rows= loops=)

  • Index Cond: (((category)::text = 'componentnature'::text) AND ((countrycode)::text = ($1)::text))
11. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=32) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on componentnature (cost=0.00..1.11 rows=11 width=32) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=2.43..2.43 rows=1 width=20) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (((category)::text = 'componentnature'::text) AND (countrycode IS NULL))
15.          

CTE bestgeneralconfig

16. 0.000 0.000 ↓ 0.0

Seq Scan on contractdelivery (cost=0.00..2,959.89 rows=10,626 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=11.77..34,237.17 rows=2 width=0) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.63..34,192.70 rows=2 width=4) (actual rows= loops=)

  • Join Filter: (co.contractid = mche.contractid)
19. 0.000 0.000 ↓ 0.0

CTE Scan on maxcontracthistentry mche (cost=0.00..208.22 rows=10,411 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Materialize (cost=3.63..33,672.15 rows=2 width=12) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.63..33,672.14 rows=2 width=12) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.34..33,671.43 rows=2 width=16) (actual rows= loops=)

  • Join Filter: (cdp.contractdeliveryid = bgc.contractdeliveryid)
23. 0.000 0.000 ↓ 0.0

CTE Scan on bestgeneralconfig bgc (cost=0.00..212.52 rows=10,626 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Materialize (cost=3.34..33,140.13 rows=2 width=16) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.34..33,140.12 rows=2 width=16) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.06..33,139.46 rows=2 width=12) (actual rows= loops=)

  • Join Filter: ((cn.componentnature)::text = (deliverypointcomponent.componentnature)::text)
27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.06..33,138.85 rows=2 width=20) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.77..33,138.20 rows=2 width=20) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.49..33,137.54 rows=2 width=20) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.21..33,136.78 rows=2 width=24) (actual rows= loops=)

  • Join Filter: (processmessages.processid = process.processid)
31. 0.000 0.000 ↓ 0.0

Merge Join (cost=2.21..33,122.62 rows=2 width=24) (actual rows= loops=)

  • Merge Cond: (deliveryperiod.deliveryperiodid = consumption.deliveryperiodid)
  • Join Filter: (consumption.deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
32. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiod_pkey on deliveryperiod (cost=0.29..306.27 rows=8,932 width=8) (actual rows= loops=)

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

Materialize (cost=1.74..38,511.02 rows=20,704 width=28) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.74..38,459.26 rows=20,704 width=28) (actual rows= loops=)

  • Merge Cond: (cdp.deliveryperiodid = consumption.deliveryperiodid)
35. 0.000 0.000 ↓ 0.0

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp (cost=0.29..335.68 rows=10,626 width=12) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Materialize (cost=1.26..44,805.11 rows=20,746 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..44,753.24 rows=20,746 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.12..41,329.90 rows=20,746 width=33) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.70..8,941.06 rows=9,358 width=20) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using "IX_consumption_deliveryperiodid_consumption" on consumption (cost=0.42..6,072.87 rows=9,358 width=35) (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))
41. 0.000 0.000 ↓ 0.0

Index Scan using "IX_processmessages_messageid_processmessages" on processmessages (cost=0.28..0.30 rows=1 width=8) (actual rows= loops=)

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

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail (cost=0.42..3.43 rows=3 width=25) (actual rows= loops=)

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

Index Only Scan using measurementnature_pk on measurementnature (cost=0.14..0.16 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (nature = (consumptiondetail.measurementnature)::text)
44. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..7.59 rows=239 width=4) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on process (cost=0.00..6.39 rows=239 width=4) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Index Scan using "IX_transactioncrossref_processid_transactioncrossref" on transactioncrossref (cost=0.27..0.37 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (processid = process.processid)
  • Filter: (((type)::text = 'STANDARD'::text) AND ((counterpart)::text = 'BackOffice'::text))
47. 0.000 0.000 ↓ 0.0

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.29..0.32 rows=1 width=4) (actual rows= loops=)

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

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.29..0.32 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (deliverypointcomponentid = consumption.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
49. 0.000 0.000 ↓ 0.0

CTE Scan on componentnature cn (cost=0.00..0.22 rows=11 width=32) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Index Scan using contract_pkey on contract co (cost=0.29..0.32 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (contractid = cdp.contractid)
51. 0.000 0.000 ↓ 0.0

Index Scan using generalconfig_pkey on generalconfig (cost=0.29..0.35 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (generalconfigid = bgc.generalconfigid)
  • Filter: ((deleted IS FALSE) AND ((direction)::text = 'CONSUMPTION'::text))
52. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8.14..22.23 rows=1 width=8) (actual rows= loops=)

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

Nested Loop Left Join (cost=8.14..19.24 rows=1 width=22) (actual rows= loops=)

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

Nested Loop Left Join (cost=8.14..16.31 rows=1 width=33) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.94..11.01 rows=1 width=22) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.34..8.36 rows=1 width=7) (actual rows= loops=)

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

Hash Right Join (cost=0.34..5.43 rows=1 width=13) (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 5))::text)
58. 0.000 0.000 ↓ 0.0

Seq Scan on actor actor_dgo (cost=0.00..2.90 rows=60 width=15) (actual rows= loops=)

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

Hash (cost=0.32..0.32 rows=1 width=21) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Scan using deliverypoint_pkey on deliverypoint dp (cost=0.29..0.32 rows=1 width=21) (actual rows= loops=)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
  • Filter: (deleted IS FALSE)
61.          

SubPlan (for Hash Right Join)

62. 0.000 0.000 ↓ 0.0

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

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

Seq Scan on actor actor_reg (cost=0.00..2.90 rows=3 width=13) (actual rows= loops=)

  • Filter: ((role)::text = 'RR'::text)
64. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (deliveryperiodid = (SubPlan 6))
  • Filter: (deleted IS FALSE)
65.          

SubPlan (for Index Scan)

66. 0.000 0.000 ↓ 0.0

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

67. 0.000 0.000 ↓ 0.0

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

  • Sort Key: deliveryperiod_3.todate DESC
68. 0.000 0.000 ↓ 0.0

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

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

Nested Loop (cost=5.20..5.28 rows=1 width=19) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Index Scan using deliveryperiodbrpjournal_pk on deliveryperiodbrpjournal (cost=4.91..4.95 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (deliveryperiodbrpjournalid = (SubPlan 7))
71.          

SubPlan (for Index Scan)

72. 0.000 0.000 ↓ 0.0

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

73. 0.000 0.000 ↓ 0.0

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

  • Sort Key: deliveryperiodbrpjournal_1.todate DESC
74. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..4.61 rows=1 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

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

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

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

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

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

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

Seq Scan on actor actor_sup (cost=0.00..2.90 rows=2 width=11) (actual rows= loops=)

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

Seq Scan on actor actor_brp (cost=0.00..2.90 rows=6 width=15) (actual rows= loops=)

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