explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nmcw

Settings
# exclusive inclusive rows x rows loops node
1. 9.618 165,059.854 ↑ 1.0 1 1

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

2.          

CTE maxcontracthistentry

3. 39.221 52.160 ↓ 1.0 10,414 1

HashAggregate (cost=474.65..578.75 rows=10,411 width=8) (actual time=31.261..52.160 rows=10,414 loops=1)

  • Group Key: contracthistory.contractid
4. 12.939 12.939 ↓ 1.0 17,246 1

Seq Scan on contracthistory (cost=0.00..388.43 rows=17,243 width=8) (actual time=0.907..12.939 rows=17,246 loops=1)

5.          

CTE componentnature

6. 0.000 0.661 ↑ 1.0 11 1

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

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

Initplan (for Hash Left Join)

8. 0.190 0.190 ↑ 1.0 1 1

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

  • Index Cond: ((propertycode)::text = 'defaultCountryCode'::text)
9. 0.042 0.564 ↑ 1.0 11 1

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

  • Hash Cond: ((codetranslation_1.code)::text = (componentnature.componentnature)::text)
10. 0.494 0.494 ↓ 2.2 11 1

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

  • Index Cond: (((category)::text = 'componentnature'::text) AND ((countrycode)::text = ($1)::text))
11. 0.010 0.028 ↑ 1.0 11 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.018 0.018 ↑ 1.0 11 1

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

13. 0.001 0.021 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
14. 0.020 0.020 ↓ 0.0 0 1

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

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

CTE bestgeneralconfig

16. 3,943.891 3,943.891 ↓ 1.0 10,629 1

Seq Scan on contractdelivery (cost=0.00..2,959.89 rows=10,626 width=8) (actual time=1.308..3,943.891 rows=10,629 loops=1)

17. 24.839 165,050.236 ↓ 5,590.0 11,180 1

Nested Loop Left Join (cost=11.77..34,237.17 rows=2 width=0) (actual time=76,802.970..165,050.236 rows=11,180 loops=1)

18. 52,957.589 159,949.677 ↓ 5,590.0 11,180 1

Nested Loop (cost=3.63..34,192.70 rows=2 width=4) (actual time=76,801.818..159,949.677 rows=11,180 loops=1)

  • Join Filter: (co.contractid = mche.contractid)
  • Rows Removed by Join Filter: 116,417,340
19. 81.964 81.964 ↓ 1.0 10,414 1

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

20. 24,165.308 106,910.124 ↓ 5,590.0 11,180 10,414

Materialize (cost=3.63..33,672.15 rows=2 width=12) (actual time=0.243..10.266 rows=11,180 loops=10,414)

21. 20.571 82,744.816 ↓ 5,590.0 11,180 1

Nested Loop (cost=3.63..33,672.14 rows=2 width=12) (actual time=2,529.016..82,744.816 rows=11,180 loops=1)

22. 52,318.669 82,618.819 ↓ 5,857.0 11,714 1

Nested Loop (cost=3.34..33,671.43 rows=2 width=16) (actual time=2,528.528..82,618.819 rows=11,714 loops=1)

  • Join Filter: (cdp.contractdeliveryid = bgc.contractdeliveryid)
  • Rows Removed by Join Filter: 124,496,392
23. 3,961.488 3,961.488 ↓ 1.0 10,629 1

CTE Scan on bestgeneralconfig bgc (cost=0.00..212.52 rows=10,626 width=8) (actual time=1.311..3,961.488 rows=10,629 loops=1)

24. 23,840.431 26,338.662 ↓ 5,857.0 11,714 10,629

Materialize (cost=3.34..33,140.13 rows=2 width=16) (actual time=0.000..2.478 rows=11,714 loops=10,629)

25. 10.080 2,498.231 ↓ 5,857.0 11,714 1

Nested Loop (cost=3.34..33,140.12 rows=2 width=16) (actual time=1.525..2,498.231 rows=11,714 loops=1)

26. 73.564 2,394.439 ↓ 5,857.0 11,714 1

Nested Loop Left Join (cost=3.06..33,139.46 rows=2 width=12) (actual time=1.235..2,394.439 rows=11,714 loops=1)

  • Join Filter: ((cn.componentnature)::text = (deliverypointcomponent.componentnature)::text)
  • Rows Removed by Join Filter: 117,140
