explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qbe1

Settings
# exclusive inclusive rows x rows loops node
1. 2,196.510 17,892.258 ↓ 7.2 99,528 1

Sort (cost=8,007,257.16..8,007,291.87 rows=13,886 width=820) (actual time=17,797.843..17,892.258 rows=99,528 loops=1)

  • Sort Key: dp.gsrn, (CASE WHEN (i.previousindexdate IS NULL) THEN i.indexdate WHEN (i.previousindexdate <= '9999-12-31'::date) THEN i.previousindexdate ELSE cdp.fromdate END), (CASE WHEN (i.indexdate <= '9999-12-31'::date) THEN i.indexdate ELSE cdp.todate END)
  • Sort Method: external merge Disk: 25,808kB
2. 360.606 15,695.748 ↓ 7.2 99,528 1

HashAggregate (cost=8,006,162.85..8,006,301.71 rows=13,886 width=820) (actual time=15,600.255..15,695.748 rows=99,528 loops=1)

  • Group Key: dp.gsrn, (CASE WHEN (i.previousindexdate IS NULL) THEN i.indexdate WHEN (i.previousindexdate <= '9999-12-31'::date) THEN i.previousindexdate ELSE cdp.fromdate END), (CASE WHEN (i.indexdate <= '9999-12-31'::date) THEN i.indexdate ELSE cdp.todate END), ('$IXD'::character varying), s.gln, cdp.fromdate, cdp.todate, (CASE WHEN (((i.status)::text = 'R'::text) OR ((i.indexdate = i.previousindexdate) AND (alternatives: SubPlan 7 or hashed SubPlan 8))) THEN 'RECTIFIED'::text ELSE 'ACTUAL'::text END), (CASE WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'C'::text) THEN 'CONTINUOUS'::text WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'M'::text) THEN 'MONTH'::text WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'Y'::text) THEN 'YEAR'::text ELSE 'UNKNOWN'::text END), (CASE WHEN (((dp.marketcode)::text = 'EL'::text) AND ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'C'::text)) THEN 15 WHEN (((dp.marketcode)::text = 'NG'::text) AND ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'C'::text)) THEN 60 WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'M'::text) THEN 1 WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'Y'::text) THEN 1 ELSE 1 END), (CASE WHEN ((t.reason)::text = 'E23'::text) THEN 'PERIODIC_METERING'::text WHEN ((t.reason)::text = 'E53'::text) THEN 'METERING_ON_DEMAND'::text WHEN ((t.reason)::text = 'B95'::text) THEN 'AD_HOC'::text WHEN ((t.reason)::text = 'E24'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'E03'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'B74'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'B75'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E40'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'BB1'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'BB2'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E35'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'BC0'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E20'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E06'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'B90'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E21'::text) THEN 'CUSTOMER_SWITCH'::text WHEN ((t.reason)::text = 'E25'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'ERR'::text) THEN 'RECTIFICATION'::text WHEN ((t.reason)::text = 'B03'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'B04'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'BA1'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'BA2'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'E01'::text) THEN 'GAIN'::text WHEN ((t.reason)::text = 'E30'::text) THEN 'GAIN'::text WHEN ((t.reason)::text = 'BA9'::text) THEN 'LOSS'::text ELSE 'UNKNOWN'::text END), (COALESCE(i.meternumber, 'NA'::character varying)), i.registername, (CASE WHEN (((i.type)::text = 'A'::text) AND ((i.unit)::text = 'KW'::text)) THEN 'ACTIVE_POWER'::text WHEN (((i.type)::text = 'A'::text) AND ((i.unit)::text = 'KWH'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((i.type)::text = 'A'::text) AND ((i.unit)::text = 'MTQ'::text)) THEN 'ACTIVE_POWER'::text WHEN (((i.type)::text = 'B29'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.type)::text = 'BCW'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.type)::text = 'B30'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN (((i.type)::text = 'BCX'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN (((i.type)::text = 'B31'::text) AND ((i.unit)::text = 'D90'::text)) THEN 'NORMALIZED_VOLUME'::text WHEN (((i.type)::text = 'BCY'::text) AND ((i.unit)::text = 'KWH'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((i.type)::text = 'B31'::text) AND ((i.unit)::text = 'KWH'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((i.type)::text = 'B31'::text) AND ((i.unit)::text = 'MTQ'::text)) THEN 'VOLUME'::text WHEN (((i.type)::text = 'B47'::text) AND ((i.unit)::text = 'KW'::text)) THEN 'ACTIVE_POWER'::text WHEN (((i.type)::text = 'RC'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.type)::text = 'RI'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN ((i.unit)::text = 'MTQ'::text) THEN 'VOLUME'::text WHEN (((i.unit)::text = 'KRH'::text) AND ((i.registername)::text ~~* '%B29%'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.unit)::text = 'KRH'::text) AND ((i.registername)::text ~~* '%B30%'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN ((i.unit)::text = 'KRH'::text) THEN 'UNKNOWN'::text WHEN ((i.unit)::text = 'KW'::text) THEN 'ACTIVE_POWER'::text WHEN ((i.unit)::text = 'KWH'::text) THEN 'ACTIVE_ENERGY'::text WHEN ((i.unit IS NULL) OR ((i.unit)::text = ''::text)) THEN 'UNKNOWN'::text ELSE 'UNKNOWN'::text END), (CASE WHEN ((i.timeframe)::text = 'B36'::text) THEN 'NIGHT_EXCLUSIVE'::text WHEN ((i.timeframe)::text = ANY ('{E11,e11,J11}'::text[])) THEN 'HIGH'::text WHEN ((i.timeframe)::text = ANY ('{E10,e10,J10}'::text[])) THEN 'LOW'::text WHEN ((i.timeframe)::text = 'NPK'::text) THEN 'NON_PEAK'::text WHEN ((i.timeframe)::text = 'NPH'::text) THEN 'NON_PEAK_HIGH'::text WHEN ((i.timeframe)::text = 'NPL'::text) THEN 'NON_PEAK_LOW'::text WHEN ((i.timeframe)::text = 'E12'::text) THEN 'PEAK'::text WHEN ((i.timeframe)::text = 'PKH'::text) THEN 'PEAK_HIGH'::text WHEN ((i.timeframe)::text = 'PKL'::text) THEN 'PEAK_LOW'::text WHEN ((i.timeframe)::text = ANY ('{B37,b37}'::text[])) THEN 'TOTAL_HOUR'::text WHEN ((i.timeframe)::text = 'B48'::text) THEN 'NOT_USED'::text ELSE 'UNKNOWN'::text END), (CASE WHEN ((i.type)::text = ANY ('{BCW,BCX,BCY}'::text[])) THEN 'PRODUCTION'::text WHEN ((dp.direction)::text = 'I'::text) THEN 'PRODUCTION'::text ELSE 'CONSUMPTION'::text END), ((('{'::text || (i.index)::text) || '}'::text)), ((('{'::text || CASE WHEN ((i.quality)::text = 'estimated'::text) THEN 'ESTIMATED'::text WHEN ((i.quality)::text = 'ratified'::text) THEN 'VALIDATED'::text WHEN ((i.quality)::text = 'adjusted'::text) THEN 'ADJUSTED'::text WHEN ((i.quality)::text = 'revised'::text) THEN 'CORRECTED'::text WHEN ((i.quality)::text = 'unchanged'::text) THEN 'UNCHANGED'::text WHEN ((i.quality)::text = 'significant'::text) THEN 'OVERESTIMATED'::text WHEN ((i.quality)::text = 'for-agreement'::text) THEN 'VALID_PROVIDED_BY_THIRD_PARTY'::text ELSE 'MISSING'::text END) || '}'::text)), ('{UNDEFINED}'::character varying), (CASE WHEN ((t.msgandtxids IS NOT NULL) AND ((t.msgandtxids)::text !~~ '%dummy%'::text)) THEN "substring"((t.msgandtxids)::text, ("position"((t.msgandtxids)::text, '+'::text) + 1)) WHEN ((t.msgandtxids)::text ~~ '%dummy%'::text) THEN 'NA'::text WHEN (msg.interchangeid IS NOT NULL) THEN (msg.interchangeid)::text ELSE ('USER_IXD_'::text || (i.meteringid)::text) END), (CASE WHEN ((t.msgandtxids IS NOT NULL) AND ((t.msgandtxids)::text !~~ '%dummy%'::text)) THEN to_char(t.msgdate, 'YYYY-MM-DD HH24:MI:SS'::text) WHEN (msg.interchangeid IS NOT NULL) THEN to_char(msg.creationdate, 'YYYY-MM-DD HH24:MI:SS'::text) ELSE to_char(i.creationdate, 'YYYY-MM-DD HH24:MI:SS'::text) END), (CASE WHEN ((cdp.sourceid)::text = 'USER'::text) THEN 'USER'::text WHEN ((cdp.fromdate = cdp.todate) AND (NOT (alternatives: SubPlan 9 or hashed SubPlan 10))) THEN 'USER'::text WHEN ((m.source)::text = 'USER'::text) THEN 'USER'::text ELSE 'DGO'::text END), (CASE m.historical WHEN CASE_TEST_EXPR THEN 't'::text ELSE 'f'::text END), ('1'::character varying), (NULL::text), ((cdp.contractid)::character varying), (CASE m.ignored WHEN CASE_TEST_EXPR THEN 't'::text WHEN (NOT CASE_TEST_EXPR) THEN 'f'::text ELSE NULL::text END), ('$CUS'::character varying), ((cdp.contractdpid)::character varying)
3. 104.405 15,335.142 ↑ 1.4 100,904 1

Unique (cost=7,983,598.26..7,994,359.83 rows=138,859 width=229) (actual time=15,115.697..15,335.142 rows=100,904 loops=1)

4.          

CTE previousmet

5. 1.649 624.426 ↑ 1.0 1,848 1

HashAggregate (cost=26,269.45..26,287.94 rows=1,849 width=4) (actual time=623.984..624.426 rows=1,848 loops=1)

  • Group Key: i_3.meteringid
6. 110.605 622.777 ↓ 1.8 3,397 1

Merge Anti Join (cost=24,433.04..26,264.83 rows=1,849 width=4) (actual time=222.790..622.777 rows=3,397 loops=1)

  • Merge Cond: ((j.pointid = contractdpjournal.pointid) AND (i_3.previousindexdate = index.indexdate) AND (i_3.previousindex = index.index) AND ((i_3.timeframe)::text = (index.timeframe)::text) AND ((i_3.unit)::text = (index.unit)::text) AND ((i_3.type)::text = (index.type)::text))
7. 73.693 232.871 ↓ 2.3 63,889 1

Sort (cost=9,275.67..9,345.81 rows=28,056 width=29) (actual time=222.785..232.871 rows=63,889 loops=1)

  • Sort Key: j.pointid, i_3.previousindexdate, i_3.previousindex, i_3.timeframe, i_3.unit, i_3.type
  • Sort Method: quicksort Memory: 6,528kB
8. 36.751 159.178 ↓ 2.3 63,889 1

Hash Join (cost=2,997.49..7,202.89 rows=28,056 width=29) (actual time=33.468..159.178 rows=63,889 loops=1)

  • Hash Cond: (m_2.contractdpid = j.contractdpid)
9. 60.098 118.102 ↓ 2.3 63,889 1

Hash Join (cost=2,605.57..6,284.91 rows=28,056 width=29) (actual time=29.125..118.102 rows=63,889 loops=1)

  • Hash Cond: (i_3.meteringid = m_2.meteringid)
10. 28.908 28.908 ↓ 2.3 63,889 1

Seq Scan on index i_3 (cost=0.00..3,048.09 rows=28,056 width=25) (actual time=0.006..28.908 rows=63,889 loops=1)

  • Filter: (previousindexdate < indexdate)
  • Rows Removed by Filter: 20,278
11. 14.202 29.096 ↑ 1.0 68,114 1

Hash (cost=1,754.14..1,754.14 rows=68,114 width=8) (actual time=29.096..29.096 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,661kB
12. 14.894 14.894 ↑ 1.0 68,114 1

Seq Scan on metering m_2 (cost=0.00..1,754.14 rows=68,114 width=8) (actual time=0.004..14.894 rows=68,114 loops=1)

13. 2.196 4.325 ↑ 1.0 10,619 1

Hash (cost=259.19..259.19 rows=10,619 width=8) (actual time=4.325..4.325 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 415kB
14. 2.129 2.129 ↑ 1.0 10,619 1

Seq Scan on contractdpjournal j (cost=0.00..259.19 rows=10,619 width=8) (actual time=0.003..2.129 rows=10,619 loops=1)

15. 91.200 279.301 ↓ 1.0 76,642 1

Sort (cost=15,157.37..15,348.92 rows=76,617 width=25) (actual time=267.912..279.301 rows=76,642 loops=1)

  • Sort Key: contractdpjournal.pointid, index.indexdate, index.index, index.timeframe, index.unit, index.type
  • Sort Method: quicksort Memory: 9,020kB
16. 47.510 188.101 ↓ 1.0 76,650 1

Hash Join (cost=393.20..8,941.68 rows=76,617 width=25) (actual time=4.538..188.101 rows=76,650 loops=1)

  • Hash Cond: (metering.contractdpid = contractdpjournal.contractdpid)
17. 55.344 136.112 ↓ 1.0 76,650 1

Merge Join (cost=1.27..7,113.18 rows=76,617 width=25) (actual time=0.044..136.112 rows=76,650 loops=1)

  • Merge Cond: (metering.meteringid = index.meteringid)
18. 32.749 32.749 ↑ 1.0 68,114 1

Index Scan using idx_metering_contractdpid on metering (cost=0.29..2,284.00 rows=68,114 width=8) (actual time=0.018..32.749 rows=68,114 loops=1)

19. 48.019 48.019 ↓ 1.0 76,650 1

Index Scan using idx_index_meteringid on index (cost=0.29..3,701.22 rows=76,617 width=25) (actual time=0.014..48.019 rows=76,650 loops=1)

  • Filter: ((status)::text = 'A'::text)
  • Rows Removed by Filter: 7,517
20. 2.170 4.479 ↑ 1.0 10,619 1

Hash (cost=259.19..259.19 rows=10,619 width=8) (actual time=4.479..4.479 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 415kB
21. 2.309 2.309 ↑ 1.0 10,619 1

Seq Scan on contractdpjournal (cost=0.00..259.19 rows=10,619 width=8) (actual time=0.007..2.309 rows=10,619 loops=1)

22. 3,239.251 15,230.737 ↑ 1.4 100,914 1

Sort (cost=7,957,310.32..7,957,657.46 rows=138,859 width=229) (actual time=15,115.695..15,230.737 rows=100,914 loops=1)

  • Sort Key: ('$IXD'::character varying), dp.gsrn, (CASE WHEN (i.previousindexdate IS NULL) THEN i.indexdate WHEN (i.previousindexdate <= '9999-12-31'::date) THEN i.previousindexdate ELSE cdp.fromdate END), (CASE WHEN (i.indexdate <= '9999-12-31'::date) THEN i.indexdate ELSE cdp.todate END), s.gln, cdp.fromdate, cdp.todate, (CASE WHEN (((i.status)::text = 'R'::text) OR ((i.indexdate = i.previousindexdate) AND (alternatives: SubPlan 7 or hashed SubPlan 8))) THEN 'RECTIFIED'::text ELSE 'ACTUAL'::text END), (CASE WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'C'::text) THEN 'CONTINUOUS'::text WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'M'::text) THEN 'MONTH'::text WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'Y'::text) THEN 'YEAR'::text ELSE 'UNKNOWN'::text END), (CASE WHEN (((dp.marketcode)::text = 'EL'::text) AND ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'C'::text)) THEN 15 WHEN (((dp.marketcode)::text = 'NG'::text) AND ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'C'::text)) THEN 60 WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'M'::text) THEN 1 WHEN ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'Y'::text) THEN 1 ELSE 1 END), (CASE WHEN ((t.reason)::text = 'E23'::text) THEN 'PERIODIC_METERING'::text WHEN ((t.reason)::text = 'E53'::text) THEN 'METERING_ON_DEMAND'::text WHEN ((t.reason)::text = 'B95'::text) THEN 'AD_HOC'::text WHEN ((t.reason)::text = 'E24'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'E03'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'B74'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'B75'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E40'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'BB1'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'BB2'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E35'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'BC0'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E20'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E06'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'B90'::text) THEN 'LOSS'::text WHEN ((t.reason)::text = 'E21'::text) THEN 'CUSTOMER_SWITCH'::text WHEN ((t.reason)::text = 'E25'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'ERR'::text) THEN 'RECTIFICATION'::text WHEN ((t.reason)::text = 'B03'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'B04'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'BA1'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'BA2'::text) THEN 'MDCHANGE'::text WHEN ((t.reason)::text = 'E01'::text) THEN 'GAIN'::text WHEN ((t.reason)::text = 'E30'::text) THEN 'GAIN'::text WHEN ((t.reason)::text = 'BA9'::text) THEN 'LOSS'::text ELSE 'UNKNOWN'::text END), (COALESCE(i.meternumber, 'NA'::character varying)), i.registername, (CASE WHEN (((i.type)::text = 'A'::text) AND ((i.unit)::text = 'KW'::text)) THEN 'ACTIVE_POWER'::text WHEN (((i.type)::text = 'A'::text) AND ((i.unit)::text = 'KWH'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((i.type)::text = 'A'::text) AND ((i.unit)::text = 'MTQ'::text)) THEN 'ACTIVE_POWER'::text WHEN (((i.type)::text = 'B29'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.type)::text = 'BCW'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.type)::text = 'B30'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN (((i.type)::text = 'BCX'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN (((i.type)::text = 'B31'::text) AND ((i.unit)::text = 'D90'::text)) THEN 'NORMALIZED_VOLUME'::text WHEN (((i.type)::text = 'BCY'::text) AND ((i.unit)::text = 'KWH'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((i.type)::text = 'B31'::text) AND ((i.unit)::text = 'KWH'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((i.type)::text = 'B31'::text) AND ((i.unit)::text = 'MTQ'::text)) THEN 'VOLUME'::text WHEN (((i.type)::text = 'B47'::text) AND ((i.unit)::text = 'KW'::text)) THEN 'ACTIVE_POWER'::text WHEN (((i.type)::text = 'RC'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.type)::text = 'RI'::text) AND ((i.unit)::text = 'KRH'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN ((i.unit)::text = 'MTQ'::text) THEN 'VOLUME'::text WHEN (((i.unit)::text = 'KRH'::text) AND ((i.registername)::text ~~* '%B29%'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((i.unit)::text = 'KRH'::text) AND ((i.registername)::text ~~* '%B30%'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text WHEN ((i.unit)::text = 'KRH'::text) THEN 'UNKNOWN'::text WHEN ((i.unit)::text = 'KW'::text) THEN 'ACTIVE_POWER'::text WHEN ((i.unit)::text = 'KWH'::text) THEN 'ACTIVE_ENERGY'::text WHEN ((i.unit IS NULL) OR ((i.unit)::text = ''::text)) THEN 'UNKNOWN'::text ELSE 'UNKNOWN'::text END), (CASE WHEN ((i.timeframe)::text = 'B36'::text) THEN 'NIGHT_EXCLUSIVE'::text WHEN ((i.timeframe)::text = ANY ('{E11,e11,J11}'::text[])) THEN 'HIGH'::text WHEN ((i.timeframe)::text = ANY ('{E10,e10,J10}'::text[])) THEN 'LOW'::text WHEN ((i.timeframe)::text = 'NPK'::text) THEN 'NON_PEAK'::text WHEN ((i.timeframe)::text = 'NPH'::text) THEN 'NON_PEAK_HIGH'::text WHEN ((i.timeframe)::text = 'NPL'::text) THEN 'NON_PEAK_LOW'::text WHEN ((i.timeframe)::text = 'E12'::text) THEN 'PEAK'::text WHEN ((i.timeframe)::text = 'PKH'::text) THEN 'PEAK_HIGH'::text WHEN ((i.timeframe)::text = 'PKL'::text) THEN 'PEAK_LOW'::text WHEN ((i.timeframe)::text = ANY ('{B37,b37}'::text[])) THEN 'TOTAL_HOUR'::text WHEN ((i.timeframe)::text = 'B48'::text) THEN 'NOT_USED'::text ELSE 'UNKNOWN'::text END), (CASE WHEN ((i.type)::text = ANY ('{BCW,BCX,BCY}'::text[])) THEN 'PRODUCTION'::text WHEN ((dp.direction)::text = 'I'::text) THEN 'PRODUCTION'::text ELSE 'CONSUMPTION'::text END), ((('{'::text || (i.index)::text) || '}'::text)), ((('{'::text || CASE WHEN ((i.quality)::text = 'estimated'::text) THEN 'ESTIMATED'::text WHEN ((i.quality)::text = 'ratified'::text) THEN 'VALIDATED'::text WHEN ((i.quality)::text = 'adjusted'::text) THEN 'ADJUSTED'::text WHEN ((i.quality)::text = 'revised'::text) THEN 'CORRECTED'::text WHEN ((i.quality)::text = 'unchanged'::text) THEN 'UNCHANGED'::text WHEN ((i.quality)::text = 'significant'::text) THEN 'OVERESTIMATED'::text WHEN ((i.quality)::text = 'for-agreement'::text) THEN 'VALID_PROVIDED_BY_THIRD_PARTY'::text ELSE 'MISSING'::text END) || '}'::text)), ('{UNDEFINED}'::character varying), (CASE WHEN ((t.msgandtxids IS NOT NULL) AND ((t.msgandtxids)::text !~~ '%dummy%'::text)) THEN "substring"((t.msgandtxids)::text, ("position"((t.msgandtxids)::text, '+'::text) + 1)) WHEN ((t.msgandtxids)::text ~~ '%dummy%'::text) THEN 'NA'::text WHEN (msg.interchangeid IS NOT NULL) THEN (msg.interchangeid)::text ELSE ('USER_IXD_'::text || (i.meteringid)::text) END), ((i.meteringid)::character varying), (CASE WHEN ((cdp.sourceid)::text = 'USER'::text) THEN 'USER'::text WHEN ((cdp.fromdate = cdp.todate) AND (NOT (alternatives: SubPlan 9 or hashed SubPlan 10))) THEN 'USER'::text WHEN ((m.source)::text = 'USER'::text) THEN 'USER'::text ELSE 'DGO'::text END), (CASE m.historical WHEN CASE_TEST_EXPR THEN 't'::text ELSE 'f'::text END), ('1'::character varying), (NULL::text), (CASE WHEN ((t.msgandtxids IS NOT NULL) AND ((t.msgandtxids)::text !~~ '%dummy%'::text)) THEN to_char(t.msgdate, 'YYYY-MM-DD HH24:MI:SS'::text) WHEN (msg.interchangeid IS NOT NULL) THEN to_char(msg.creationdate, 'YYYY-MM-DD HH24:MI:SS'::text) ELSE to_char(i.creationdate, 'YYYY-MM-DD HH24:MI:SS'::text) END), (CASE m.ignored WHEN CASE_TEST_EXPR THEN 't'::text WHEN (NOT CASE_TEST_EXPR) THEN 'f'::text ELSE NULL::text END), ((cdp.contractid)::character varying), ('$CUS'::character varying), ((cdp.contractdpid)::character varying)
  • Sort Method: external merge Disk: 26,160kB
23. 27.651 11,991.486 ↑ 1.4 100,914 1

Append (cost=44,395.91..7,936,769.49 rows=138,859 width=229) (actual time=2,210.770..11,991.486 rows=100,914 loops=1)

24. 1,365.320 10,250.580 ↓ 1.0 84,173 1

Nested Loop Left Join (cost=44,395.91..6,779,774.78 rows=84,167 width=244) (actual time=2,210.768..10,250.580 rows=84,173 loops=1)

  • Join Filter: (i.previousindexdate = i.indexdate)
  • Rows Removed by Join Filter: 790
25. 2,986.977 7,792.409 ↑ 1.0 84,167 1

Nested Loop Left Join (cost=44,395.61..2,040,317.64 rows=84,167 width=248) (actual time=1,531.948..7,792.409 rows=84,167 loops=1)

  • Join Filter: (dpc.dpconfigjid IS NULL)
  • Rows Removed by Join Filter: 84,155
26. 2,649.610 4,637.098 ↑ 1.0 84,167 1

Nested Loop Left Join (cost=44,380.08..726,987.53 rows=84,167 width=250) (actual time=1,531.828..4,637.098 rows=84,167 loops=1)

27. 179.788 1,734.987 ↑ 1.0 84,167 1

Hash Join (cost=44,372.10..49,103.52 rows=84,167 width=244) (actual time=1,531.673..1,734.987 rows=84,167 loops=1)

  • Hash Cond: (i.meteringid = m.meteringid)
28. 23.567 23.567 ↑ 1.0 84,167 1

Seq Scan on index i (cost=0.00..2,837.67 rows=84,167 width=71) (actual time=0.007..23.567 rows=84,167 loops=1)

29. 70.074 1,531.632 ↑ 1.0 68,114 1

Hash (cost=43,520.67..43,520.67 rows=68,114 width=177) (actual time=1,531.632..1,531.632 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 11,385kB
30. 69.520 1,461.558 ↑ 1.0 68,114 1

Hash Left Join (cost=13,463.82..43,520.67 rows=68,114 width=177) (actual time=416.858..1,461.558 rows=68,114 loops=1)

  • Hash Cond: (t.envid = msg.envid)
31. 83.536 1,072.395 ↑ 1.0 68,114 1

Hash Join (cost=4,283.67..32,979.26 rows=68,114 width=164) (actual time=96.637..1,072.395 rows=68,114 loops=1)

  • Hash Cond: (m.contractdpid = cdp.contractdpid)
32. 534.869 932.665 ↑ 1.0 68,114 1

Hash Right Join (cost=2,605.57..30,024.02 rows=68,114 width=57) (actual time=40.423..932.665 rows=68,114 loops=1)

  • Hash Cond: (t.msgid = m.txref)
33. 358.537 358.537 ↑ 1.0 564,873 1

Seq Scan on transaction t (cost=0.00..19,731.73 rows=564,873 width=47) (actual time=0.004..358.537 rows=564,873 loops=1)

34. 17.794 39.259 ↑ 1.0 68,114 1

Hash (cost=1,754.14..1,754.14 rows=68,114 width=18) (actual time=39.259..39.259 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,438kB
35. 21.465 21.465 ↑ 1.0 68,114 1

Seq Scan on metering m (cost=0.00..1,754.14 rows=68,114 width=18) (actual time=0.006..21.465 rows=68,114 loops=1)

36. 6.575 56.194 ↑ 1.0 10,619 1

Hash (cost=1,545.37..1,545.37 rows=10,619 width=111) (actual time=56.194..56.194 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 1,036kB
37. 5.215 49.619 ↑ 1.0 10,619 1

Hash Left Join (cost=411.74..1,545.37 rows=10,619 width=111) (actual time=12.808..49.619 rows=10,619 loops=1)

  • Hash Cond: (contract.supplierid = s.supplierid)
38. 8.064 44.349 ↑ 1.0 10,619 1

Hash Join (cost=410.70..1,398.31 rows=10,619 width=83) (actual time=12.736..44.349 rows=10,619 loops=1)

  • Hash Cond: (cdp.contractid = contract.contractid)
39. 7.821 23.626 ↑ 1.0 10,619 1

Merge Join (cost=0.61..789.12 rows=10,619 width=79) (actual time=0.031..23.626 rows=10,619 loops=1)

  • Merge Cond: (dp.pointid = cdp.pointid)
40. 6.402 6.402 ↑ 1.0 8,487 1

Index Scan using deliverypoint_pkey on deliverypoint dp (cost=0.29..290.59 rows=8,487 width=27) (actual time=0.013..6.402 rows=8,487 loops=1)

41. 9.403 9.403 ↑ 1.0 10,619 1

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp (cost=0.29..344.57 rows=10,619 width=52) (actual time=0.009..9.403 rows=10,619 loops=1)

42. 3.316 12.659 ↑ 1.0 10,404 1

Hash (cost=280.04..280.04 rows=10,404 width=8) (actual time=12.659..12.659 rows=10,404 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 407kB
43. 9.343 9.343 ↑ 1.0 10,404 1

Seq Scan on contract (cost=0.00..280.04 rows=10,404 width=8) (actual time=0.064..9.343 rows=10,404 loops=1)

44. 0.002 0.055 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.055..0.055 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
45. 0.053 0.053 ↑ 1.0 2 1

Seq Scan on supplier s (cost=0.00..1.02 rows=2 width=36) (actual time=0.051..0.053 rows=2 loops=1)

46. 81.841 319.643 ↑ 1.0 194,940 1

Hash (cost=6,743.40..6,743.40 rows=194,940 width=21) (actual time=319.643..319.643 rows=194,940 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 10,849kB
47. 237.802 237.802 ↑ 1.0 194,940 1

Seq Scan on msgenv msg (cost=0.00..6,743.40 rows=194,940 width=21) (actual time=0.050..237.802 rows=194,940 loops=1)

48. 0.000 252.501 ↑ 1.0 1 84,167

Index Scan using dpj_un on dpconfigjournal dpc (cost=7.98..8.04 rows=1 width=6) (actual time=0.002..0.003 rows=1 loops=84,167)

  • Index Cond: (dpconfigjid = (SubPlan 11))
49.          

SubPlan (for Index Scan)

50. 84.167 2,525.010 ↑ 1.0 1 84,167

Limit (cost=7.69..7.70 rows=1 width=26) (actual time=0.030..0.030 rows=1 loops=84,167)

51. 420.835 2,440.843 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.029..0.029 rows=1 loops=84,167)

  • Sort Key: ((tx_2.msgdate)::date), ((j_3.creationdate)::date), j_3.fromdate, j_3.powerlimiter, m_5.budgetmeter
  • Sort Method: top-N heapsort Memory: 25kB
52. 152.950 2,020.008 ↓ 2.0 2 84,167

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.013..0.024 rows=2 loops=84,167)

53. 147.822 1,346.672 ↓ 2.0 2 84,167

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.009..0.016 rows=2 loops=84,167)

54. 505.002 505.002 ↓ 2.0 2 84,167

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_3 (cost=0.29..2.91 rows=1 width=25) (actual time=0.004..0.006 rows=2 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((fromdate <= i.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
55. 693.848 693.848 ↑ 1.0 1 173,462

Index Scan using idx_transaction_msgid_pk on transaction tx_2 (cost=0.42..2.44 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=173,462)

  • Index Cond: (msgid = j_3.txref)
56. 520.386 520.386 ↑ 1.0 1 173,462

Index Scan using idx_meter_physicalconfigid_pk on meter m_5 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=173,462)

  • Index Cond: (j_3.physicalconfigid = physicalconfigid)
57. 84.167 2,525.010 ↑ 1.0 1 84,167

Limit (cost=7.69..7.70 rows=1 width=26) (actual time=0.030..0.030 rows=1 loops=84,167)

58. 420.835 2,440.843 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.029..0.029 rows=1 loops=84,167)

  • Sort Key: ((tx_2.msgdate)::date), ((j_3.creationdate)::date), j_3.fromdate, j_3.powerlimiter, m_5.budgetmeter
  • Sort Method: top-N heapsort Memory: 25kB
59. 152.950 2,020.008 ↓ 2.0 2 84,167

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.013..0.024 rows=2 loops=84,167)

60. 147.822 1,346.672 ↓ 2.0 2 84,167

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.009..0.016 rows=2 loops=84,167)

61. 505.002 505.002 ↓ 2.0 2 84,167

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_3 (cost=0.29..2.91 rows=1 width=25) (actual time=0.004..0.006 rows=2 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((fromdate <= i.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
62. 693.848 693.848 ↑ 1.0 1 173,462

Index Scan using idx_transaction_msgid_pk on transaction tx_2 (cost=0.42..2.44 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=173,462)

  • Index Cond: (msgid = j_3.txref)
63. 520.386 520.386 ↑ 1.0 1 173,462

Index Scan using idx_meter_physicalconfigid_pk on meter m_5 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=173,462)

  • Index Cond: (j_3.physicalconfigid = physicalconfigid)
64. 0.000 168.334 ↑ 1.0 1 84,167

Index Scan using dpj_un on dpconfigjournal dpc2 (cost=15.53..15.59 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=84,167)

  • Index Cond: (dpconfigjid = (SubPlan 13))
65.          

SubPlan (for Index Scan)

66. 0.000 2,777.511 ↑ 1.0 1 84,167

Limit (cost=15.24..15.25 rows=1 width=26) (actual time=0.033..0.033 rows=1 loops=84,167)

67.          

Initplan (for Limit)

68. 84.167 1,178.338 ↑ 1.0 1 84,167

Aggregate (cost=7.54..7.55 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=84,167)

69. 281.565 1,094.171 ↓ 1.5 3 84,167

Nested Loop Left Join (cost=0.57..7.53 rows=2 width=4) (actual time=0.005..0.013 rows=3 loops=84,167)

70. 336.668 336.668 ↓ 1.5 3 84,167

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j1_1 (cost=0.29..2.91 rows=2 width=12) (actual time=0.002..0.004 rows=3 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((source)::text = 'DGO'::text)
71. 475.938 475.938 ↑ 1.0 1 237,969

Index Only Scan using idx_meter_physicalconfigid_pk on meter m1_1 (cost=0.29..2.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=237,969)

  • Index Cond: (physicalconfigid = j1_1.physicalconfigid)
  • Heap Fetches: 265,022
72. 252.501 2,693.344 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.032..0.032 rows=1 loops=84,167)

  • Sort Key: ((tx_3.msgdate)::date), ((j_4.creationdate)::date), j_4.powerlimiter, m_6.budgetmeter
  • Sort Method: quicksort Memory: 25kB
73. 218.565 2,440.843 ↑ 1.0 1 84,167

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.025..0.029 rows=1 loops=84,167)

74. 33.263 2,020.008 ↑ 1.0 1 84,167

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.021..0.024 rows=1 loops=84,167)

75. 1,683.340 1,683.340 ↑ 1.0 1 84,167

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_4 (cost=0.29..2.91 rows=1 width=25) (actual time=0.018..0.020 rows=1 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $36))
  • Rows Removed by Filter: 2
76. 303.405 303.405 ↑ 1.0 1 101,135

Index Scan using idx_transaction_msgid_pk on transaction tx_3 (cost=0.42..2.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=101,135)

  • Index Cond: (msgid = j_4.txref)
77. 202.270 202.270 ↑ 1.0 1 101,135

Index Scan using idx_meter_physicalconfigid_pk on meter m_6 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=101,135)

  • Index Cond: (j_4.physicalconfigid = physicalconfigid)
78. 0.000 2,777.511 ↑ 1.0 1 84,167

Limit (cost=15.24..15.25 rows=1 width=26) (actual time=0.033..0.033 rows=1 loops=84,167)

79.          

Initplan (for Limit)

80. 84.167 1,178.338 ↑ 1.0 1 84,167

Aggregate (cost=7.54..7.55 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=84,167)

81. 281.565 1,094.171 ↓ 1.5 3 84,167

Nested Loop Left Join (cost=0.57..7.53 rows=2 width=4) (actual time=0.005..0.013 rows=3 loops=84,167)

82. 336.668 336.668 ↓ 1.5 3 84,167

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j1_1 (cost=0.29..2.91 rows=2 width=12) (actual time=0.002..0.004 rows=3 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((source)::text = 'DGO'::text)
83. 475.938 475.938 ↑ 1.0 1 237,969

Index Only Scan using idx_meter_physicalconfigid_pk on meter m1_1 (cost=0.29..2.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=237,969)

  • Index Cond: (physicalconfigid = j1_1.physicalconfigid)
  • Heap Fetches: 265,022
84. 252.501 2,693.344 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.032..0.032 rows=1 loops=84,167)

  • Sort Key: ((tx_3.msgdate)::date), ((j_4.creationdate)::date), j_4.powerlimiter, m_6.budgetmeter
  • Sort Method: quicksort Memory: 25kB
85. 218.565 2,440.843 ↑ 1.0 1 84,167

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.025..0.029 rows=1 loops=84,167)

86. 33.263 2,020.008 ↑ 1.0 1 84,167

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.021..0.024 rows=1 loops=84,167)

87. 1,683.340 1,683.340 ↑ 1.0 1 84,167

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_4 (cost=0.29..2.91 rows=1 width=25) (actual time=0.018..0.020 rows=1 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $36))
  • Rows Removed by Filter: 2
88. 303.405 303.405 ↑ 1.0 1 101,135

Index Scan using idx_transaction_msgid_pk on transaction tx_3 (cost=0.42..2.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=101,135)

  • Index Cond: (msgid = j_4.txref)
89. 202.270 202.270 ↑ 1.0 1 101,135

Index Scan using idx_meter_physicalconfigid_pk on meter m_6 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=101,135)

  • Index Cond: (j_4.physicalconfigid = physicalconfigid)
90. 336.668 336.668 ↓ 0.0 0 84,167

Index Scan using idx_index_meteringid on index i2 (cost=0.29..0.37 rows=1 width=19) (actual time=0.004..0.004 rows=0 loops=84,167)

  • Index Cond: (i.meteringid = meteringid)
  • Filter: ((i.meterid <> meterid) AND ((i.timeframe)::text = (timeframe)::text) AND ((i.unit)::text = (unit)::text) AND ((i.type)::text = (type)::text))
  • Rows Removed by Filter: 2
91.          

SubPlan (for Nested Loop Left Join)

92. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=50.07..53.70 rows=1 width=0) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=49.78..52.54 rows=3 width=8) (never executed)

  • Hash Cond: (previousmet_1.meteringid = m3.meteringid)
94. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=41.60..43.60 rows=200 width=4) (never executed)

  • Group Key: previousmet_1.meteringid
95. 0.000 0.000 ↓ 0.0 0

CTE Scan on previousmet previousmet_1 (cost=0.00..36.98 rows=1,849 width=4) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.10..8.10 rows=6 width=4) (never executed)

97. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..8.10 rows=6 width=4) (never executed)

98. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal j3 (cost=0.29..2.30 rows=1 width=4) (never executed)

  • Index Cond: (pointid = cdp.pointid)
99. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_metering_contractdpid_2 on metering m3 (cost=0.29..5.73 rows=7 width=8) (never executed)

  • Index Cond: (contractdpid = j3.contractdpid)
100. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_index_meteringid on index i3 (cost=0.29..0.38 rows=1 width=4) (never executed)

  • Index Cond: (meteringid = m3.meteringid)
  • Filter: ((previousindexdate = i.indexdate) AND ((status)::text = 'A'::text))
101. 1.789 677.174 ↑ 13.4 2,863 1

Hash Join (cost=3,039.39..4,857.13 rows=38,308 width=8) (actual time=663.750..677.174 rows=2,863 loops=1)

  • Hash Cond: (m3_1.contractdpid = j3_1.contractdpid)
102. 2.600 670.761 ↑ 13.4 2,863 1

Hash Join (cost=2,647.46..3,746.93 rows=38,308 width=8) (actual time=659.086..670.761 rows=2,863 loops=1)

  • Hash Cond: (i3_1.meteringid = m3_1.meteringid)
103. 1.253 634.740 ↑ 13.4 2,863 1

