explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jOlR

Settings
# exclusive inclusive rows x rows loops node
1. 19.584 4,488.161 ↓ 3.7 11,099 1

Unique (cost=133,549.59..133,842.28 rows=3,002 width=634) (actual time=4,458.237..4,488.161 rows=11,099 loops=1)

2.          

CTE conso

3. 1,459.609 1,980.787 ↑ 1.7 10,034 1

Nested Loop (cost=0.42..42,686.45 rows=17,518 width=947) (actual time=0.349..1,980.787 rows=10,034 loops=1)

4. 202.938 202.938 ↑ 1.3 6,120 1

Seq Scan on consumption (cost=0.00..5,188.76 rows=7,902 width=95) (actual time=0.044..202.938 rows=6,120 loops=1)

  • Filter: ((deleted IS FALSE) AND (deliveryperiodid IS NOT NULL) 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: 107618
5. 318.240 318.240 ↑ 1.5 2 6,120

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail (cost=0.42..3.58 rows=3 width=863) (actual time=0.032..0.052 rows=2 loops=6,120)

  • 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
6.          

Initplan (for Unique)

7. 0.145 0.145 ↑ 1.0 1 1

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

  • Index Cond: ((propertycode)::text = 'defaultCountryCode'::text)
8. 233.348 4,468.432 ↓ 3.7 11,180 1

Sort (cost=90,860.85..90,868.36 rows=3,002 width=634) (actual time=4,458.234..4,468.432 rows=11,180 loops=1)

  • Sort Key: cu.customernbr, 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, ((alternatives: SubPlan 2 or hashed SubPlan 3)), ((alternatives: SubPlan 4 or hashed SubPlan 5)), message.externaltimestamp, c.sourceid, c.historical, c.ignored, c.measurementnature, c.status, c.fromdate, c.todate, c.value, measurementnature.unit, c.creator, c.creationdate, c.latestmodifier, c.latestmodifdate, c.timeframe, c.direction, c.valuelist, c.qualitylist, c.consumptionid, ((SubPlan 6))
  • Sort Method: external merge Disk: 3808kB
9. 33.467 4,235.084 ↓ 3.7 11,180 1

Hash Left Join (cost=14,394.04..90,687.46 rows=3,002 width=634) (actual time=2,747.015..4,235.084 rows=11,180 loops=1)

  • Hash Cond: (actor_dgo.actorid = dgo_actor.actorid)
10. 21.590 3,776.250 ↓ 3.7 11,180 1

Hash Right Join (cost=14,390.42..54,150.26 rows=3,002 width=548) (actual time=2,746.092..3,776.250 rows=11,180 loops=1)

  • Hash Cond: (dp.deliverypointid = deliverypoint.deliverypointid)
11. 17.155 1,051.781 ↓ 1.0 8,498 1

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

  • Hash Cond: (((deliveryperiodbrpjournal.brp)::text = (actor_brp.externalid)::text) AND ((dp.market)::text = (actor_brp.marketcode)::text))
12. 17.578 1,034.565 ↓ 1.0 8,497 1

Hash Left Join (cost=1,143.59..40,709.98 rows=8,494 width=26) (actual time=42.737..1,034.565 rows=8,497 loops=1)

  • Hash Cond: (((deliveryperiod_1.supplier)::text = (actor_sup.externalid)::text) AND ((dp.market)::text = (actor_sup.marketcode)::text))
13. 24.907 1,016.921 ↓ 1.0 8,497 1

Hash Left Join (cost=1,140.66..40,620.73 rows=8,494 width=37) (actual time=42.644..1,016.921 rows=8,497 loops=1)

  • Hash Cond: ((SubPlan 12) = deliveryperiodbrpjournal.deliveryperiodbrpjournalid)
14. 32.682 615.699 ↓ 1.0 8,497 1

Hash Left Join (cost=352.71..20,052.38 rows=8,494 width=26) (actual time=11.677..615.699 rows=8,497 loops=1)

  • Hash Cond: ((SubPlan 11) = deliveryperiod_1.deliveryperiodid)
15. 56.978 351.386 ↓ 1.0 8,497 1

Hash Left Join (cost=6.74..9,747.19 rows=8,494 width=11) (actual time=0.374..351.386 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 10))::text)
  • Rows Removed by Join Filter: 322
16. 11.672 20.951 ↓ 1.0 8,497 1

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

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

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

  • Filter: (deleted IS FALSE)