27. 13.336 2,250.591 ↓ 5,857.0 11,714 1

Nested Loop (cost=3.06..33,138.85 rows=2 width=20) (actual time=0.592..2,250.591 rows=11,714 loops=1)

28. 16.191 2,155.257 ↓ 5,857.0 11,714 1

Nested Loop Left Join (cost=2.77..33,138.20 rows=2 width=20) (actual time=0.579..2,155.257 rows=11,714 loops=1)

29. 18.860 2,057.068 ↓ 5,857.0 11,714 1

Nested Loop Left Join (cost=2.49..33,137.54 rows=2 width=20) (actual time=0.565..2,057.068 rows=11,714 loops=1)

30. 968.298 2,038.208 ↓ 5,857.0 11,714 1

Nested Loop Left Join (cost=2.21..33,136.78 rows=2 width=24) (actual time=0.562..2,038.208 rows=11,714 loops=1)

  • Join Filter: (processmessages.processid = process.processid)
  • Rows Removed by Join Filter: 2,846,486
31. 20.049 507.638 ↓ 5,857.0 11,714 1

Merge Join (cost=2.21..33,122.62 rows=2 width=24) (actual time=0.205..507.638 rows=11,714 loops=1)

  • Merge Cond: (deliveryperiod.deliveryperiodid = consumption.deliveryperiodid)
  • Join Filter: (consumption.deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
32. 20.799 20.799 ↑ 1.0 8,832 1

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

  • Filter: (deleted IS FALSE)
33. 6.662 466.790 ↑ 1.8 11,714 1

Materialize (cost=1.74..38,511.02 rows=20,704 width=28) (actual time=0.177..466.790 rows=11,714 loops=1)

34. 17.354 460.128 ↑ 1.8 11,714 1

Merge Join (cost=1.74..38,459.26 rows=20,704 width=28) (actual time=0.175..460.128 rows=11,714 loops=1)

  • Merge Cond: (cdp.deliveryperiodid = consumption.deliveryperiodid)
35. 20.803 20.803 ↑ 1.0 10,603 1

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

36. 7.177 421.971 ↑ 1.8 11,715 1

Materialize (cost=1.26..44,805.11 rows=20,746 width=16) (actual time=0.147..421.971 rows=11,715 loops=1)

37. 13.282 414.794 ↑ 2.1 10,036 1

Nested Loop (cost=1.26..44,753.24 rows=20,746 width=16) (actual time=0.143..414.794 rows=10,036 loops=1)

38. 6.427 331.260 ↑ 2.1 10,036 1

Nested Loop (cost=1.12..41,329.90 rows=20,746 width=33) (actual time=0.075..331.260 rows=10,036 loops=1)

39. 4.192 226.881 ↑ 1.5 6,122 1

Nested Loop Left Join (cost=0.70..8,941.06 rows=9,358 width=20) (actual time=0.055..226.881 rows=6,122 loops=1)

40. 192.084 192.084 ↑ 1.5 6,121 1

Index Scan using "IX_consumption_deliveryperiodid_consumption" on consumption (cost=0.42..6,072.87 rows=9,358 width=35) (actual time=0.042..192.084 rows=6,121 loops=1)

  • 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))
  • Rows Removed by Filter: 89,943
41. 30.605 30.605 ↓ 0.0 0 6,121

Index Scan using "IX_processmessages_messageid_processmessages" on processmessages (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=6,121)

  • Index Cond: (consumption.messageid = messageid)
42. 97.952 97.952 ↑ 1.5 2 6,122

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail (cost=0.42..3.43 rows=3 width=25) (actual time=0.011..0.016 rows=2 loops=6,122)

  • 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[])))
  • Rows Removed by Filter: 0
43. 70.252 70.252 ↑ 1.0 1 10,036

Index Only Scan using measurementnature_pk on measurementnature (cost=0.14..0.16 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=10,036)

  • Index Cond: (nature = (consumptiondetail.measurementnature)::text)
  • Heap Fetches: 10,036
44. 562.138 562.272 ↓ 1.0 243 11,714

Materialize (cost=0.00..7.59 rows=239 width=4) (actual time=0.000..0.048 rows=243 loops=11,714)