Nested Loop (cost=41.90..279.43 rows=38,308 width=12) (actual time=625.638..634.740 rows=2,863 loops=1)

104. 1.098 626.095 ↓ 9.2 1,848 1

HashAggregate (cost=41.60..43.60 rows=200 width=4) (actual time=625.609..626.095 rows=1,848 loops=1)

  • Group Key: previousmet_2.meteringid
105. 624.997 624.997 ↑ 1.0 1,848 1

CTE Scan on previousmet previousmet_2 (cost=0.00..36.98 rows=1,849 width=4) (actual time=623.988..624.997 rows=1,848 loops=1)

106. 7.392 7.392 ↑ 1.0 2 1,848

Index Scan using idx_index_meteringid on index i3_1 (cost=0.29..1.16 rows=2 width=8) (actual time=0.003..0.004 rows=2 loops=1,848)

  • Index Cond: (meteringid = previousmet_2.meteringid)
  • Filter: ((status)::text = 'A'::text)
  • Rows Removed by Filter: 1
107. 17.813 33.421 ↑ 1.0 68,114 1

Hash (cost=1,754.14..1,754.14 rows=68,114 width=8) (actual time=33.421..33.421 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,661kB
108. 15.608 15.608 ↑ 1.0 68,114 1

Seq Scan on metering m3_1 (cost=0.00..1,754.14 rows=68,114 width=8) (actual time=0.006..15.608 rows=68,114 loops=1)

109. 2.262 4.624 ↑ 1.0 10,619 1

Hash (cost=259.19..259.19 rows=10,619 width=8) (actual time=4.624..4.624 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 415kB
110. 2.362 2.362 ↑ 1.0 10,619 1

Seq Scan on contractdpjournal j3_1 (cost=0.00..259.19 rows=10,619 width=8) (actual time=0.008..2.362 rows=10,619 loops=1)

111. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..8.64 rows=6 width=0) (never executed)

112. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_5 (cost=0.29..2.30 rows=1 width=4) (never executed)

  • Index Cond: (pointid = dp.pointid)
113. 0.000 0.000 ↓ 0.0 0

Index Only Scan using process_idx4test on process p_2 (cost=0.29..6.27 rows=7 width=4) (never executed)

  • Index Cond: (contractdpid = cdp_5.contractdpid)
  • Heap Fetches: 0
114. 41.733 79.009 ↑ 1.0 61,691 1

Hash Join (cost=391.93..3,204.54 rows=61,691 width=4) (actual time=5.349..79.009 rows=61,691 loops=1)

  • Hash Cond: (p_3.contractdpid = cdp_6.contractdpid)
115. 31.971 31.971 ↑ 1.0 61,691 1

Seq Scan on process p_3 (cost=0.00..1,655.91 rows=61,691 width=4) (actual time=0.031..31.971 rows=61,691 loops=1)

116. 2.854 5.305 ↑ 1.0 10,619 1

Hash (cost=259.19..259.19 rows=10,619 width=8) (actual time=5.305..5.305 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 415kB
117. 2.451 2.451 ↑ 1.0 10,619 1

Seq Scan on contractdpjournal cdp_6 (cost=0.00..259.19 rows=10,619 width=8) (actual time=0.005..2.451 rows=10,619 loops=1)

118. 2.616 1,562.238 ↑ 10.6 3,973 1

Subquery Scan on *SELECT* 2 (cost=44,442.01..1,152,825.30 rows=41,988 width=236) (actual time=902.895..1,562.238 rows=3,973 loops=1)

119. 90.665 1,559.622 ↑ 10.6 3,973 1

Nested Loop Left Join (cost=44,442.01..1,152,405.42 rows=41,988 width=236) (actual time=902.893..1,559.622 rows=3,973 loops=1)

  • Join Filter: (i_1.previousindexdate = i_1.indexdate)
  • Rows Removed by Join Filter: 50
120. 167.834 1,398.858 ↑ 10.6 3,973 1

Nested Loop Left Join (cost=44,441.72..1,042,810.43 rows=41,988 width=244) (actual time=902.753..1,398.858 rows=3,973 loops=1)

  • Join Filter: (dpc_1.dpconfigjid IS NULL)
  • Rows Removed by Join Filter: 3,967
121. 138.848 1,223.078 ↑ 10.6 3,973 1

Nested Loop Left Join (cost=44,426.18..387,635.55 rows=41,988 width=246) (actual time=902.641..1,223.078 rows=3,973 loops=1)

122. 21.059 1,072.311 ↑ 10.6 3,973 1

Hash Join (cost=44,418.20..49,462.71 rows=41,988 width=240) (actual time=902.533..1,072.311 rows=3,973 loops=1)

  • Hash Cond: (m_1.meteringid = previousmet.meteringid)
123. 119.251 1,049.966 ↓ 1.0 84,080 1

Hash Join (cost=44,372.10..49,099.22 rows=83,976 width=244) (actual time=901.226..1,049.966 rows=84,080 loops=1)

  • Hash Cond: (i_1.meteringid = m_1.meteringid)
124. 29.518 29.518 ↓ 1.0 84,080 1

Seq Scan on index i_1 (cost=0.00..2,837.67 rows=83,976 width=71) (actual time=0.006..29.518 rows=84,080 loops=1)

  • Filter: ((previousindexdate IS NOT NULL) AND (previousindex IS NOT NULL))
  • Rows Removed by Filter: 87
125. 54.420 901.197 ↑ 1.0 68,114 1

Hash (cost=43,520.67..43,520.67 rows=68,114 width=173) (actual time=901.197..901.197 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 11,168kB
126. 57.849 846.777 ↑ 1.0 68,114 1

Hash Left Join (cost=13,463.82..43,520.67 rows=68,114 width=173) (actual time=178.556..846.777 rows=68,114 loops=1)

  • Hash Cond: (t_1.envid = msg_1.envid)
127. 65.717 680.931 ↑ 1.0 68,114 1

Hash Join (cost=4,283.67..32,979.26 rows=68,114 width=160) (actual time=70.507..680.931 rows=68,114 loops=1)

  • Hash Cond: (m_1.contractdpid = cdp_1.contractdpid)
128. 439.169 579.243 ↑ 1.0 68,114 1

Hash Right Join (cost=2,605.57..30,024.02 rows=68,114 width=57) (actual time=34.509..579.243 rows=68,114 loops=1)

  • Hash Cond: (t_1.msgid = m_1.txref)
129. 106.356 106.356 ↑ 1.0 564,873 1

Seq Scan on transaction t_1 (cost=0.00..19,731.73 rows=564,873 width=47) (actual time=0.005..106.356 rows=564,873 loops=1)

130. 14.957 33.718 ↑ 1.0 68,114 1

Hash (cost=1,754.14..1,754.14 rows=68,114 width=18) (actual time=33.718..33.718 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,438kB
131. 18.761 18.761 ↑ 1.0 68,114 1

Seq Scan on metering m_1 (cost=0.00..1,754.14 rows=68,114 width=18) (actual time=0.006..18.761 rows=68,114 loops=1)

132. 4.556 35.971 ↑ 1.0 10,619 1

Hash (cost=1,545.37..1,545.37 rows=10,619 width=107) (actual time=35.971..35.971 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 994kB
133. 4.187 31.415 ↑ 1.0 10,619 1

Hash Left Join (cost=411.74..1,545.37 rows=10,619 width=107) (actual time=4.035..31.415 rows=10,619 loops=1)

  • Hash Cond: (contract_1.supplierid = s_1.supplierid)
134. 5.476 27.224 ↑ 1.0 10,619 1

Hash Join (cost=410.70..1,398.31 rows=10,619 width=79) (actual time=4.024..27.224 rows=10,619 loops=1)

  • Hash Cond: (cdp_1.contractid = contract_1.contractid)
135. 6.165 17.764 ↑ 1.0 10,619 1

Merge Join (cost=0.61..789.12 rows=10,619 width=75) (actual time=0.030..17.764 rows=10,619 loops=1)

  • Merge Cond: (dp_1.pointid = cdp_1.pointid)
136. 4.648 4.648 ↑ 1.0 8,487 1

Index Scan using deliverypoint_pkey on deliverypoint dp_1 (cost=0.29..290.59 rows=8,487 width=27) (actual time=0.012..4.648 rows=8,487 loops=1)

137. 6.951 6.951 ↑ 1.0 10,619 1

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_1 (cost=0.29..344.57 rows=10,619 width=52) (actual time=0.010..6.951 rows=10,619 loops=1)

138. 1.824 3.984 ↑ 1.0 10,404 1

Hash (cost=280.04..280.04 rows=10,404 width=8) (actual time=3.984..3.984 rows=10,404 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 407kB
139. 2.160 2.160 ↑ 1.0 10,404 1

Seq Scan on contract contract_1 (cost=0.00..280.04 rows=10,404 width=8) (actual time=0.006..2.160 rows=10,404 loops=1)

140. 0.001 0.004 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.004..0.004 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
141. 0.003 0.003 ↑ 1.0 2 1

Seq Scan on supplier s_1 (cost=0.00..1.02 rows=2 width=36) (actual time=0.002..0.003 rows=2 loops=1)

142. 44.400 107.997 ↑ 1.0 194,940 1

Hash (cost=6,743.40..6,743.40 rows=194,940 width=21) (actual time=107.997..107.997 rows=194,940 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 10,849kB
143. 63.597 63.597 ↑ 1.0 194,940 1

Seq Scan on msgenv msg_1 (cost=0.00..6,743.40 rows=194,940 width=21) (actual time=0.004..63.597 rows=194,940 loops=1)

144. 0.312 1.286 ↓ 9.2 1,848 1

Hash (cost=43.60..43.60 rows=200 width=4) (actual time=1.286..1.286 rows=1,848 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 65kB
145. 0.746 0.974 ↓ 9.2 1,848 1

HashAggregate (cost=41.60..43.60 rows=200 width=4) (actual time=0.734..0.974 rows=1,848 loops=1)

  • Group Key: previousmet.meteringid
146. 0.228 0.228 ↑ 1.0 1,848 1

CTE Scan on previousmet (cost=0.00..36.98 rows=1,849 width=4) (actual time=0.002..0.228 rows=1,848 loops=1)

147. 0.000 11.919 ↑ 1.0 1 3,973

Index Scan using dpj_un on dpconfigjournal dpc_1 (cost=7.98..8.04 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=3,973)

  • Index Cond: (dpconfigjid = (SubPlan 4))
148.          

SubPlan (for Index Scan)

149. 3.973 131.109 ↑ 1.0 1 3,973

Limit (cost=7.69..7.70 rows=1 width=26) (actual time=0.033..0.033 rows=1 loops=3,973)

150. 19.865 127.136 ↑ 1.0 1 3,973

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.032..0.032 rows=1 loops=3,973)

  • Sort Key: ((tx.msgdate)::date), ((j_1.creationdate)::date), j_1.fromdate, j_1.powerlimiter, m_3.budgetmeter
  • Sort Method: top-N heapsort Memory: 25kB
151. 13.513 107.271 ↓ 2.0 2 3,973

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.013..0.027 rows=2 loops=3,973)

152. 4.774 67.541 ↓ 2.0 2 3,973

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.009..0.017 rows=2 loops=3,973)

153. 27.811 27.811 ↓ 2.0 2 3,973

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_1 (cost=0.29..2.91 rows=1 width=25) (actual time=0.004..0.007 rows=2 loops=3,973)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((fromdate <= i_1.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 2
154. 34.956 34.956 ↑ 1.0 1 8,739

Index Scan using idx_transaction_msgid_pk on transaction tx (cost=0.42..2.44 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=8,739)

  • Index Cond: (msgid = j_1.txref)
155. 26.217 26.217 ↑ 1.0 1 8,739

Index Scan using idx_meter_physicalconfigid_pk on meter m_3 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=8,739)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
156. 3.973 131.109 ↑ 1.0 1 3,973

Limit (cost=7.69..7.70 rows=1 width=26) (actual time=0.033..0.033 rows=1 loops=3,973)

157. 19.865 127.136 ↑ 1.0 1 3,973

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.032..0.032 rows=1 loops=3,973)

  • Sort Key: ((tx.msgdate)::date), ((j_1.creationdate)::date), j_1.fromdate, j_1.powerlimiter, m_3.budgetmeter
  • Sort Method: top-N heapsort Memory: 25kB
158. 13.513 107.271 ↓ 2.0 2 3,973

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.013..0.027 rows=2 loops=3,973)

159. 4.774 67.541 ↓ 2.0 2 3,973

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.009..0.017 rows=2 loops=3,973)

160. 27.811 27.811 ↓ 2.0 2 3,973

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_1 (cost=0.29..2.91 rows=1 width=25) (actual time=0.004..0.007 rows=2 loops=3,973)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((fromdate <= i_1.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 2
161. 34.956 34.956 ↑ 1.0 1 8,739

Index Scan using idx_transaction_msgid_pk on transaction tx (cost=0.42..2.44 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=8,739)

  • Index Cond: (msgid = j_1.txref)
162. 26.217 26.217 ↑ 1.0 1 8,739

Index Scan using idx_meter_physicalconfigid_pk on meter m_3 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=8,739)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
163. 0.000 7.946 ↑ 1.0 1 3,973

Index Scan using dpj_un on dpconfigjournal dpc2_1 (cost=15.53..15.59 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=3,973)

  • Index Cond: (dpconfigjid = (SubPlan 6))
164.          

SubPlan (for Index Scan)

165. 0.000 158.920 ↑ 1.0 1 3,973

Limit (cost=15.24..15.25 rows=1 width=26) (actual time=0.040..0.040 rows=1 loops=3,973)

166.          

Initplan (for Limit)

167. 7.946 75.487 ↑ 1.0 1 3,973

Aggregate (cost=7.54..7.55 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=3,973)

168. 1.557 67.541 ↓ 2.0 4 3,973

Nested Loop Left Join (cost=0.57..7.53 rows=2 width=4) (actual time=0.006..0.017 rows=4 loops=3,973)

169. 19.865 19.865 ↓ 2.0 4 3,973

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j1 (cost=0.29..2.91 rows=2 width=12) (actual time=0.002..0.005 rows=4 loops=3,973)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((source)::text = 'DGO'::text)
170. 46.119 46.119 ↑ 1.0 1 15,373

Index Only Scan using idx_meter_physicalconfigid_pk on meter m1 (cost=0.29..2.30 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=15,373)

  • Index Cond: (physicalconfigid = j1.physicalconfigid)
  • Heap Fetches: 17,093
171. 15.892 154.947 ↑ 1.0 1 3,973

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.039..0.039 rows=1 loops=3,973)

  • Sort Key: ((tx_1.msgdate)::date), ((j_2.creationdate)::date), j_2.powerlimiter, m_4.budgetmeter
  • Sort Method: quicksort Memory: 25kB
172. 10.081 139.055 ↑ 1.0 1 3,973

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.030..0.035 rows=1 loops=3,973)

173. 5.189 119.190 ↑ 1.0 1 3,973

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.027..0.030 rows=1 loops=3,973)

174. 99.325 99.325 ↑ 1.0 1 3,973

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_2 (cost=0.29..2.91 rows=1 width=25) (actual time=0.023..0.025 rows=1 loops=3,973)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $10))
  • Rows Removed by Filter: 3