18. 0.004 0.108 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.104 0.104 ↑ 1.0 3 1

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

  • Filter: ((role)::text = 'RR'::text)
  • Rows Removed by Filter: 69
20. 0.050 0.130 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.080 0.080 ↑ 1.0 60 1

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

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

SubPlan (for Hash Left Join)

23. 273.327 273.327 ↑ 1.0 1 8,817

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

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 543kB
25. 6.614 6.614 ↑ 1.0 8,873 1

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

  • Filter: (deleted IS FALSE)
26.          

SubPlan (for Hash Left Join)

27. 33.942 220.623 ↑ 1.0 1 16,971

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

28. 67.884 186.681 ↑ 1.0 1 16,971

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

  • Sort Key: deliveryperiod_3.todate DESC
  • Sort Method: quicksort Memory: 25kB
29. 118.797 118.797 ↑ 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.006..0.007 rows=1 loops=16,971)

  • Index Cond: (deliverypointid = dp.deliverypointid)
  • Filter: (deleted IS FALSE)
30. 4.668 30.697 ↓ 1.0 8,776 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 572kB
31. 10.165 26.029 ↓ 1.0 8,776 1

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

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

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

33. 4.111 10.596 ↑ 1.0 8,873 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 440kB
34. 6.485 6.485 ↑ 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..6.485 rows=8,873 loops=1)

  • Filter: (deleted IS FALSE)
35.          

SubPlan (for Hash Left Join)

36. 32.916 345.618 ↑ 1.0 1 16,458

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

37. 65.832 312.702 ↑ 1.0 1 16,458

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

  • Sort Key: deliveryperiodbrpjournal_1.todate DESC
  • Sort Method: quicksort Memory: 25kB
38. 47.538 246.870 ↑ 1.0 1 16,458

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

39. 98.748 98.748 ↑ 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.006 rows=1 loops=16,458)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
40. 100.584 100.584 ↑ 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.006 rows=1 loops=16,764)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
  • Filter: (deleted IS FALSE)
41. 0.003 0.066 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.063 0.063 ↑ 1.0 2 1

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

  • Filter: ((role)::text = 'S'::text)
  • Rows Removed by Filter: 70
43. 0.007 0.061 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.054 0.054 ↑ 1.0 6 1

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

  • Filter: ((role)::text = 'B'::text)
  • Rows Removed by Filter: 66
45. 14.759 2,702.879 ↓ 3.7 11,180 1