45. 0.134 0.134 ↓ 1.0 243 1

Seq Scan on process (cost=0.00..6.39 rows=239 width=4) (actual time=0.017..0.134 rows=243 loops=1)

46. 0.000 0.000 ↓ 0.0 0 11,714

Index Scan using "IX_transactioncrossref_processid_transactioncrossref" on transactioncrossref (cost=0.27..0.37 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=11,714)

  • Index Cond: (processid = process.processid)
  • Filter: (((type)::text = 'STANDARD'::text) AND ((counterpart)::text = 'BackOffice'::text))
  • Rows Removed by Filter: 0
47. 81.998 81.998 ↑ 1.0 1 11,714

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.29..0.32 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=11,714)

  • Index Cond: (deliverypointid = consumption.deliverypointid)
  • Filter: (deleted IS FALSE)
48. 81.998 81.998 ↑ 1.0 1 11,714

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.29..0.32 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=11,714)

  • Index Cond: (deliverypointcomponentid = consumption.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
49. 70.284 70.284 ↑ 1.0 11 11,714

CTE Scan on componentnature cn (cost=0.00..0.22 rows=11 width=32) (actual time=0.001..0.006 rows=11 loops=11,714)

50. 93.712 93.712 ↑ 1.0 1 11,714

Index Scan using contract_pkey on contract co (cost=0.29..0.32 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=11,714)

  • Index Cond: (contractid = cdp.contractid)
51. 105.426 105.426 ↑ 1.0 1 11,714

Index Scan using generalconfig_pkey on generalconfig (cost=0.29..0.35 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=11,714)

  • Index Cond: (generalconfigid = bgc.generalconfigid)
  • Filter: ((deleted IS FALSE) AND ((direction)::text = 'CONSUMPTION'::text))
  • Rows Removed by Filter: 0
52. 100.620 5,075.720 ↑ 1.0 1 11,180

Nested Loop Left Join (cost=8.14..22.23 rows=1 width=8) (actual time=0.268..0.454 rows=1 loops=11,180)

  • Join Filter: (((actor_brp.externalid)::text = (deliveryperiodbrpjournal.brp)::text) AND ((actor_brp.marketcode)::text = (dp.market)::text))
  • Rows Removed by Join Filter: 5
53. 67.080 4,393.740 ↑ 1.0 1 11,180

Nested Loop Left Join (cost=8.14..19.24 rows=1 width=22) (actual time=0.249..0.393 rows=1 loops=11,180)

  • Join Filter: (((actor_sup.externalid)::text = (deliveryperiod_1.supplier)::text) AND ((actor_sup.marketcode)::text = (dp.market)::text))
  • Rows Removed by Join Filter: 1
54. 33.540 3,756.480 ↑ 1.0 1 11,180

Nested Loop Left Join (cost=8.14..16.31 rows=1 width=33) (actual time=0.233..0.336 rows=1 loops=11,180)

55. 268.320 3,186.300 ↑ 1.0 1 11,180

Nested Loop Left Join (cost=2.94..11.01 rows=1 width=22) (actual time=0.185..0.285 rows=1 loops=11,180)

56. 156.520 2,817.360 ↑ 1.0 1 11,180

Nested Loop Left Join (cost=0.34..8.36 rows=1 width=7) (actual time=0.152..0.252 rows=1 loops=11,180)

  • Join Filter: ((actor_reg.externalid)::text = (dp.regulator)::text)
  • Rows Removed by Join Filter: 2
57. 800.145 2,124.200 ↑ 1.0 1 11,180

Hash Right Join (cost=0.34..5.43 rows=1 width=13) (actual time=0.127..0.190 rows=1 loops=11,180)

  • 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)
  • Rows Removed by Join Filter: 0
58. 715.520 715.520 ↑ 1.0 60 11,180

Seq Scan on actor actor_dgo (cost=0.00..2.90 rows=60 width=15) (actual time=0.018..0.064 rows=60 loops=11,180)

  • Filter: ((role)::text = ANY ('{D,T}'::text[]))
  • Rows Removed by Filter: 12
59. 44.720 201.240 ↑ 1.0 1 11,180

Hash (cost=0.32..0.32 rows=1 width=21) (actual time=0.018..0.018 rows=1 loops=11,180)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 156.520 156.520 ↑ 1.0 1 11,180