175. 14.676 14.676 ↑ 1.0 1 4,892

Index Scan using idx_transaction_msgid_pk on transaction tx_1 (cost=0.42..2.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4,892)

  • Index Cond: (msgid = j_2.txref)
176. 9.784 9.784 ↑ 1.0 1 4,892

Index Scan using idx_meter_physicalconfigid_pk on meter m_4 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=4,892)

  • Index Cond: (j_2.physicalconfigid = physicalconfigid)
177. 0.000 158.920 ↑ 1.0 1 3,973

Limit (cost=15.24..15.25 rows=1 width=26) (actual time=0.040..0.040 rows=1 loops=3,973)

178.          

Initplan (for Limit)

179. 7.946 75.487 ↑ 1.0 1 3,973

Aggregate (cost=7.54..7.55 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=3,973)

180. 1.557 67.541 ↓ 2.0 4 3,973

Nested Loop Left Join (cost=0.57..7.53 rows=2 width=4) (actual time=0.006..0.017 rows=4 loops=3,973)

181. 19.865 19.865 ↓ 2.0 4 3,973

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j1 (cost=0.29..2.91 rows=2 width=12) (actual time=0.002..0.005 rows=4 loops=3,973)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((source)::text = 'DGO'::text)
182. 46.119 46.119 ↑ 1.0 1 15,373

