explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f14v

Settings
# exclusive inclusive rows x rows loops node
1. 44.845 34,940.508 ↓ 23.4 468 1

HashAggregate (cost=30,836.61..30,836.81 rows=20 width=1,503) (actual time=34,939.728..34,940.508 rows=468 loops=1)

  • Group Key: dp.gsrn, dpc.componentnature, dp.market, config.direction, address.street, address.number, address.box, address.postalcode, address.town, address.country, address.geographicalcode, dp.gridoperator, dp.transporter, dp.regulator, dp.sourceid, brps.brp, config.fromdate, config."Todate", config.readingfrequency, config.gridrate, config.profile, config.readingday, config.readingmonth, config.readingyear, config.netarea, config.pressurelevel, config.voltagelevel, config.copperloss, config.ironloss, config.powerlimiter, config.decprod, config.deliverycategory, config.state, config.deliverystatus, config.contractedcapacity, config.contractedcapacityunit, config.contractedcapacitytimeframe, config.contractedcapacitypertf, config.contractedcapacityunitpertf, config.physicalcapacity, config.premisescategory, config.energyusage, config.configsource, config.meternumber, config.metertype, config.smartmeterstatus, config.configcode, config.registernbr, config.registername, config.timeframe, config.nature, config.registerdirection, config.registerprofile, config.unit, config.type, config.registersource, config.constant, config.format, config.idtech
2.          

CTE fromdates

3. 0.325 19.404 ↑ 1.2 1,003 1

Sort (cost=397.28..400.35 rows=1,228 width=8) (actual time=19.349..19.404 rows=1,003 loops=1)

  • Sort Key: generalconfig.deliverypointid, generalconfig.fromdate
  • Sort Method: quicksort Memory: 72kB
4. 0.533 19.079 ↑ 1.2 1,003 1

HashAggregate (cost=321.99..334.27 rows=1,228 width=8) (actual time=18.953..19.079 rows=1,003 loops=1)

  • Group Key: generalconfig.fromdate, generalconfig.deliverypointid
5. 0.175 18.546 ↓ 1.7 2,090 1

Append (cost=121.49..315.85 rows=1,228 width=8) (actual time=16.489..18.546 rows=2,090 loops=1)

6. 0.253 16.550 ↓ 4.2 502 1

HashAggregate (cost=121.49..122.68 rows=119 width=8) (actual time=16.489..16.550 rows=502 loops=1)

  • Group Key: generalconfig.fromdate, generalconfig.deliverypointid
7. 16.297 16.297 ↑ 1.1 502 1