Index Scan using deliverypoint_pkey on deliverypoint dp (cost=0.29..0.32 rows=1 width=21) (actual time=0.013..0.014 rows=1 loops=11,180)

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

SubPlan (for Hash Right Join)

62. 407.295 407.295 ↑ 1.0 1 11,637

Seq Scan on gridoperator dgo (cost=0.00..1.73 rows=1 width=32) (actual time=0.024..0.035 rows=1 loops=11,637)

  • Filter: (gridoperatorid = actor_dgo.actorid)
  • Rows Removed by Filter: 57
63. 536.640 536.640 ↑ 1.0 3 11,180

Seq Scan on actor actor_reg (cost=0.00..2.90 rows=3 width=13) (actual time=0.013..0.048 rows=3 loops=11,180)

  • Filter: ((role)::text = 'RR'::text)
  • Rows Removed by Filter: 69
64. 0.000 100.620 ↑ 1.0 1 11,180

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_1 (cost=2.60..2.64 rows=1 width=15) (actual time=0.008..0.009 rows=1 loops=11,180)

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

SubPlan (for Index Scan)

66. 33.540 223.600 ↑ 1.0 1 11,180

Limit (cost=2.31..2.32 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=11,180)

67. 78.260 190.060 ↑ 1.0 1 11,180

Sort (cost=2.31..2.32 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=11,180)

  • Sort Key: deliveryperiod_3.todate DESC
  • Sort Method: quicksort Memory: 25kB
68. 111.800 111.800 ↑ 1.0 1 11,180

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_3 (cost=0.29..2.30 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=11,180)

  • Index Cond: (deliverypointid = dp.deliverypointid)
  • Filter: (deleted IS FALSE)
69. 368.940 536.640 ↑ 1.0 1 11,180

Nested Loop (cost=5.20..5.28 rows=1 width=19) (actual time=0.046..0.048 rows=1 loops=11,180)

70. 0.000 89.440 ↑ 1.0 1 11,180

Index Scan using deliveryperiodbrpjournal_pk on deliveryperiodbrpjournal (cost=4.91..4.95 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=11,180)

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

SubPlan (for Index Scan)

72. 33.540 313.040 ↑ 1.0 1 11,180

Limit (cost=4.62..4.63 rows=1 width=8) (actual time=0.026..0.028 rows=1 loops=11,180)

73. 55.900 279.500 ↑ 1.0 1 11,180

Sort (cost=4.62..4.63 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=11,180)

  • Sort Key: deliveryperiodbrpjournal_1.todate DESC
  • Sort Method: quicksort Memory: 25kB
74. 34.059 223.600 ↑ 1.0 1 11,180

Nested Loop (cost=0.57..4.61 rows=1 width=8) (actual time=0.017..0.020 rows=1 loops=11,180)

75. 100.620 100.620 ↑ 1.0 1 11,180

Index Scan using "IX_deliveryperiodbrpjournal_delivetyperiodid_deliveryperiodbrpj" on deliveryperiodbrpjournal deliveryperiodbrpjournal_1 (cost=0.29..2.30 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=11,180)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
76. 88.921 88.921 ↑ 1.0 1 12,703

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_4 (cost=0.29..2.30 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=12,703)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
  • Filter: (deleted IS FALSE)
77. 78.260 78.260 ↑ 1.0 1 11,180

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_2 (cost=0.29..0.32 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=11,180)

  • Index Cond: (deliveryperiodid = deliveryperiodbrpjournal.deliveryperiodid)
  • Filter: (deleted IS FALSE)
78. 570.180 570.180 ↑ 1.0 2 11,180

Seq Scan on actor actor_sup (cost=0.00..2.90 rows=2 width=11) (actual time=0.009..0.051 rows=2 loops=11,180)

  • Filter: ((role)::text = 'S'::text)
  • Rows Removed by Filter: 70
79. 581.360 581.360 ↑ 1.0 6 11,180

Seq Scan on actor actor_brp (cost=0.00..2.90 rows=6 width=15) (actual time=0.010..0.052 rows=6 loops=11,180)

  • Filter: ((role)::text = 'B'::text)
  • Rows Removed by Filter: 66
Planning time : 52.351 ms
Execution time : 165,063.769 ms