Index Only Scan using idx_meter_physicalconfigid_pk on meter m1 (cost=0.29..2.30 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=15,373)

  • Index Cond: (physicalconfigid = j1.physicalconfigid)
  • Heap Fetches: 17,093
183. 15.892 154.947 ↑ 1.0 1 3,973

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.039..0.039 rows=1 loops=3,973)

  • Sort Key: ((tx_1.msgdate)::date), ((j_2.creationdate)::date), j_2.powerlimiter, m_4.budgetmeter
  • Sort Method: quicksort Memory: 25kB
184. 10.081 139.055 ↑ 1.0 1 3,973

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.030..0.035 rows=1 loops=3,973)

185. 5.189 119.190 ↑ 1.0 1 3,973

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.027..0.030 rows=1 loops=3,973)

186. 99.325 99.325 ↑ 1.0 1 3,973

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_2 (cost=0.29..2.91 rows=1 width=25) (actual time=0.023..0.025 rows=1 loops=3,973)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $10))
  • Rows Removed by Filter: 3
187. 14.676 14.676 ↑ 1.0 1 4,892

Index Scan using idx_transaction_msgid_pk on transaction tx_1 (cost=0.42..2.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4,892)

  • Index Cond: (msgid = j_2.txref)
188. 9.784 9.784 ↑ 1.0 1 4,892

