explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SEdwq

Settings
# exclusive inclusive rows x rows loops node
1. 42.174 24,971.705 ↑ 7.7 524 1

HashAggregate (cost=31,024.76..31,064.93 rows=4,017 width=1,503) (actual time=24,971.031..24,971.705 rows=524 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.422 3.250 ↑ 1.2 1,003 1

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

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

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

  • Group Key: generalconfig.fromdate, generalconfig.deliverypointid
5. 0.156 2.286 ↓ 1.7 2,090 1

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

6. 0.181 0.675 ↓ 4.2 502 1

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

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

Index Scan using generalconfig_sourceid_id on generalconfig (cost=0.28..118.64 rows=570 width=8) (actual time=0.023..0.494 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.086 0.209 ↓ 1.1 214 1

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

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

Seq Scan on logicalconfig (cost=0.00..9.18 rows=213 width=8) (actual time=0.005..0.123 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.194 0.562 ↓ 4.2 500 1

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

  • Group Key: generalconfig_1.todate, generalconfig_1.deliverypointid
11. 0.368 0.368 ↑ 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.011..0.368 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.075 0.173 ↓ 1.1 213 1

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

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

Seq Scan on logicalconfig logicalconfig_1 (cost=0.00..9.18 rows=213 width=8) (actual time=0.004..0.098 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.115 0.265 ↓ 1.1 331 1

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

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

Seq Scan on physicalconfig (cost=0.00..12.27 rows=329 width=8) (actual time=0.006..0.150 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.106 0.246 ↓ 1.1 330 1

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

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

Seq Scan on physicalconfig physicalconfig_1 (cost=0.00..12.27 rows=329 width=8) (actual time=0.003..0.140 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.137 5.818 ↓ 40.8 531 1

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

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

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

21. 0.603 5.091 ↑ 2.4 1,076 1

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

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

Merge Join (cost=175.14..313.23 rows=2,538 width=12) (actual time=3.769..4.488 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.194 3.616 ↑ 1.2 1,003 1

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

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

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

25. 0.264 0.341 ↓ 1.8 2,213 1

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

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

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

27.          

CTE datesuniqueid

28. 0.319 11.325 ↓ 40.8 531 1

HashAggregate (cost=48.56..48.69 rows=13 width=24) (actual time=11.212..11.325 rows=531 loops=1)

  • Group Key: dp1.fromdate, dp1.todate, dp1.deliverypointid, max(gc.generalconfigid) OVER (?), max(lc.logicalconfigid) OVER (?), max(pc.physicalconfigid) OVER (?)
29. 0.460 11.006 ↓ 41.5 540 1

WindowAgg (cost=48.01..48.37 rows=13 width=24) (actual time=10.529..11.006 rows=540 loops=1)

30. 0.293 10.546 ↓ 41.5 540 1

Sort (cost=48.01..48.04 rows=13 width=24) (actual time=10.521..10.546 rows=540 loops=1)

  • Sort Key: dp1.fromdate, dp1.todate, dp1.deliverypointid
  • Sort Method: quicksort Memory: 57kB
31. 0.550 10.253 ↓ 41.5 540 1

Nested Loop Left Join (cost=1.12..47.77 rows=13 width=24) (actual time=6.072..10.253 rows=540 loops=1)

  • Join Filter: ((pc.fromdate <= dp1.fromdate) AND ((pc.todate > dp1.fromdate) OR (pc.todate IS NULL)) AND (pc.deliverypointid = dp1.deliverypointid))
  • Rows Removed by Join Filter: 52
32. 0.277 9.167 ↓ 41.2 536 1

Nested Loop Left Join (cost=0.85..43.16 rows=13 width=24) (actual time=6.059..9.167 rows=536 loops=1)

  • Join Filter: ((lc.fromdate <= dp1.fromdate) AND ((lc.todate > dp1.fromdate) OR (lc.todate IS NULL)) AND (lc.deliverypointid = dp1.deliverypointid))
  • Rows Removed by Join Filter: 26
33. 0.341 8.357 ↓ 41.0 533 1

Nested Loop Left Join (cost=0.70..40.37 rows=13 width=20) (actual time=6.052..8.357 rows=533 loops=1)

  • Join Filter: ((gc.fromdate <= dp1.fromdate) AND ((gc.todate > dp1.fromdate) OR (gc.todate IS NULL)) AND (gc.deliverypointid = dp1.deliverypointid))
  • Rows Removed by Join Filter: 123
34. 0.279 6.423 ↓ 40.8 531 1

Hash Join (cost=0.42..27.63 rows=13 width=16) (actual time=6.032..6.423 rows=531 loops=1)

  • Hash Cond: (dpc_1.deliverypointid = dp1.deliverypointid)
35. 0.119 0.119 ↓ 1.0 971 1

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

36. 0.078 6.025 ↓ 40.8 531 1

Hash (cost=0.26..0.26 rows=13 width=12) (actual time=6.025..6.025 rows=531 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
37. 5.947 5.947 ↓ 40.8 531 1

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

38. 1.593 1.593 ↑ 1.0 1 531

Index Scan using generalconfig_deliverypointcomponentid_idx on generalconfig gc (cost=0.28..0.96 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=531)

  • Index Cond: (deliverypointcomponentid = dpc_1.deliverypointcomponentid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 1
39. 0.533 0.533 ↓ 0.0 0 533

Index Scan using logicalconfig_deliverypointcomponentid_idx on logicalconfig lc (cost=0.14..0.20 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=533)

  • Index Cond: (deliverypointcomponentid = dpc_1.deliverypointcomponentid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 0
40. 0.536 0.536 ↑ 1.0 1 536

Index Scan using physicalconfig_dpcomp_idx on physicalconfig pc (cost=0.27..0.34 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=536)

  • Index Cond: (deliverypointcomponentid = dpc_1.deliverypointcomponentid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 0
41.          

CTE brps

42. 0.356 6.314 ↓ 49.1 834 1

Subquery Scan on x_1 (cost=468.46..589.07 rows=17 width=11) (actual time=4.338..6.314 rows=834 loops=1)

  • Filter: (x_1.nbr = 1)
  • Rows Removed by Filter: 2658
43. 1.430 5.958 ↓ 1.0 3,492 1

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

44. 1.674 4.528 ↓ 1.0 3,492 1

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

  • Sort Key: deliveryperiod.deliverypointid, brpj.fromdate, brpj.ctid
  • Sort Method: quicksort Memory: 369kB
45. 1.333 2.854 ↓ 1.0 3,492 1

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

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

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

47. 0.557 1.150 ↓ 1.0 3,492 1

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

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

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

49.          

CTE address

50. 1.789 61.262 ↓ 200.3 12,019 1

Subquery Scan on x_2 (cost=4,729.54..5,118.89 rows=60 width=37) (actual time=50.646..61.262 rows=12,019 loops=1)

  • Filter: (x_2.nbr = 1)
  • Rows Removed by Filter: 43
51. 8.194 59.473 ↓ 1.0 12,062 1

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

52. 4.757 51.279 ↓ 1.0 12,062 1

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

  • Sort Key: fa.addressid, fad.addressdetailnbr
  • Sort Method: quicksort Memory: 1378kB
53. 21.145 46.522 ↓ 1.0 12,062 1

Merge Right Join (cost=1,751.11..3,918.00 rows=11,980 width=39) (actual time=17.178..46.522 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)
54. 7.555 7.555 ↑ 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=0.012..7.555 rows=36,608 loops=1)

55. 5.389 17.822 ↓ 1.0 12,062 1

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

  • Sort Key: fa.country, fa.postalcode, fa.towncode
  • Sort Method: quicksort Memory: 1397kB
56. 5.433 12.433 ↓ 1.0 12,062 1

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

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

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

58. 1.772 4.019 ↓ 1.0 12,019 1

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

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

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

60.          

CTE meters

61. 2.543 3.110 ↓ 1.0 362 1

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

  • Group Key: m.physicalconfigid
62. 0.280 0.567 ↓ 1.1 668 1

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

  • Hash Cond: (pr.meterid = m.meterid)
63. 0.105 0.105 ↓ 1.0 648 1

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

64. 0.077 0.182 ↓ 1.0 363 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
65. 0.105 0.105 ↓ 1.0 363 1

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

66.          

CTE cap

67. 1.903 8.040 ↓ 197.0 2,167 1

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

68. 0.956 3.970 ↓ 197.0 2,167 1

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

  • Group Key: x_3.capacitiesid
69. 0.307 3.014 ↓ 197.0 2,167 1

Subquery Scan on x_3 (cost=0.28..243.94 rows=11 width=4) (actual time=0.013..3.014 rows=2,167 loops=1)

  • Filter: (x_3.nbr = 1)
  • Rows Removed by Filter: 18
70. 1.353 2.707 ↑ 1.0 2,185 1

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

71. 1.354 1.354 ↑ 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.007..1.354 rows=2,185 loops=1)

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

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

  • Index Cond: (capacitiesid = x_3.capacitiesid)
73.          

CTE cappertf

74. 11.749 13.585 ↓ 1.1 1,378 1

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

  • Group Key: capacities_2.generalconfigid
75. 1.836 1.836 ↓ 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.018..1.836 rows=2,735 loops=1)

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

CTE config

77. 1,571.588 24,837.054 ↑ 7.7 524 1

GroupAggregate (cost=22,131.52..22,864.63 rows=4,017 width=710) (actual time=23,089.559..24,837.054 rows=524 loops=1)

  • Group Key: dp1_1.deliverypointid, gc_1.generalconfigid, lc_1.logicalconfigid, pc_1.physicalconfigid, f_1.fromdate, f_1.todate, gc_1.readingfrequency, gc_1.direction, gc_1.gridrate, gc_1.slpid, gc_1.readingmonth, gc_1.netarea, gc_1.pressurelevel, gc_1.voltagelevel, gc_1.copperloss, gc_1.ironloss, gc_1.powerlimiter, gc_1.decprod, gc_1.category, gc_1.state, gc_1.deliverystatus, cap.contractedcapacity, cap.contractedcapacityunit, cappertf.contractedcapacitytimeframe, cappertf.contractedcapacitypertf, cappertf.contractedcapacityunitpertf, cap.physicalcapacity, cap.physicalcapacityunit, gc_1.premisescategory, gc_1.energyusage, gc_1.sourceid, m2.meternumber, m2.metertype, m2.smartmeterstatus, lc_1.configcode, lc_1.sourceid, m2.constant, m2.format
78. 23,033.988 23,265.466 ↓ 110.9 445,365 1

Sort (cost=22,131.52..22,141.57 rows=4,017 width=710) (actual time=23,084.867..23,265.466 rows=445,365 loops=1)

  • Sort Key: dp1_1.deliverypointid, gc_1.generalconfigid, lc_1.logicalconfigid, pc_1.physicalconfigid, f_1.fromdate, f_1.todate, gc_1.readingfrequency, gc_1.direction, gc_1.gridrate, gc_1.slpid, gc_1.readingmonth, gc_1.netarea, gc_1.pressurelevel, gc_1.voltagelevel, gc_1.copperloss, gc_1.ironloss, gc_1.powerlimiter, gc_1.decprod, gc_1.category, gc_1.state, gc_1.deliverystatus, cap.contractedcapacity, cap.contractedcapacityunit, cappertf.contractedcapacitytimeframe, cappertf.contractedcapacitypertf, cappertf.contractedcapacityunitpertf, cap.physicalcapacity, cap.physicalcapacityunit, gc_1.premisescategory, gc_1.energyusage, gc_1.sourceid, m2.meternumber, m2.metertype, m2.smartmeterstatus, lc_1.configcode, lc_1.sourceid, m2.constant, m2.format
  • Sort Method: external merge Disk: 177504kB
79. 139.938 231.478 ↓ 110.9 445,365 1

Nested Loop Left Join (cost=253.64..21,891.07 rows=4,017 width=710) (actual time=32.795..231.478 rows=445,365 loops=1)

  • Join Filter: (pc_1.physicalconfigid = m2.physicalconfigid)
  • Rows Removed by Join Filter: 122472
80. 1.830 50.368 ↑ 2.6 876 1

Hash Left Join (cost=253.64..299.46 rows=2,268 width=714) (actual time=32.555..50.368 rows=876 loops=1)

  • Hash Cond: (gc_1.generalconfigid = cap.generalconfigid)
81. 1.726 38.566 ↑ 2.6 876 1

Hash Right Join (cost=253.29..290.54 rows=2,268 width=586) (actual time=22.571..38.566 rows=876 loops=1)

  • Hash Cond: (cappertf.generalconfigid = gc_1.generalconfigid)
82. 14.891 14.891 ↓ 1.1 1,378 1

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

83. 0.515 21.949 ↑ 2.6 876 1

Hash (cost=224.94..224.94 rows=2,268 width=490) (actual time=21.949..21.949 rows=876 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 298kB
84. 0.656 21.434 ↑ 2.6 876 1

Hash Left Join (cost=172.49..224.94 rows=2,268 width=490) (actual time=20.260..21.434 rows=876 loops=1)

  • Hash Cond: (dp1_1.deliverypointid = f_1.deliverypointid)
  • Filter: ((gc_1.generalconfigid IS NOT NULL) OR (lc_1.logicalconfigid IS NOT NULL))
  • Rows Removed by Filter: 506
85. 0.313 0.828 ↓ 1.0 971 1

Hash Join (cost=35.67..83.05 rows=951 width=4) (actual time=0.302..0.828 rows=971 loops=1)

  • Hash Cond: (dp1_1.deliverypointid = dpc_2.deliverypointid)
86. 0.234 0.234 ↓ 1.0 971 1

Index Only Scan using deliverypoint_deliverypointid_idx on deliverypoint dp1_1 (cost=0.28..34.57 rows=953 width=4) (actual time=0.016..0.234 rows=971 loops=1)

  • Heap Fetches: 262
87. 0.146 0.281 ↓ 1.0 971 1

Hash (cost=23.51..23.51 rows=951 width=4) (actual time=0.281..0.281 rows=971 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
88. 0.135 0.135 ↓ 1.0 971 1

Seq Scan on deliverypointcomponent dpc_2 (cost=0.00..23.51 rows=951 width=4) (actual time=0.003..0.135 rows=971 loops=1)

89. 0.848 19.950 ↓ 28.5 883 1

Hash (cost=136.43..136.43 rows=31 width=490) (actual time=19.950..19.950 rows=883 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 300kB
90. 0.691 19.102 ↓ 28.5 883 1

Nested Loop Left Join (cost=20.77..136.43 rows=31 width=490) (actual time=14.749..19.102 rows=883 loops=1)

91. 0.674 17.880 ↓ 40.8 531 1

Nested Loop Left Join (cost=20.50..128.88 rows=13 width=434) (actual time=14.740..17.880 rows=531 loops=1)

92. 0.313 16.144 ↓ 40.8 531 1

Hash Right Join (cost=20.22..28.85 rows=13 width=224) (actual time=14.726..16.144 rows=531 loops=1)

  • Hash Cond: (m2.physicalconfigid = pc_1.physicalconfigid)
93. 3.569 3.569 ↓ 1.0 362 1

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

94. 0.101 12.262 ↓ 40.8 531 1

Hash (cost=20.05..20.05 rows=13 width=60) (actual time=12.262..12.262 rows=531 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
95. 0.183 12.161 ↓ 40.8 531 1

Hash Right Join (cost=9.00..20.05 rows=13 width=60) (actual time=11.943..12.161 rows=531 loops=1)

  • Hash Cond: (pc_1.physicalconfigid = f_1.physicalconfigid)
96. 0.043 0.043 ↓ 1.0 362 1

Seq Scan on physicalconfig pc_1 (cost=0.00..9.58 rows=358 width=4) (actual time=0.003..0.043 rows=362 loops=1)

97. 0.094 11.935 ↓ 40.8 531 1

Hash (cost=8.84..8.84 rows=13 width=60) (actual time=11.935..11.935 rows=531 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
98. 0.147 11.841 ↓ 40.8 531 1

Hash Right Join (cost=0.42..8.84 rows=13 width=60) (actual time=11.675..11.841 rows=531 loops=1)

  • Hash Cond: (lc_1.logicalconfigid = f_1.logicalconfigid)
99. 0.029 0.029 ↓ 1.0 243 1

Seq Scan on logicalconfig lc_1 (cost=0.00..7.39 rows=239 width=40) (actual time=0.002..0.029 rows=243 loops=1)

100. 0.108 11.665 ↓ 40.8 531 1

Hash (cost=0.26..0.26 rows=13 width=24) (actual time=11.665..11.665 rows=531 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
101. 11.557 11.557 ↓ 40.8 531 1

CTE Scan on datesuniqueid f_1 (cost=0.00..0.26 rows=13 width=24) (actual time=11.213..11.557 rows=531 loops=1)

102. 1.062 1.062 ↑ 1.0 1 531

Index Scan using generalconfig_pkey on generalconfig gc_1 (cost=0.28..7.68 rows=1 width=214) (actual time=0.002..0.002 rows=1 loops=531)

  • Index Cond: (generalconfigid = f_1.generalconfigid)
103. 0.531 0.531 ↑ 2.0 1 531

Index Scan using logicalregister_logicalconfigid_idx on logicalregister lr (cost=0.28..0.56 rows=2 width=60) (actual time=0.001..0.001 rows=1 loops=531)

  • Index Cond: (logicalconfigid = lc_1.logicalconfigid)
104. 0.487 9.972 ↓ 197.0 2,167 1

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

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

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

106. 41.086 41.172 ↓ 1.0 648 876

Materialize (cost=0.00..24.51 rows=634 width=0) (actual time=0.000..0.047 rows=648 loops=876)

107. 0.086 0.086 ↓ 1.0 648 1

Seq Scan on physicalregister pr_1 (cost=0.00..21.34 rows=634 width=0) (actual time=0.004..0.086 rows=648 loops=1)

108. 0.834 24,929.531 ↑ 7.7 524 1

Hash Join (cost=116.30..251.88 rows=4,017 width=1,503) (actual time=23,168.648..24,929.531 rows=524 loops=1)

  • Hash Cond: (config.deliverypointid = dpc.deliverypointid)
109. 24,849.657 24,849.657 ↑ 7.7 524 1

CTE Scan on config (cost=0.00..80.34 rows=4,017 width=1,208) (actual time=23,089.596..24,849.657 rows=524 loops=1)

110. 0.407 79.040 ↓ 1.0 971 1

Hash (cost=104.42..104.42 rows=951 width=307) (actual time=79.040..79.040 rows=971 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 129kB
111. 1.010 78.633 ↓ 1.0 971 1

Hash Left Join (cost=37.90..104.42 rows=951 width=307) (actual time=76.546..78.633 rows=971 loops=1)

  • Hash Cond: (dp.addressid = address.addressid)
112. 0.367 8.185 ↓ 1.0 971 1

Hash Left Join (cost=35.95..98.30 rows=951 width=87) (actual time=7.097..8.185 rows=971 loops=1)

  • Hash Cond: (dp.deliverypointid = brps.deliverypointid)
113. 0.587 1.062 ↓ 1.0 971 1

Hash Join (cost=35.40..94.01 rows=951 width=55) (actual time=0.335..1.062 rows=971 loops=1)

  • Hash Cond: (dp.deliverypointid = dpc.deliverypointid)
114. 0.153 0.153 ↓ 1.0 971 1

Seq Scan on deliverypoint dp (cost=0.00..45.53 rows=953 width=43) (actual time=0.006..0.153 rows=971 loops=1)

115. 0.154 0.322 ↓ 1.0 971 1

Hash (cost=23.51..23.51 rows=951 width=12) (actual time=0.322..0.322 rows=971 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
116. 0.168 0.168 ↓ 1.0 971 1

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

117. 0.129 6.756 ↓ 49.1 834 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
118. 6.627 6.627 ↓ 49.1 834 1

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

119. 2.698 69.438 ↓ 200.3 12,019 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 753kB
120. 66.740 66.740 ↓ 200.3 12,019 1

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

Planning time : 11.346 ms
Execution time : 24,976.986 ms