explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MSI9

Settings
# exclusive inclusive rows x rows loops node
1. 7.169 11,625.124 ↑ 1.0 1 1

Aggregate (cost=86,948.77..86,948.78 rows=1 width=8) (actual time=11,625.124..11,625.124 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((propertycode)::text = 'defaultCountryCode'::text)
4. 18.921 11,617.955 ↑ 2.2 11,180 1

Hash Left Join (cost=43,229.97..86,885.49 rows=24,397 width=0) (actual time=1,243.867..11,617.955 rows=11,180 loops=1)

  • Hash Cond: (deliverypoint.deliverypointid = dp.deliverypointid)
5. 48.708 10,451.244 ↑ 2.2 11,180 1

Hash Join (cost=2,279.24..45,599.30 rows=24,397 width=4) (actual time=96.041..10,451.244 rows=11,180 loops=1)

  • Hash Cond: ((SubPlan 2) = generalconfig.generalconfigid)
6. 18.310 870.119 ↑ 2.0 12,559 1

Hash Left Join (cost=1,479.84..41,193.14 rows=24,681 width=8) (actual time=58.173..870.119 rows=12,559 loops=1)

  • Hash Cond: (deliveryperiod.deliveryperiodid = cdp.deliveryperiodid)
7. 20.435 838.718 ↑ 1.9 10,880 1

Hash Left Join (cost=1,096.76..40,303.92 rows=20,746 width=8) (actual time=45.046..838.718 rows=10,880 loops=1)

  • Hash Cond: ((deliverypointcomponent.deliverypointcomponentid = deliveryperiod.deliverypointcomponentid) AND (consumption.deliveryperiodid = deliveryperiod.deliveryperiodid))
8. 15.707 804.134 ↑ 1.9 10,880 1

Hash Left Join (cost=728.46..39,780.01 rows=20,746 width=12) (actual time=30.857..804.134 rows=10,880 loops=1)

  • Hash Cond: (consumption.deliverypointid = deliverypoint.deliverypointid)
9. 7.759 776.234 ↑ 1.9 10,880 1

Hash Left Join (cost=382.34..39,148.64 rows=20,746 width=12) (actual time=18.629..776.234 rows=10,880 loops=1)

  • Hash Cond: (processmessages.processid = process.processid)
10. 17.547 767.115 ↑ 1.9 10,880 1

Hash Left Join (cost=353.26..38,834.29 rows=20,746 width=16) (actual time=17.257..767.115 rows=10,880 loops=1)

  • Hash Cond: ((deliverypointcomponent.componentnature)::text = (componentnature.componentnature)::text)
11. 17.712 749.437 ↑ 1.9 10,880 1

Hash Left Join (cost=349.51..38,545.29 rows=20,746 width=24) (actual time=17.105..749.437 rows=10,880 loops=1)

  • Hash Cond: (consumption.deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
12. 20.831 717.840 ↑ 1.9 10,880 1

Hash Join (cost=44.40..37,954.92 rows=20,746 width=16) (actual time=2.936..717.840 rows=10,880 loops=1)

  • Hash Cond: ((consumptiondetail.measurementnature)::text = (measurementnature.nature)::text)
13. 13.571 696.950 ↑ 1.9 10,880 1

Nested Loop (cost=42.75..37,668.01 rows=20,746 width=33) (actual time=2.848..696.950 rows=10,880 loops=1)

14. 7.527 235.239 ↑ 1.4 6,790 1

Hash Left Join (cost=42.33..5,279.17 rows=9,358 width=20) (actual time=1.607..235.239 rows=6,790 loops=1)

  • Hash Cond: (consumption.messageid = processmessages.messageid)
15. 226.156 226.156 ↑ 1.4 6,789 1

Seq Scan on consumption (cost=0.00..5,188.76 rows=9,358 width=35) (actual time=0.036..226.156 rows=6,789 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: 106,949
16. 0.804 1.556 ↓ 1.0 1,578 1

Hash (cost=22.70..22.70 rows=1,570 width=8) (actual time=1.556..1.556 rows=1,578 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 78kB
17. 0.752 0.752 ↓ 1.0 1,578 1

Seq Scan on processmessages (cost=0.00..22.70 rows=1,570 width=8) (actual time=0.035..0.752 rows=1,578 loops=1)

18. 448.140 448.140 ↑ 1.5 2 6,790

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

  • 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
19. 0.027 0.059 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=32) (actual time=0.059..0.059 rows=29 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.032 0.032 ↑ 1.0 29 1

Seq Scan on measurementnature (cost=0.00..1.29 rows=29 width=32) (actual time=0.019..0.032 rows=29 loops=1)

21. 5.176 13.885 ↓ 1.0 8,497 1

Hash (cost=198.94..198.94 rows=8,494 width=12) (actual time=13.885..13.885 rows=8,497 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 494kB
22. 8.709 8.709 ↓ 1.0 8,497 1

Seq Scan on deliverypointcomponent (cost=0.00..198.94 rows=8,494 width=12) (actual time=0.022..8.709 rows=8,497 loops=1)

  • Filter: (deleted IS FALSE)
23. 0.013 0.131 ↑ 1.0 11 1

Hash (cost=3.60..3.60 rows=11 width=32) (actual time=0.131..0.131 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.044 0.118 ↑ 1.0 11 1

Hash Left Join (cost=2.44..3.60 rows=11 width=32) (actual time=0.105..0.118 rows=11 loops=1)

  • Hash Cond: ((componentnature.componentnature)::text = (codetranslation.code)::text)
25. 0.041 0.041 ↑ 1.0 11 1

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

26. 0.000 0.033 ↓ 0.0 0 1

Hash (cost=2.43..2.43 rows=1 width=12) (actual time=0.033..0.033 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
27. 0.033 0.033 ↓ 0.0 0 1

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

  • Index Cond: ((category = 'componentnature'::text) AND (countrycode IS NULL))
  • Heap Fetches: 0
28. 0.152 1.360 ↓ 1.0 243 1

Hash (cost=26.10..26.10 rows=239 width=4) (actual time=1.360..1.360 rows=243 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
29. 0.300 1.208 ↓ 1.0 243 1

Hash Right Join (cost=9.65..26.10 rows=239 width=4) (actual time=0.504..1.208 rows=243 loops=1)

  • Hash Cond: (transactioncrossref.processid = process.processid)
30. 0.536 0.536 ↓ 1.0 230 1

Index Scan using transactioncrossref_counterpart_idx on transactioncrossref (cost=0.27..13.61 rows=226 width=4) (actual time=0.119..0.536 rows=230 loops=1)

  • Index Cond: ((counterpart)::text = 'BackOffice'::text)
  • Filter: ((type)::text = 'STANDARD'::text)
31. 0.142 0.372 ↓ 1.0 243 1

Hash (cost=6.39..6.39 rows=239 width=4) (actual time=0.372..0.372 rows=243 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
32. 0.230 0.230 ↓ 1.0 243 1

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

33. 4.696 12.193 ↓ 1.0 8,497 1

Hash (cost=239.94..239.94 rows=8,494 width=4) (actual time=12.193..12.193 rows=8,497 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 427kB
34. 7.497 7.497 ↓ 1.0 8,497 1

Seq Scan on deliverypoint (cost=0.00..239.94 rows=8,494 width=4) (actual time=0.019..7.497 rows=8,497 loops=1)

  • Filter: (deleted IS FALSE)
35. 6.073 14.149 ↑ 1.0 8,873 1

Hash (cost=234.32..234.32 rows=8,932 width=8) (actual time=14.149..14.149 rows=8,873 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 475kB
36. 8.076 8.076 ↑ 1.0 8,873 1

Seq Scan on deliveryperiod (cost=0.00..234.32 rows=8,932 width=8) (actual time=0.018..8.076 rows=8,873 loops=1)

  • Filter: (deleted IS FALSE)
37. 6.278 13.091 ↓ 1.0 10,629 1

Hash (cost=250.26..250.26 rows=10,626 width=12) (actual time=13.091..13.091 rows=10,629 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 585kB
38. 6.813 6.813 ↓ 1.0 10,629 1

Seq Scan on contractdelivery cdp (cost=0.00..250.26 rows=10,626 width=12) (actual time=0.021..6.813 rows=10,629 loops=1)

39. 9.673 36.817 ↓ 1.0 16,841 1

Hash (cost=588.92..588.92 rows=16,838 width=4) (actual time=36.817..36.817 rows=16,841 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 849kB
40. 27.144 27.144 ↓ 1.0 16,841 1

Seq Scan on generalconfig (cost=0.00..588.92 rows=16,838 width=4) (actual time=0.028..27.144 rows=16,841 loops=1)

  • Filter: ((deleted IS FALSE) AND ((direction)::text = 'CONSUMPTION'::text))
  • Rows Removed by Filter: 196
41.          

SubPlan (for Hash Join)

42. 9,495.600 9,495.600 ↑ 1.0 1 23,739

Result (cost=0.00..0.27 rows=1 width=4) (actual time=0.399..0.400 rows=1 loops=23,739)

43. 7.659 1,147.790 ↓ 1.0 8,498 1

Hash (cost=40,844.55..40,844.55 rows=8,494 width=8) (actual time=1,147.790..1,147.790 rows=8,498 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 458kB
44. 19.681 1,140.131 ↓ 1.0 8,498 1

Hash Left Join (cost=1,146.58..40,844.55 rows=8,494 width=8) (actual time=49.168..1,140.131 rows=8,498 loops=1)

  • Hash Cond: (((deliveryperiodbrpjournal.brp)::text = (actor_brp.externalid)::text) AND ((dp.market)::text = (actor_brp.marketcode)::text))
45. 18.918 1,120.370 ↓ 1.0 8,497 1

Hash Left Join (cost=1,143.59..40,709.98 rows=8,494 width=22) (actual time=49.074..1,120.370 rows=8,497 loops=1)

  • Hash Cond: (((deliveryperiod_1.supplier)::text = (actor_sup.externalid)::text) AND ((dp.market)::text = (actor_sup.marketcode)::text))
46. 25.277 1,101.354 ↓ 1.0 8,497 1

Hash Left Join (cost=1,140.66..40,620.73 rows=8,494 width=33) (actual time=48.949..1,101.354 rows=8,497 loops=1)

  • Hash Cond: ((SubPlan 7) = deliveryperiodbrpjournal.deliveryperiodbrpjournalid)
47. 35.762 662.620 ↓ 1.0 8,497 1

Hash Left Join (cost=352.71..20,052.38 rows=8,494 width=22) (actual time=13.957..662.620 rows=8,497 loops=1)

  • Hash Cond: ((SubPlan 6) = deliveryperiod_1.deliveryperiodid)
48. 55.997 375.827 ↓ 1.0 8,497 1

Hash Left Join (cost=6.74..9,747.19 rows=8,494 width=7) (actual time=0.414..375.827 rows=8,497 loops=1)

  • Hash Cond: (((dp.market)::text = (actor_dgo.marketcode)::text) AND ((dp.gridoperator)::text = (actor_dgo.externalid)::text))
  • Join Filter: ((dp.regulator)::text = ((SubPlan 5))::text)
  • Rows Removed by Join Filter: 322
49. 12.379 19.894 ↓ 1.0 8,497 1

Hash Left Join (cost=2.94..299.51 rows=8,494 width=21) (actual time=0.101..19.894 rows=8,497 loops=1)

  • Hash Cond: ((dp.regulator)::text = (actor_reg.externalid)::text)
50. 7.445 7.445 ↓ 1.0 8,497 1

Seq Scan on deliverypoint dp (cost=0.00..239.94 rows=8,494 width=21) (actual time=0.020..7.445 rows=8,497 loops=1)

  • Filter: (deleted IS FALSE)
51. 0.005 0.070 ↑ 1.0 3 1

Hash (cost=2.90..2.90 rows=3 width=13) (actual time=0.070..0.070 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.065 0.065 ↑ 1.0 3 1

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

  • Filter: ((role)::text = 'RR'::text)
  • Rows Removed by Filter: 69
53. 0.062 0.158 ↑ 1.0 60 1

Hash (cost=2.90..2.90 rows=60 width=15) (actual time=0.158..0.158 rows=60 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
54. 0.096 0.096 ↑ 1.0 60 1

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

  • Filter: ((role)::text = ANY ('{D,T}'::text[]))
  • Rows Removed by Filter: 12
55.          

SubPlan (for Hash Left Join)

56. 299.778 299.778 ↑ 1.0 1 8,817

Seq Scan on gridoperator dgo (cost=0.00..1.73 rows=1 width=32) (actual time=0.024..0.034 rows=1 loops=8,817)

  • Filter: (gridoperatorid = actor_dgo.actorid)
  • Rows Removed by Filter: 57
57. 5.364 13.437 ↑ 1.0 8,873 1

Hash (cost=234.32..234.32 rows=8,932 width=15) (actual time=13.437..13.437 rows=8,873 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 543kB
58. 8.073 8.073 ↑ 1.0 8,873 1

Seq Scan on deliveryperiod deliveryperiod_1 (cost=0.00..234.32 rows=8,932 width=15) (actual time=0.017..8.073 rows=8,873 loops=1)

  • Filter: (deleted IS FALSE)
59.          

SubPlan (for Hash Left Join)

60. 33.942 237.594 ↑ 1.0 1 16,971

Limit (cost=2.31..2.32 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=16,971)

61. 67.884 203.652 ↑ 1.0 1 16,971

Sort (cost=2.31..2.32 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=16,971)

  • Sort Key: deliveryperiod_3.todate DESC
  • Sort Method: quicksort Memory: 25kB
62. 135.768 135.768 ↑ 1.0 1 16,971

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_3 (cost=0.29..2.30 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=16,971)

  • Index Cond: (deliverypointid = dp.deliverypointid)
  • Filter: (deleted IS FALSE)
63. 6.614 34.923 ↓ 1.0 8,776 1

Hash (cost=678.31..678.31 rows=8,772 width=19) (actual time=34.923..34.923 rows=8,776 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 572kB
64. 11.991 28.309 ↓ 1.0 8,776 1

Hash Join (cost=345.97..678.31 rows=8,772 width=19) (actual time=12.637..28.309 rows=8,776 loops=1)

  • Hash Cond: (deliveryperiodbrpjournal.deliveryperiodid = deliveryperiod_2.deliveryperiodid)
65. 3.749 3.749 ↓ 1.0 8,776 1

Seq Scan on deliveryperiodbrpjournal (cost=0.00..211.72 rows=8,772 width=23) (actual time=0.031..3.749 rows=8,776 loops=1)

66. 4.892 12.569 ↑ 1.0 8,873 1

Hash (cost=234.32..234.32 rows=8,932 width=4) (actual time=12.569..12.569 rows=8,873 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 440kB
67. 7.677 7.677 ↑ 1.0 8,873 1

Seq Scan on deliveryperiod deliveryperiod_2 (cost=0.00..234.32 rows=8,932 width=4) (actual time=0.015..7.677 rows=8,873 loops=1)

  • Filter: (deleted IS FALSE)
68.          

SubPlan (for Hash Left Join)

69. 49.374 378.534 ↑ 1.0 1 16,458

Limit (cost=4.62..4.63 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=16,458)

70. 65.832 329.160 ↑ 1.0 1 16,458

Sort (cost=4.62..4.63 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=16,458)

  • Sort Key: deliveryperiodbrpjournal_1.todate DESC
  • Sort Method: quicksort Memory: 25kB
71. 30.774 263.328 ↑ 1.0 1 16,458

Nested Loop (cost=0.57..4.61 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=16,458)

72. 115.206 115.206 ↑ 1.0 1 16,458

Index Scan using "IX_deliveryperiodbrpjournal_delivetyperiodid_deliveryperiodbrpj" on deliveryperiodbrpjournal deliveryperiodbrpjournal_1 (cost=0.29..2.30 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=16,458)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
73. 117.348 117.348 ↑ 1.0 1 16,764

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=16,764)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
  • Filter: (deleted IS FALSE)
74. 0.023 0.098 ↑ 1.0 2 1

Hash (cost=2.90..2.90 rows=2 width=11) (actual time=0.098..0.098 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
75. 0.075 0.075 ↑ 1.0 2 1

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

  • Filter: ((role)::text = 'S'::text)
  • Rows Removed by Filter: 70
76. 0.018 0.080 ↑ 1.0 6 1

Hash (cost=2.90..2.90 rows=6 width=15) (actual time=0.080..0.080 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
77. 0.062 0.062 ↑ 1.0 6 1

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

  • Filter: ((role)::text = 'B'::text)
  • Rows Removed by Filter: 66
Planning time : 52.905 ms
Execution time : 11,626.079 ms