Index Scan using idx_meter_physicalconfigid_pk on meter m_4 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=4,892)

  • Index Cond: (j_2.physicalconfigid = physicalconfigid)
189. 19.865 19.865 ↓ 0.0 0 3,973

Index Scan using idx_index_meteringid on index i2_1 (cost=0.29..0.37 rows=1 width=19) (actual time=0.005..0.005 rows=0 loops=3,973)

  • Index Cond: (i_1.meteringid = meteringid)
  • Filter: ((i_1.meterid <> meterid) AND ((i_1.timeframe)::text = (timeframe)::text) AND ((i_1.unit)::text = (unit)::text) AND ((i_1.type)::text = (type)::text))
  • Rows Removed by Filter: 3
190.          

SubPlan (for Nested Loop Left Join)

191. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..8.64 rows=6 width=0) (never executed)

192. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_3 (cost=0.29..2.30 rows=1 width=4) (never executed)

  • Index Cond: (pointid = dp_1.pointid)
193. 0.000 0.000 ↓ 0.0 0

Index Only Scan using process_idx4test on process p (cost=0.29..6.27 rows=7 width=4) (never executed)

  • Index Cond: (contractdpid = cdp_3.contractdpid)
  • Heap Fetches: 0
194. 34.215 50.234 ↑ 1.0 61,691 1