Index Scan using generalconfig_sourceid_id on generalconfig (cost=0.28..118.64 rows=570 width=8) (actual time=0.762..16.297 rows=502 loops=1)

  • Index Cond: ((sourceid)::text = 'DGO'::text)
  • Filter: ((deleted IS FALSE) AND ((fromdate <> todate) OR (todate IS NULL)) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 85
8. 0.078 0.283 ↓ 1.1 214 1

HashAggregate (cost=10.25..12.22 rows=197 width=8) (actual time=0.255..0.283 rows=214 loops=1)

  • Group Key: logicalconfig.fromdate, logicalconfig.deliverypointid
9. 0.205 0.205 ↓ 1.0 215 1

Seq Scan on logicalconfig (cost=0.00..9.18 rows=213 width=8) (actual time=0.010..0.205 rows=215 loops=1)

  • Filter: ((deleted IS FALSE) AND ((fromdate <> todate) OR (todate IS NULL)) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 28
10. 0.212 0.707 ↓ 4.2 500 1

HashAggregate (cost=121.49..122.68 rows=119 width=8) (actual time=0.657..0.707 rows=500 loops=1)

  • Group Key: generalconfig_1.todate, generalconfig_1.deliverypointid
11. 0.495 0.495 ↑ 1.1 502 1

Index Scan using generalconfig_sourceid_id on generalconfig generalconfig_1 (cost=0.28..118.64 rows=570 width=8) (actual time=0.029..0.495 rows=502 loops=1)

  • Index Cond: ((sourceid)::text = 'DGO'::text)
  • Filter: ((deleted IS FALSE) AND ((fromdate <> todate) OR (todate IS NULL)) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 85
12. 0.074 0.170 ↓ 1.1 213 1

HashAggregate (cost=10.25..12.22 rows=197 width=8) (actual time=0.142..0.170 rows=213 loops=1)

  • Group Key: logicalconfig_1.todate, logicalconfig_1.deliverypointid
13. 0.096 0.096 ↓ 1.0 215 1

Seq Scan on logicalconfig logicalconfig_1 (cost=0.00..9.18 rows=213 width=8) (actual time=0.005..0.096 rows=215 loops=1)

  • Filter: ((deleted IS FALSE) AND ((fromdate <> todate) OR (todate IS NULL)) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 28
14. 0.106 0.372 ↓ 1.1 331 1

HashAggregate (cost=13.91..16.89 rows=298 width=8) (actual time=0.340..0.372 rows=331 loops=1)

  • Group Key: physicalconfig.fromdate, physicalconfig.deliverypointid
15. 0.266 0.266 ↓ 1.0 331 1

Seq Scan on physicalconfig (cost=0.00..12.27 rows=329 width=8) (actual time=0.006..0.266 rows=331 loops=1)

  • Filter: ((deleted IS FALSE) AND ((fromdate <> todate) OR (todate IS NULL)) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 31
16. 0.109 0.289 ↓ 1.1 330 1

HashAggregate (cost=13.91..16.89 rows=298 width=8) (actual time=0.247..0.289 rows=330 loops=1)

  • Group Key: physicalconfig_1.todate, physicalconfig_1.deliverypointid
17. 0.180 0.180 ↓ 1.0 331 1

Seq Scan on physicalconfig physicalconfig_1 (cost=0.00..12.27 rows=329 width=8) (actual time=0.005..0.180 rows=331 loops=1)

  • Filter: ((deleted IS FALSE) AND ((fromdate <> todate) OR (todate IS NULL)) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 31
18.          

CTE dates

19. 0.122 21.852 ↓ 40.8 531 1

Subquery Scan on x (cost=456.75..545.58 rows=13 width=12) (actual time=21.074..21.852 rows=531 loops=1)

  • Filter: ((x.fromdate IS NOT NULL) AND (x.nbr = 1))
  • Rows Removed by Filter: 545
20. 0.613 21.730 ↑ 2.4 1,076 1

WindowAgg (cost=456.75..513.85 rows=2,538 width=12) (actual time=21.072..21.730 rows=1,076 loops=1)

21. 0.539 21.117 ↑ 2.4 1,076 1

Sort (cost=456.75..463.09 rows=2,538 width=12) (actual time=21.068..21.117 rows=1,076 loops=1)

  • Sort Key: f.fromdate, f.deliverypointid, t.fromdate
  • Sort Method: quicksort Memory: 99kB
22. 0.582 20.578 ↑ 2.4 1,076 1

Merge Join (cost=175.14..313.23 rows=2,538 width=12) (actual time=19.846..20.578 rows=1,076 loops=1)

  • Merge Cond: (f.deliverypointid = t.deliverypointid)
  • Join Filter: ((t.fromdate > f.fromdate) OR (t.fromdate IS NULL))
  • Rows Removed by Join Filter: 1139
23. 0.141 19.699 ↑ 1.2 1,003 1

Sort (cost=87.57..90.64 rows=1,228 width=8) (actual time=19.652..19.699 rows=1,003 loops=1)

  • Sort Key: f.deliverypointid
  • Sort Method: quicksort Memory: 72kB
24. 19.558 19.558 ↑ 1.2 1,003 1

CTE Scan on fromdates f (cost=0.00..24.56 rows=1,228 width=8) (actual time=19.350..19.558 rows=1,003 loops=1)

25. 0.200 0.297 ↓ 1.8 2,213 1

Sort (cost=87.57..90.64 rows=1,228 width=8) (actual time=0.190..0.297 rows=2,213 loops=1)

  • Sort Key: t.deliverypointid
  • Sort Method: quicksort Memory: 72kB
26. 0.097 0.097 ↑ 1.2 1,003 1

CTE Scan on fromdates t (cost=0.00..24.56 rows=1,228 width=8) (actual time=0.001..0.097 rows=1,003 loops=1)

27.          

CTE dateslimit

28. 0.071 22.502 ↓ 472.0 472 1

Subquery Scan on x_1 (cost=0.50..0.96 rows=1 width=12) (actual time=22.104..22.502 rows=472 loops=1)

  • Filter: (x_1.nbr = 1)
  • Rows Removed by Filter: 59
29. 0.300 22.431 ↓ 40.8 531 1

WindowAgg (cost=0.50..0.79 rows=13 width=12) (actual time=22.103..22.431 rows=531 loops=1)

30. 0.149 22.131 ↓ 40.8 531 1

Sort (cost=0.50..0.53 rows=13 width=12) (actual time=22.102..22.131 rows=531 loops=1)

  • Sort Key: dates.deliverypointid, dates.fromdate, dates.todate
  • Sort Method: quicksort Memory: 49kB
31. 21.982 21.982 ↓ 40.8 531 1

CTE Scan on dates (cost=0.00..0.26 rows=13 width=12) (actual time=21.076..21.982 rows=531 loops=1)

32.          

CTE brps

33. 0.740 49.343 ↓ 49.1 834 1

Subquery Scan on x_2 (cost=468.46..589.07 rows=17 width=11) (actual time=44.926..49.343 rows=834 loops=1)

  • Filter: (x_2.nbr = 1)
  • Rows Removed by Filter: 2658
34. 3.230 48.603 ↓ 1.0 3,492 1

WindowAgg (cost=468.46..545.99 rows=3,446 width=21) (actual time=44.920..48.603 rows=3,492 loops=1)

35. 4.109 45.373 ↓ 1.0 3,492 1

Sort (cost=468.46..477.07 rows=3,446 width=21) (actual time=44.906..45.373 rows=3,492 loops=1)

  • Sort Key: deliveryperiod.deliverypointid, brpj.fromdate, brpj.ctid
  • Sort Method: quicksort Memory: 369kB
36. 1.676 41.264 ↓ 1.0 3,492 1

Hash Join (cost=119.54..265.99 rows=3,446 width=21) (actual time=3.550..41.264 rows=3,492 loops=1)

  • Hash Cond: (deliveryperiod.deliveryperiodid = brpj.deliveryperiodid)
37. 36.049 36.049 ↓ 1.0 3,492 1

Seq Scan on deliveryperiod (cost=0.00..90.46 rows=3,446 width=8) (actual time=0.003..36.049 rows=3,492 loops=1)

38. 0.769 3.539 ↓ 1.0 3,492 1

Hash (cost=76.46..76.46 rows=3,446 width=21) (actual time=3.539..3.539 rows=3,492 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 191kB
39. 2.770 2.770 ↓ 1.0 3,492 1

Seq Scan on deliveryperiodbrpjournal brpj (cost=0.00..76.46 rows=3,446 width=21) (actual time=0.004..2.770 rows=3,492 loops=1)

40.          

CTE address

41. 1.880 5,231.165 ↓ 200.3 12,019 1

Subquery Scan on x_3 (cost=4,729.54..5,118.89 rows=60 width=37) (actual time=5,220.711..5,231.165 rows=12,019 loops=1)

  • Filter: (x_3.nbr = 1)
  • Rows Removed by Filter: 43
42. 7.863 5,229.285 ↓ 1.0 12,062 1

WindowAgg (cost=4,729.54..4,969.14 rows=11,980 width=39) (actual time=5,220.707..5,229.285 rows=12,062 loops=1)

43. 7.995 5,221.422 ↓ 1.0 12,062 1

Sort (cost=4,729.54..4,759.49 rows=11,980 width=39) (actual time=5,220.699..5,221.422 rows=12,062 loops=1)

  • Sort Key: fa.addressid, fad.addressdetailnbr
  • Sort Method: quicksort Memory: 1378kB
44. 34.427 5,213.427 ↓ 1.0 12,062 1

Merge Right Join (cost=1,751.11..3,918.00 rows=11,980 width=39) (actual time=1,803.835..5,213.427 rows=12,062 loops=1)

  • Merge Cond: (((tj.country)::text = (fa.country)::text) AND ((tj.postalcode)::text = (fa.postalcode)::text) AND (tj.towncode = fa.towncode))
  • Filter: (tj.todate IS NULL)
45. 3,374.729 3,374.729 ↑ 1.0 36,608 1

Index Scan using townjournal_town_idx on townjournal tj (cost=0.29..1,768.78 rows=37,145 width=23) (actual time=1.018..3,374.729 rows=36,608 loops=1)

46. 8.745 1,804.271 ↓ 1.0 12,062 1

Sort (cost=1,750.82..1,780.77 rows=11,980 width=46) (actual time=1,802.809..1,804.271 rows=12,062 loops=1)

  • Sort Key: fa.country, fa.postalcode, fa.towncode
  • Sort Method: quicksort Memory: 1397kB
47. 12.454 1,795.526 ↓ 1.0 12,062 1

Hash Join (cost=395.51..939.27 rows=11,980 width=46) (actual time=787.270..1,795.526 rows=12,062 loops=1)

  • Hash Cond: (fad.addressid = fa.addressid)
48. 996.903 996.903 ↓ 1.0 12,062 1

Seq Scan on freeaddressesdetail fad (cost=0.00..303.48 rows=12,048 width=33) (actual time=1.074..996.903 rows=12,062 loops=1)

49. 5.173 786.169 ↓ 1.0 12,019 1

Hash (cost=246.34..246.34 rows=11,934 width=17) (actual time=786.169..786.169 rows=12,019 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 438kB
50. 780.996 780.996 ↓ 1.0 12,019 1

Seq Scan on freeaddresses fa (cost=0.00..246.34 rows=11,934 width=17) (actual time=0.047..780.996 rows=12,019 loops=1)

51.          

CTE meters

52. 1.841 2.748 ↓ 1.0 362 1

HashAggregate (cost=53.65..61.70 rows=358 width=41) (actual time=2.426..2.748 rows=362 loops=1)

  • Group Key: m.physicalconfigid
53. 0.258 0.907 ↓ 1.1 668 1

Hash Right Join (cost=14.08..44.14 rows=634 width=41) (actual time=0.281..0.907 rows=668 loops=1)

  • Hash Cond: (pr.meterid = m.meterid)
54. 0.377 0.377 ↓ 1.0 648 1

Seq Scan on physicalregister pr (cost=0.00..21.34 rows=634 width=11) (actual time=0.004..0.377 rows=648 loops=1)

55. 0.072 0.272 ↓ 1.0 363 1

Hash (cost=9.59..9.59 rows=359 width=38) (actual time=0.272..0.272 rows=363 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
56. 0.200 0.200 ↓ 1.0 363 1

Seq Scan on meter m (cost=0.00..9.59 rows=359 width=38) (actual time=0.004..0.200 rows=363 loops=1)

57.          

CTE cap

58. 2.134 17.240 ↓ 197.0 2,167 1

Nested Loop (cost=244.25..323.51 rows=11 width=52) (actual time=7.971..17.240 rows=2,167 loops=1)

59. 1.594 8.605 ↓ 197.0 2,167 1

HashAggregate (cost=243.97..244.08 rows=11 width=4) (actual time=7.957..8.605 rows=2,167 loops=1)

  • Group Key: x_4.capacitiesid
60. 0.480 7.011 ↓ 197.0 2,167 1

Subquery Scan on x_4 (cost=0.28..243.94 rows=11 width=4) (actual time=0.014..7.011 rows=2,167 loops=1)

  • Filter: (x_4.nbr = 1)
  • Rows Removed by Filter: 18
61. 2.240 6.531 ↑ 1.0 2,185 1

WindowAgg (cost=0.28..215.64 rows=2,264 width=8) (actual time=0.012..6.531 rows=2,185 loops=1)

62. 4.291 4.291 ↑ 1.0 2,185 1

Index Scan using capacities_generalconf_idx on capacities capacities_1 (cost=0.28..181.68 rows=2,264 width=8) (actual time=0.009..4.291 rows=2,185 loops=1)

  • Filter: (timeframe IS NULL)
  • Rows Removed by Filter: 2778
63. 6.501 6.501 ↑ 1.0 1 2,167

Index Scan using capacities_pk on capacities (cost=0.28..7.21 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=2,167)

  • Index Cond: (capacitiesid = x_4.capacitiesid)
64.          

CTE cappertf

65. 9.354 11.713 ↓ 1.1 1,378 1

GroupAggregate (cost=0.28..227.96 rows=1,225 width=18) (actual time=0.734..11.713 rows=1,378 loops=1)

  • Group Key: capacities_2.generalconfigid
66. 2.359 2.359 ↓ 1.1 2,735 1

Index Scan using capacities_generalconf_idx on capacities capacities_2 (cost=0.28..181.68 rows=2,484 width=18) (actual time=0.685..2.359 rows=2,735 loops=1)

  • Filter: ((timeframe IS NOT NULL) AND (contractedcapacity IS NOT NULL))
  • Rows Removed by Filter: 2228
67.          

CTE config

68. 0.198 29,583.716 ↓ 23.4 468 1

Subquery Scan on x_5 (cost=23,244.31..23,525.50 rows=20 width=938) (actual time=29,572.843..29,583.716 rows=468 loops=1)

  • Filter: (x_5.nbr = 1)
  • Rows Removed by Filter: 9
69. 10.636 29,583.518 ↑ 8.4 477 1

WindowAgg (cost=23,244.31..23,475.28 rows=4,017 width=721) (actual time=29,572.840..29,583.518 rows=477 loops=1)

70. 6.379 29,572.882 ↑ 8.4 477 1

Sort (cost=23,244.31..23,254.35 rows=4,017 width=721) (actual time=29,572.780..29,572.882 rows=477 loops=1)

  • Sort Key: dp1.deliverypointid, f_1.fromdate, f_1.todate
  • Sort Method: quicksort Memory: 25002kB
71. 1,980.876 29,566.503 ↑ 8.4 477 1

GroupAggregate (cost=22,230.58..23,003.85 rows=4,017 width=721) (actual time=27,395.622..29,566.503 rows=477 loops=1)

  • Group Key: dp1.deliverypointid, gc.generalconfigid, lc.logicalconfigid, pc.physicalconfigid, f_1.fromdate, f_1.todate, gc.readingfrequency, gc.direction, gc.gridrate, gc.slpid, gc.readingmonth, gc.netarea, gc.pressurelevel, gc.voltagelevel, gc.copperloss, gc.ironloss, gc.powerlimiter, gc.decprod, gc.category, gc.state, gc.deliverystatus, cap.contractedcapacity, cap.contractedcapacityunit, cappertf.contractedcapacitytimeframe, cappertf.contractedcapacitypertf, cappertf.contractedcapacityunitpertf, cap.physicalcapacity, cap.physicalcapacityunit, gc.premisescategory, gc.energyusage, gc.sourceid, m2.meternumber, m2.metertype, m2.smartmeterstatus, lc.configcode, lc.sourceid, m2.constant, m2.format
72. 27,316.918 27,585.627 ↓ 102.5 411,638 1

Sort (cost=22,230.58..22,240.62 rows=4,017 width=721) (actual time=27,384.730..27,585.627 rows=411,638 loops=1)

  • Sort Key: dp1.deliverypointid, gc.generalconfigid, lc.logicalconfigid, pc.physicalconfigid, f_1.fromdate, f_1.todate, gc.readingfrequency, gc.direction, gc.gridrate, gc.slpid, gc.readingmonth, gc.netarea, gc.pressurelevel, gc.voltagelevel, gc.copperloss, gc.ironloss, gc.powerlimiter, gc.decprod, gc.category, gc.state, gc.deliverystatus, cap.contractedcapacity, cap.contractedcapacityunit, cappertf.contractedcapacitytimeframe, cappertf.contractedcapacitypertf, cappertf.contractedcapacityunitpertf, cap.physicalcapacity, cap.physicalcapacityunit, gc.premisescategory, gc.energyusage, gc.sourceid, m2.meternumber, m2.metertype, m2.smartmeterstatus, lc.configcode, lc.sourceid, m2.constant, m2.format
  • Sort Method: external merge Disk: 167272kB
73. 159.589 268.709 ↓ 102.5 411,638 1

Nested Loop Left Join (cost=349.63..21,990.12 rows=4,017 width=721) (actual time=54.205..268.709 rows=411,638 loops=1)

  • Join Filter: (pc.physicalconfigid = m2.physicalconfigid)
  • Rows Removed by Join Filter: 102384
74. 1.836 67.884 ↑ 2.9 793 1

Hash Left Join (cost=349.63..398.52 rows=2,268 width=714) (actual time=53.901..67.884 rows=793 loops=1)

  • Hash Cond: (gc.generalconfigid = cap.generalconfigid)
75. 1.598 45.659 ↑ 2.9 793 1

Hash Right Join (cost=349.28..389.59 rows=2,268 width=586) (actual time=33.502..45.659 rows=793 loops=1)

  • Hash Cond: (cappertf.generalconfigid = gc.generalconfigid)
76. 12.224 12.224 ↓ 1.1 1,378 1

CTE Scan on cappertf (cost=0.00..24.50 rows=1,225 width=100) (actual time=0.737..12.224 rows=1,378 loops=1)

77. 0.463 31.837 ↑ 2.9 793 1

Hash (cost=320.93..320.93 rows=2,268 width=490) (actual time=31.837..31.837 rows=793 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 275kB
78. 0.553 31.374 ↑ 2.9 793 1

Hash Right Join (cost=293.18..320.93 rows=2,268 width=490) (actual time=30.313..31.374 rows=793 loops=1)

  • Hash Cond: ((lc.deliverypointid = dp1.deliverypointid) AND (lc.deliverypointcomponentid = dpc_1.deliverypointcomponentid))
  • Join Filter: ((lc.fromdate <= f_1.fromdate) AND ((lc.todate > f_1.fromdate) OR (lc.todate IS NULL)))
  • Rows Removed by Join Filter: 9
  • Filter: ((gc.generalconfigid IS NOT NULL) OR (lc.logicalconfigid IS NOT NULL))
  • Rows Removed by Filter: 503
79. 0.265 0.675 ↓ 1.1 537 1

Hash Right Join (cost=11.25..35.16 rows=508 width=112) (actual time=0.159..0.675 rows=537 loops=1)

  • Hash Cond: (lr.logicalconfigid = lc.logicalconfigid)
80. 0.264 0.264 ↓ 1.0 580 1

Seq Scan on logicalregister lr (cost=0.00..16.70 rows=570 width=60) (actual time=0.004..0.264 rows=580 loops=1)

81. 0.048 0.146 ↓ 1.0 215 1

Hash (cost=8.59..8.59 rows=213 width=56) (actual time=0.146..0.146 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
82. 0.098 0.098 ↓ 1.0 215 1

Seq Scan on logicalconfig lc (cost=0.00..8.59 rows=213 width=56) (actual time=0.006..0.098 rows=215 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 28
83. 0.365 30.146 ↓ 1.0 975 1

Hash (cost=267.67..267.67 rows=951 width=398) (actual time=30.146..30.146 rows=975 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 141kB
84. 0.395 29.781 ↓ 1.0 975 1

Hash Left Join (cost=132.24..267.67 rows=951 width=398) (actual time=28.548..29.781 rows=975 loops=1)

  • Hash Cond: ((dp1.deliverypointid = pc.deliverypointid) AND (dpc_1.deliverypointcomponentid = pc.deliverypointcomponentid))
  • Join Filter: ((pc.fromdate <= f_1.fromdate) AND ((pc.todate > f_1.fromdate) OR (pc.todate IS NULL)))
  • Rows Removed by Join Filter: 13
85. 0.401 25.957 ↓ 1.0 973 1

Hash Right Join (cost=100.03..221.18 rows=951 width=230) (actual time=25.108..25.957 rows=973 loops=1)

  • Hash Cond: ((gc.deliverypointid = dp1.deliverypointid) AND (gc.deliverypointcomponentid = dpc_1.deliverypointcomponentid))
  • Join Filter: ((gc.fromdate <= f_1.fromdate) AND ((gc.todate > f_1.fromdate) OR (gc.todate IS NULL)))
  • Rows Removed by Join Filter: 45
86. 0.475 0.475 ↑ 1.1 503 1

Index Scan using generalconfig_sourceid_id on generalconfig gc (cost=0.28..117.13 rows=572 width=230) (actual time=0.020..0.475 rows=503 loops=1)

  • Index Cond: ((sourceid)::text = 'DGO'::text)
  • Filter: ((deleted IS FALSE) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 84
87. 0.191 25.081 ↓ 1.0 971 1

Hash (cost=85.48..85.48 rows=951 width=16) (actual time=25.081..25.081 rows=971 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
88. 0.325 24.890 ↓ 1.0 971 1

Hash Join (cost=35.70..85.48 rows=951 width=16) (actual time=24.113..24.890 rows=971 loops=1)

  • Hash Cond: (dp1.deliverypointid = dpc_1.deliverypointid)
89. 0.266 23.893 ↓ 1.0 971 1

Merge Left Join (cost=0.31..37.00 rows=953 width=12) (actual time=23.401..23.893 rows=971 loops=1)

  • Merge Cond: (dp1.deliverypointid = f_1.deliverypointid)
90. 0.939 0.939 ↓ 1.0 971 1

Index Only Scan using deliverypoint_deliverypointid_idx on deliverypoint dp1 (cost=0.28..34.57 rows=953 width=4) (actual time=0.738..0.939 rows=971 loops=1)

  • Heap Fetches: 262
91. 0.084 22.688 ↓ 472.0 472 1

Sort (cost=0.03..0.04 rows=1 width=12) (actual time=22.659..22.688 rows=472 loops=1)

  • Sort Key: f_1.deliverypointid
  • Sort Method: quicksort Memory: 47kB
92. 22.604 22.604 ↓ 472.0 472 1

CTE Scan on dateslimit f_1 (cost=0.00..0.02 rows=1 width=12) (actual time=22.106..22.604 rows=472 loops=1)

93. 0.165 0.672 ↓ 1.0 971 1

Hash (cost=23.51..23.51 rows=951 width=8) (actual time=0.672..0.672 rows=971 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
94. 0.507 0.507 ↓ 1.0 971 1

Seq Scan on deliverypointcomponent dpc_1 (cost=0.00..23.51 rows=951 width=8) (actual time=0.005..0.507 rows=971 loops=1)

95. 0.167 3.429 ↓ 1.0 331 1

Hash (cost=27.28..27.28 rows=329 width=184) (actual time=3.429..3.429 rows=331 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 85kB
96. 0.135 3.262 ↓ 1.0 331 1

Hash Right Join (cost=15.48..27.28 rows=329 width=184) (actual time=2.629..3.262 rows=331 loops=1)

  • Hash Cond: (m2.physicalconfigid = pc.physicalconfigid)
97. 2.932 2.932 ↓ 1.0 362 1

CTE Scan on meters m2 (cost=0.00..7.16 rows=358 width=164) (actual time=2.429..2.932 rows=362 loops=1)

98. 0.058 0.195 ↓ 1.0 331 1

Hash (cost=11.37..11.37 rows=329 width=20) (actual time=0.195..0.195 rows=331 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
99. 0.137 0.137 ↓ 1.0 331 1

Seq Scan on physicalconfig pc (cost=0.00..11.37 rows=329 width=20) (actual time=0.007..0.137 rows=331 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 31
100. 0.970 20.389 ↓ 197.0 2,167 1

Hash (cost=0.22..0.22 rows=11 width=132) (actual time=20.389..20.389 rows=2,167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
101. 19.419 19.419 ↓ 197.0 2,167 1

CTE Scan on cap (cost=0.00..0.22 rows=11 width=132) (actual time=7.975..19.419 rows=2,167 loops=1)

102. 41.107 41.236 ↓ 1.0 648 793

Materialize (cost=0.00..24.51 rows=634 width=11) (actual time=0.000..0.052 rows=648 loops=793)

103. 0.129 0.129 ↓ 1.0 648 1

Seq Scan on physicalregister pr_1 (cost=0.00..21.34 rows=634 width=11) (actual time=0.005..0.129 rows=648 loops=1)

104. 0.882 34,895.663 ↓ 23.4 468 1

Hash Left Join (cost=3.43..40.15 rows=20 width=1,503) (actual time=34,890.045..34,895.663 rows=468 loops=1)

  • Hash Cond: (dp.addressid = address.addressid)
105. 0.384 29,655.590 ↓ 23.4 468 1

Hash Left Join (cost=1.48..38.11 rows=20 width=1,283) (actual time=29,650.832..29,655.590 rows=468 loops=1)

  • Hash Cond: (dp.deliverypointid = brps.deliverypointid)
106. 0.487 29,604.929 ↓ 23.4 468 1

Nested Loop (cost=0.93..37.48 rows=20 width=1,255) (actual time=29,600.540..29,604.929 rows=468 loops=1)

107. 0.977 29,601.634 ↓ 23.4 468 1

Hash Join (cost=0.65..27.93 rows=20 width=1,220) (actual time=29,600.523..29,601.634 rows=468 loops=1)

  • Hash Cond: (dpc.deliverypointid = config.deliverypointid)
108. 0.146 0.146 ↓ 1.0 971 1

Seq Scan on deliverypointcomponent dpc (cost=0.00..23.51 rows=951 width=12) (actual time=0.004..0.146 rows=971 loops=1)

109. 8.436 29,600.511 ↓ 23.4 468 1

Hash (cost=0.40..0.40 rows=20 width=1,208) (actual time=29,600.511..29,600.511 rows=468 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24419kB
110. 29,592.075 29,592.075 ↓ 23.4 468 1

CTE Scan on config (cost=0.00..0.40 rows=20 width=1,208) (actual time=29,572.879..29,592.075 rows=468 loops=1)

111. 2.808 2.808 ↑ 1.0 1 468

Index Scan using deliverypoint_deliverypointid_idx on deliverypoint dp (cost=0.28..0.47 rows=1 width=43) (actual time=0.006..0.006 rows=1 loops=468)

  • Index Cond: (deliverypointid = dpc.deliverypointid)
112. 0.349 50.277 ↓ 49.1 834 1

Hash (cost=0.34..0.34 rows=17 width=36) (actual time=50.277..50.277 rows=834 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
113. 49.928 49.928 ↓ 49.1 834 1

CTE Scan on brps (cost=0.00..0.34 rows=17 width=36) (actual time=44.930..49.928 rows=834 loops=1)

114. 2.607 5,239.191 ↓ 200.3 12,019 1

Hash (cost=1.20..1.20 rows=60 width=228) (actual time=5,239.191..5,239.191 rows=12,019 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 753kB
115. 5,236.584 5,236.584 ↓ 200.3 12,019 1

CTE Scan on address (cost=0.00..1.20 rows=60 width=228) (actual time=5,220.714..5,236.584 rows=12,019 loops=1)

Planning time : 52.776 ms
Execution time : 34,949.749 ms