Hash (cost=13,206.31..13,206.31 rows=3,002 width=548) (actual time=2,702.879..2,702.879 rows=11,180 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3912kB
46. 134.608 2,688.120 ↓ 3.7 11,180 1

Nested Loop Left Join (cost=6,514.07..13,206.31 rows=3,002 width=548) (actual time=2,405.573..2,688.120 rows=11,180 loops=1)

47. 13.013 2,486.432 ↓ 3.7 11,180 1

Hash Left Join (cost=6,512.07..7,040.33 rows=3,002 width=548) (actual time=2,404.930..2,486.432 rows=11,180 loops=1)

  • Hash Cond: (cu.customerid = crm.customerid)
48. 13.144 2,464.893 ↓ 3.7 11,180 1

Hash Left Join (cost=6,266.91..6,753.89 rows=3,002 width=548) (actual time=2,396.377..2,464.893 rows=11,180 loops=1)

  • Hash Cond: (co.customerid = cu.customerid)
49. 13.474 2,440.214 ↓ 3.7 11,180 1

Hash Join (cost=5,961.74..6,407.44 rows=3,002 width=535) (actual time=2,384.799..2,440.214 rows=11,180 loops=1)

  • Hash Cond: (c.generalconfigid = generalconfig.generalconfigid)
50. 15.356 2,388.513 ↓ 3.9 11,714 1

Hash Left Join (cost=5,162.34..5,566.63 rows=3,037 width=500) (actual time=2,346.520..2,388.513 rows=11,714 loops=1)

  • Hash Cond: ((deliverypointcomponent.componentnature)::text = (componentnature.componentnature)::text)
51. 19.432 2,372.492 ↓ 3.9 11,714 1

Hash Right Join (cost=5,152.16..5,514.69 rows=3,037 width=492) (actual time=2,345.836..2,372.492 rows=11,714 loops=1)

  • Hash Cond: (co.contractid = cdp.contractid)
52. 7.520 7.520 ↓ 1.0 10,414 1

Seq Scan on contract co (cost=0.00..280.11 rows=10,411 width=28) (actual time=0.268..7.520 rows=10,414 loops=1)

53. 12.053 2,345.540 ↓ 3.9 11,714 1

Hash (cost=5,114.20..5,114.20 rows=3,037 width=468) (actual time=2,345.540..2,345.540 rows=11,714 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3353kB
54. 16.168 2,333.487 ↓ 3.9 11,714 1

Hash Right Join (cost=4,793.72..5,114.20 rows=3,037 width=468) (actual time=2,312.198..2,333.487 rows=11,714 loops=1)

  • Hash Cond: (cdp.deliveryperiodid = deliveryperiod.deliveryperiodid)
55. 5.173 5.173 ↓ 1.0 10,629 1

Seq Scan on contractdelivery cdp (cost=0.00..250.26 rows=10,626 width=8) (actual time=0.027..5.173 rows=10,629 loops=1)

56. 10.470 2,312.146 ↓ 3.9 10,035 1

Hash (cost=4,761.81..4,761.81 rows=2,553 width=468) (actual time=2,312.146..2,312.146 rows=10,035 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2889kB
57. 17.988 2,301.676 ↓ 3.9 10,035 1

Hash Right Join (cost=4,460.49..4,761.81 rows=2,553 width=468) (actual time=2,277.177..2,301.676 rows=10,035 loops=1)

  • Hash Cond: ((deliveryperiod.deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid) AND (deliveryperiod.deliveryperiodid = c.deliveryperiodid))
58. 6.553 6.553 ↑ 1.0 8,873 1

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

  • Filter: (deleted IS FALSE)
59. 17.385 2,277.135 ↓ 3.9 10,035 1

Hash (cost=4,422.19..4,422.19 rows=2,553 width=472) (actual time=2,277.135..2,277.135 rows=10,035 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2938kB
60. 16.298 2,259.750 ↓ 3.9 10,035 1

Hash Left Join (cost=725.00..4,422.19 rows=2,553 width=472) (actual time=31.882..2,259.750 rows=10,035 loops=1)

  • Hash Cond: (c.deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
61. 6.536 2,230.155 ↓ 3.9 10,035 1

Hash Left Join (cost=419.88..4,081.97 rows=2,553 width=464) (actual time=18.549..2,230.155 rows=10,035 loops=1)

  • Hash Cond: (processmessages.processid = process.processid)
62. 8.933 2,222.227 ↓ 3.9 10,035 1

Hash Left Join (cost=390.80..4,017.78 rows=2,553 width=468) (actual time=17.148..2,222.227 rows=10,035 loops=1)

  • Hash Cond: (c.messageid = processmessages.messageid)
63. 15.314 2,211.711 ↓ 4.0 10,034 1

Hash Left Join (cost=348.48..3,937.23 rows=2,540 width=468) (actual time=15.553..2,211.711 rows=10,034 loops=1)

  • Hash Cond: (c.deliverypointid = deliverypoint.deliverypointid)
64. 21.429 2,181.321 ↓ 4.0 10,034 1

Nested Loop Left Join (cost=2.36..3,556.19 rows=2,540 width=446) (actual time=0.442..2,181.321 rows=10,034 loops=1)

65. 14.739 2,049.518 ↓ 4.0 10,034 1

Nested Loop Left Join (cost=1.94..1,325.85 rows=2,540 width=438) (actual time=0.426..2,049.518 rows=10,034 loops=1)

66. 21.788 2,024.745 ↓ 4.0 10,034 1

Hash Join (cost=1.65..443.10 rows=2,540 width=410) (actual time=0.421..2,024.745 rows=10,034 loops=1)

  • Hash Cond: ((c.measurementnature)::text = (measurementnature.nature)::text)
67. 2,002.905 2,002.905 ↑ 1.7 10,034 1

CTE Scan on conso c (cost=0.00..350.36 rows=17,518 width=378) (actual time=0.356..2,002.905 rows=10,034 loops=1)

68. 0.023 0.052 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=64) (actual time=0.052..0.052 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
69. 0.029 0.029 ↑ 1.0 29 1

Seq Scan on measurementnature (cost=0.00..1.29 rows=29 width=64) (actual time=0.014..0.029 rows=29 loops=1)

70. 10.034 10.034 ↓ 0.0 0 10,034

Index Scan using logicalregister_pk on logicalregister (cost=0.29..0.34 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=10,034)

  • Index Cond: (c.logicalregisterid = logicalregisterid)
  • Filter: (ignored IS FALSE)
71. 110.374 110.374 ↑ 1.0 1 10,034

Index Scan using message_pk on message (cost=0.42..0.87 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=10,034)

  • Index Cond: (c.messageid = messageid)
72. 5.263 15.076 ↓ 1.0 8,497 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 626kB
73. 9.813 9.813 ↓ 1.0 8,497 1

Seq Scan on deliverypoint (cost=0.00..239.94 rows=8,494 width=26) (actual time=0.017..9.813 rows=8,497 loops=1)

  • Filter: (deleted IS FALSE)
74. 0.814 1.583 ↓ 1.0 1,578 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 78kB
75. 0.769 0.769 ↓ 1.0 1,578 1

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

76. 0.141 1.392 ↓ 1.0 243 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
77. 0.293 1.251 ↓ 1.0 243 1

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

  • Hash Cond: (transactioncrossref.processid = process.processid)
78. 0.587 0.587 ↓ 1.0 230 1

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

  • Index Cond: ((counterpart)::text = 'BackOffice'::text)
  • Filter: ((type)::text = 'STANDARD'::text)
79. 0.238 0.371 ↓ 1.0 243 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
80. 0.133 0.133 ↓ 1.0 243 1

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

81. 4.815 13.297 ↓ 1.0 8,497 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 494kB
82. 8.482 8.482 ↓ 1.0 8,497 1

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

  • Filter: (deleted IS FALSE)
83. 0.009 0.665 ↑ 1.0 11 1

Hash (cost=10.04..10.04 rows=11 width=48) (actual time=0.665..0.665 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.019 0.656 ↑ 1.0 11 1

Hash Left Join (cost=3.97..10.04 rows=11 width=48) (actual time=0.622..0.656 rows=11 loops=1)

  • Hash Cond: ((componentnature.componentnature)::text = (codetranslation.code)::text)
85. 0.032 0.620 ↑ 1.0 11 1

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

  • Hash Cond: ((codetranslation_1.code)::text = (componentnature.componentnature)::text)
86. 0.537 0.537 ↓ 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.530..0.537 rows=11 loops=1)

  • Index Cond: (((category)::text = 'componentnature'::text) AND ((countrycode)::text = ($10)::text))
87. 0.006 0.051 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
88. 0.045 0.045 ↑ 1.0 11 1

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

89. 0.000 0.017 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
90. 0.017 0.017 ↓ 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.017..0.017 rows=0 loops=1)

  • Index Cond: (((category)::text = 'componentnature'::text) AND (countrycode IS NULL))
91. 10.911 38.227 ↓ 1.0 16,841 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1250kB
92. 27.316 27.316 ↓ 1.0 16,841 1

Seq Scan on generalconfig (cost=0.00..588.92 rows=16,838 width=43) (actual time=0.042..27.316 rows=16,841 loops=1)

  • Filter: ((deleted IS FALSE) AND ((direction)::text = 'CONSUMPTION'::text))
  • Rows Removed by Filter: 196
93. 3.778 11.535 ↑ 1.0 7,163 1

Hash (cost=215.63..215.63 rows=7,163 width=17) (actual time=11.535..11.535 rows=7,163 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 400kB
94. 7.757 7.757 ↑ 1.0 7,163 1

Seq Scan on customer cu (cost=0.00..215.63 rows=7,163 width=17) (actual time=0.273..7.757 rows=7,163 loops=1)

95. 3.362 8.526 ↑ 1.0 7,163 1

Hash (cost=155.63..155.63 rows=7,163 width=8) (actual time=8.525..8.526 rows=7,163 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 344kB
96. 5.164 5.164 ↑ 1.0 7,163 1

Seq Scan on crmdata crm (cost=0.00..155.63 rows=7,163 width=8) (actual time=0.042..5.164 rows=7,163 loops=1)

97. 0.000 67.080 ↑ 1.0 1 11,180

Index Scan using "Key4" on contracthistory ch (cost=2.00..2.04 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=11,180)

  • Index Cond: ((contractid = co.contractid) AND (contracthistentry = (SubPlan 9)))
98.          

SubPlan (for Index Scan)

99. 0.000 100.620 ↑ 1.0 1 11,180

Limit (cost=1.70..1.71 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=11,180)

100.          

Initplan (for Limit)

101. 11.180 78.260 ↑ 1.0 1 11,180

Limit (cost=0.29..1.70 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=11,180)

102. 67.080 67.080 ↑ 2.0 1 11,180

Index Only Scan Backward using "Key4" on contracthistory ch2 (cost=0.29..3.11 rows=2 width=4) (actual time=0.006..0.006 rows=1 loops=11,180)

  • Index Cond: ((contractid = co.contractid) AND (contracthistentry IS NOT NULL))
  • Heap Fetches: 11180
103. 89.440 89.440 ↑ 1.0 1 11,180

Result (cost=1.70..1.71 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=11,180)

104. 0.041 0.084 ↑ 1.0 72 1

Hash (cost=2.72..2.72 rows=72 width=20) (actual time=0.084..0.084 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
105. 0.043 0.043 ↑ 1.0 72 1

Seq Scan on actor dgo_actor (cost=0.00..2.72 rows=72 width=20) (actual time=0.014..0.043 rows=72 loops=1)

106.          

SubPlan (for Hash Left Join)

107. 0.000 0.000 ↓ 0.0 0

Index Only Scan using decentralizedproduction_physicalconfig_idx on decentralizedproduction (cost=0.15..2.17 rows=1 width=0) (never executed)

  • Index Cond: (physicalconfigid = c.physicalconfigid)
  • Heap Fetches: 0
108. 0.233 0.233 ↑ 1.0 295 1

Seq Scan on decentralizedproduction decentralizedproduction_1 (cost=0.00..7.95 rows=295 width=4) (actual time=0.070..0.233 rows=295 loops=1)

109. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..4.62 rows=1 width=0) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Index Scan using meter_physicalconfigid_idx on meter m (cost=0.29..2.31 rows=1 width=4) (never executed)

  • Index Cond: (physicalconfigid = c.physicalconfigid)
  • Filter: ((metertype)::text = 'SMART_METER'::text)
111. 0.000 0.000 ↓ 0.0 0

Index Scan using physicalconfig_pk on physicalconfig (cost=0.29..2.30 rows=1 width=4) (never executed)

  • Index Cond: (physicalconfigid = c.physicalconfigid)
  • Filter: (deleted IS FALSE)
112. 0.002 0.210 ↓ 0.0 0 1

Nested Loop (cost=0.57..4.62 rows=1 width=4) (actual time=0.210..0.210 rows=0 loops=1)

113. 0.208 0.208 ↓ 0.0 0 1

Index Scan using "IX_meter_metertype_meter" on meter m_1 (cost=0.29..2.31 rows=1 width=4) (actual time=0.208..0.208 rows=0 loops=1)

  • Index Cond: ((metertype)::text = 'SMART_METER'::text)
114. 0.000 0.000 ↓ 0.0 0

Index Scan using physicalconfig_pk on physicalconfig physicalconfig_1 (cost=0.29..2.30 rows=1 width=4) (never executed)

  • Index Cond: (physicalconfigid = m_1.physicalconfigid)
  • Filter: (deleted IS FALSE)
115. 33.540 424.840 ↑ 1.0 1 11,180

Limit (cost=5.37..5.37 rows=1 width=32) (actual time=0.036..0.038 rows=1 loops=11,180)

116. 44.720 391.300 ↑ 1.0 1 11,180

Sort (cost=5.37..5.37 rows=1 width=32) (actual time=0.035..0.035 rows=1 loops=11,180)

  • Sort Key: (COALESCE(id.invdocref, (i.invoiceid)::character varying)) DESC
  • Sort Method: quicksort Memory: 25kB
117. 25.960 346.580 ↑ 1.0 1 11,180

Nested Loop Left Join (cost=1.26..5.36 rows=1 width=32) (actual time=0.028..0.031 rows=1 loops=11,180)

118. 11.300 212.420 ↑ 1.0 1 11,180

Nested Loop (cost=0.84..4.89 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=11,180)

119. 111.800 111.800 ↑ 1.0 1 11,180

Index Scan using idx_invoicedconsumption_consumptionid on invoicedconsumption ic (cost=0.42..2.44 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=11,180)

  • Index Cond: (consumptionid = c.consumptionid)
  • Filter: ((status)::text !~~ 'deprecated%'::text)
  • Rows Removed by Filter: 0
120. 89.320 89.320 ↑ 1.0 1 11,165

Index Only Scan using invoice_pkey on invoice i (cost=0.42..2.44 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=11,165)

  • Index Cond: (invoiceid = ic.invoiceid)
  • Heap Fetches: 10820
121. 108.200 108.200 ↑ 1.0 1 10,820

Index Scan using invdoc_invoiceid on invdoc id (cost=0.42..0.45 rows=1 width=14) (actual time=0.009..0.010 rows=1 loops=10,820)

  • Index Cond: (invoiceid = i.invoiceid)
Planning time : 67.566 ms
Execution time : 4,493.243 ms