Hash Join (cost=391.93..3,204.54 rows=61,691 width=4) (actual time=6.045..50.234 rows=61,691 loops=1)

  • Hash Cond: (p_1.contractdpid = cdp_4.contractdpid)
195. 10.003 10.003 ↑ 1.0 61,691 1

Seq Scan on process p_1 (cost=0.00..1,655.91 rows=61,691 width=4) (actual time=0.006..10.003 rows=61,691 loops=1)

196. 3.407 6.016 ↑ 1.0 10,619 1

Hash (cost=259.19..259.19 rows=10,619 width=8) (actual time=6.016..6.016 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 415kB
197. 2.609 2.609 ↑ 1.0 10,619 1

Seq Scan on contractdpjournal cdp_4 (cost=0.00..259.19 rows=10,619 width=8) (actual time=0.006..2.609 rows=10,619 loops=1)

198. 5.695 151.017 ↓ 1.0 12,768 1

Subquery Scan on *SELECT* 3 (cost=1,678.11..3,327.75 rows=12,704 width=106) (actual time=47.859..151.017 rows=12,768 loops=1)

199. 89.981 145.322 ↓ 1.0 12,768 1

Hash Join (cost=1,678.11..3,200.71 rows=12,704 width=106) (actual time=47.857..145.322 rows=12,768 loops=1)

  • Hash Cond: (i_2.contractdpid = cdp_2.contractdpid)
200. 7.653 7.653 ↓ 1.0 12,768 1

Seq Scan on userindex i_2 (cost=0.00..331.60 rows=12,704 width=42) (actual time=0.034..7.653 rows=12,768 loops=1)

  • Filter: ((indexdate IS NULL) OR (indexdate <= '9999-12-31 00:00:00+01'::timestamp with time zone))
201. 6.022 47.688 ↑ 1.0 10,619 1

Hash (cost=1,545.37..1,545.37 rows=10,619 width=68) (actual time=47.688..47.688 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 839kB
202. 5.185 41.666 ↑ 1.0 10,619 1

Hash Left Join (cost=411.74..1,545.37 rows=10,619 width=68) (actual time=5.908..41.666 rows=10,619 loops=1)

  • Hash Cond: (contract_2.supplierid = s_2.supplierid)
203. 8.320 36.475 ↑ 1.0 10,619 1

Hash Join (cost=410.70..1,398.31 rows=10,619 width=40) (actual time=5.893..36.475 rows=10,619 loops=1)

  • Hash Cond: (cdp_2.contractid = contract_2.contractid)
204. 7.630 22.310 ↑ 1.0 10,619 1

Merge Join (cost=0.61..789.12 rows=10,619 width=36) (actual time=0.037..22.310 rows=10,619 loops=1)

  • Merge Cond: (dp_2.pointid = cdp_2.pointid)
205. 5.945 5.945 ↑ 1.0 8,487 1

Index Scan using deliverypoint_pkey on deliverypoint dp_2 (cost=0.29..290.59 rows=8,487 width=24) (actual time=0.013..5.945 rows=8,487 loops=1)

206. 8.735 8.735 ↑ 1.0 10,619 1

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_2 (cost=0.29..344.57 rows=10,619 width=20) (actual time=0.016..8.735 rows=10,619 loops=1)

207. 3.075 5.845 ↑ 1.0 10,404 1

Hash (cost=280.04..280.04 rows=10,404 width=8) (actual time=5.845..5.845 rows=10,404 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 407kB
208. 2.770 2.770 ↑ 1.0 10,404 1

Seq Scan on contract contract_2 (cost=0.00..280.04 rows=10,404 width=8) (actual time=0.006..2.770 rows=10,404 loops=1)

209. 0.003 0.006 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
210. 0.003 0.003 ↑ 1.0 2 1

Seq Scan on supplier s_2 (cost=0.00..1.02 rows=2 width=36) (actual time=0.003..0.003 rows=2 loops=1)

Planning time : 34.258 ms
Execution time : 17,904.426 ms