explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HTa8

Settings
# exclusive inclusive rows x rows loops node
1. 2,064.916 16,357.538 ↑ 1.4 99,659 1

Sort (cost=8,019,247.55..8,019,594.70 rows=138,859 width=820) (actual time=16,274.286..16,357.538 rows=99,659 loops=1)

  • Sort Key: q."Point Id", q."From date", q."To date
  • Sort Method: external merge Disk: 25,848kB
2. 32.240 14,292.622 ↑ 1.4 99,659 1

Subquery Scan on q (cost=7,975,859.55..7,978,636.73 rows=138,859 width=820) (actual time=14,180.350..14,292.622 rows=99,659 loops=1)

3. 528.073 14,260.382 ↑ 1.4 99,659 1

HashAggregate (cost=7,975,859.55..7,977,248.14 rows=138,859 width=204) (actual time=14,180.347..14,260.382 rows=99,659 loops=1)

  • Group 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)
4.          

CTE previousmet

5. 1.016 839.300 ↑ 1.7 1,096 1

HashAggregate (cost=26,762.45..26,780.94 rows=1,849 width=4) (actual time=839.041..839.300 rows=1,096 loops=1)

  • Group Key: i_3.meteringid
6. 112.293 838.284 ↓ 1.2 2,132 1

Merge Anti Join (cost=24,926.04..26,757.83 rows=1,849 width=4) (actual time=705.139..838.284 rows=2,132 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. 102.937 406.853 ↓ 2.3 63,890 1

Sort (cost=9,514.67..9,584.81 rows=28,056 width=29) (actual time=397.276..406.853 rows=63,890 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. 48.693 303.916 ↓ 2.3 63,890 1

Hash Join (cost=3,152.49..7,441.89 rows=28,056 width=29) (actual time=48.658..303.916 rows=63,890 loops=1)

  • Hash Cond: (m_2.contractdpid = j.contractdpid)
9. 79.191 247.723 ↓ 2.3 63,890 1

Hash Join (cost=2,605.57..6,368.91 rows=28,056 width=29) (actual time=41.091..247.723 rows=63,890 loops=1)

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

Seq Scan on index i_3 (cost=0.00..3,132.09 rows=28,056 width=25) (actual time=0.036..127.628 rows=63,890 loops=1)

  • Filter: (previousindexdate < indexdate)
  • Rows Removed by Filter: 20,277
11. 24.156 40.904 ↑ 1.0 68,114 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,661kB
12. 16.748 16.748 ↑ 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.005..16.748 rows=68,114 loops=1)

13. 3.844 7.500 ↑ 1.0 10,619 1

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

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

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

15. 106.028 319.138 ↑ 1.0 76,473 1

Sort (cost=15,411.37..15,602.92 rows=76,617 width=25) (actual time=307.852..319.138 rows=76,473 loops=1)

  • Sort Key: contractdpjournal.pointid, index.indexdate, index.index, index.timeframe, index.unit, index.type
  • Sort Method: quicksort Memory: 9,045kB
16. 50.186 213.110 ↑ 1.0 76,481 1

Hash Join (cost=548.20..9,195.68 rows=76,617 width=25) (actual time=5.785..213.110 rows=76,481 loops=1)

  • Hash Cond: (metering.contractdpid = contractdpjournal.contractdpid)
17. 57.665 157.207 ↑ 1.0 76,481 1

Merge Join (cost=1.27..7,212.18 rows=76,617 width=25) (actual time=0.049..157.207 rows=76,481 loops=1)

  • Merge Cond: (metering.meteringid = index.meteringid)
18. 37.518 37.518 ↑ 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.021..37.518 rows=68,114 loops=1)

19. 62.024 62.024 ↑ 1.0 76,481 1

Index Scan using idx_index_meteringid on index (cost=0.29..3,800.22 rows=76,617 width=25) (actual time=0.015..62.024 rows=76,481 loops=1)

  • Filter: ((status)::text = 'A'::text)
  • Rows Removed by Filter: 7,686
20. 2.852 5.717 ↑ 1.0 10,619 1

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

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

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

22. 28.205 13,732.309 ↑ 1.4 99,669 1

Append (cost=44,769.91..7,938,664.18 rows=138,859 width=204) (actual time=2,400.807..13,732.309 rows=99,669 loops=1)

23. 1,553.962 11,435.012 ↓ 1.0 84,173 1

Nested Loop Left Join (cost=44,769.91..6,780,758.28 rows=84,167 width=217) (actual time=2,400.806..11,435.012 rows=84,173 loops=1)

  • Join Filter: (i.previousindexdate = i.indexdate)
  • Rows Removed by Join Filter: 792
24. 3,347.001 8,569.266 ↑ 1.0 84,167 1

Nested Loop Left Join (cost=44,769.61..2,040,835.14 rows=84,167 width=221) (actual time=1,401.171..8,569.266 rows=84,167 loops=1)

  • Join Filter: (dpc.dpconfigjid IS NULL)
  • Rows Removed by Join Filter: 84,166
25. 3,102.604 4,969.764 ↑ 1.0 84,167 1

Nested Loop Left Join (cost=44,754.08..727,475.28 rows=84,167 width=223) (actual time=1,400.243..4,969.764 rows=84,167 loops=1)

26. 190.312 1,614.659 ↑ 1.0 84,167 1

Hash Join (cost=44,746.10..49,561.52 rows=84,167 width=217) (actual time=1,398.598..1,614.659 rows=84,167 loops=1)

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

Seq Scan on index i (cost=0.00..2,921.67 rows=84,167 width=71) (actual time=0.130..25.908 rows=84,167 loops=1)

28. 63.832 1,398.439 ↑ 1.0 68,114 1

Hash (cost=43,894.67..43,894.67 rows=68,114 width=150) (actual time=1,398.439..1,398.439 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 11,385kB
29. 66.996 1,334.607 ↑ 1.0 68,114 1

Hash Left Join (cost=13,837.82..43,894.67 rows=68,114 width=150) (actual time=392.921..1,334.607 rows=68,114 loops=1)

  • Hash Cond: (t.envid = msg.envid)
30. 77.932 1,013.920 ↑ 1.0 68,114 1

Hash Join (cost=4,657.67..33,353.26 rows=68,114 width=137) (actual time=139.164..1,013.920 rows=68,114 loops=1)

  • Hash Cond: (m.contractdpid = cdp.contractdpid)
31. 472.565 871.036 ↑ 1.0 68,114 1

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

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

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

33. 24.936 72.964 ↑ 1.0 68,114 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,438kB
34. 48.028 48.028 ↑ 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..48.028 rows=68,114 loops=1)

35. 6.928 64.952 ↑ 1.0 10,619 1

Hash (cost=1,919.37..1,919.37 rows=10,619 width=84) (actual time=64.952..64.952 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 1,036kB
36. 6.033 58.024 ↑ 1.0 10,619 1

Hash Left Join (cost=592.74..1,919.37 rows=10,619 width=84) (actual time=13.236..58.024 rows=10,619 loops=1)

  • Hash Cond: (contract.supplierid = s.supplierid)
37. 9.307 51.964 ↑ 1.0 10,619 1

Hash Join (cost=591.70..1,772.31 rows=10,619 width=56) (actual time=13.190..51.964 rows=10,619 loops=1)

  • Hash Cond: (cdp.contractid = contract.contractid)
38. 9.639 29.517 ↑ 1.0 10,619 1

Merge Join (cost=0.61..982.12 rows=10,619 width=52) (actual time=0.027..29.517 rows=10,619 loops=1)

  • Merge Cond: (dp.pointid = cdp.pointid)
39. 7.069 7.069 ↑ 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.012..7.069 rows=8,487 loops=1)

40. 12.809 12.809 ↑ 1.0 10,619 1

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp (cost=0.29..537.57 rows=10,619 width=25) (actual time=0.006..12.809 rows=10,619 loops=1)

41. 3.648 13.140 ↑ 1.0 10,404 1

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

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

Seq Scan on contract (cost=0.00..461.04 rows=10,404 width=8) (actual time=0.175..9.492 rows=10,404 loops=1)

43. 0.003 0.027 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
44. 0.024 0.024 ↑ 1.0 2 1

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

45. 83.613 253.691 ↑ 1.0 194,940 1

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

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

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

47. 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.003..0.003 rows=1 loops=84,167)

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

SubPlan (for Index Scan)

49. 0.000 2,861.678 ↑ 1.0 1 84,167

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

50. 505.002 2,861.678 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.034..0.034 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: quicksort Memory: 25kB
51. 321.251 2,356.676 ↓ 2.0 2 84,167

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

52. 58.472 1,515.006 ↓ 2.0 2 84,167

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

53. 589.169 589.169 ↓ 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.007 rows=2 loops=84,167)

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

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.005 rows=1 loops=173,473)

  • Index Cond: (msgid = j_3.txref)
55. 520.419 520.419 ↑ 1.0 1 173,473

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

  • Index Cond: (j_3.physicalconfigid = physicalconfigid)
56. 0.000 2,861.678 ↑ 1.0 1 84,167

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

57. 505.002 2,861.678 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.034..0.034 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: quicksort Memory: 25kB
58. 321.251 2,356.676 ↓ 2.0 2 84,167

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

59. 58.472 1,515.006 ↓ 2.0 2 84,167

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

60. 589.169 589.169 ↓ 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.007 rows=2 loops=84,167)

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

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.005 rows=1 loops=173,473)

  • Index Cond: (msgid = j_3.txref)
62. 520.419 520.419 ↑ 1.0 1 173,473

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

  • Index Cond: (j_3.physicalconfigid = physicalconfigid)
63. 0.000 252.501 ↑ 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.003..0.003 rows=1 loops=84,167)

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

SubPlan (for Index Scan)

65. 0.000 3,198.346 ↑ 1.0 1 84,167

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

66.          

Initplan (for Limit)

67. 84.167 1,346.672 ↑ 1.0 1 84,167

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

68. 127.763 1,262.505 ↓ 1.5 3 84,167

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

69. 420.835 420.835 ↓ 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.003..0.005 rows=3 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((source)::text = 'DGO'::text)
70. 713.907 713.907 ↑ 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.003 rows=1 loops=237,969)

  • Index Cond: (physicalconfigid = j1_1.physicalconfigid)
  • Heap Fetches: 265,022
71. 336.668 3,114.179 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.037..0.037 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
72. 218.779 2,777.511 ↑ 1.0 1 84,167

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

73. 201.918 2,356.676 ↑ 1.0 1 84,167

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

74. 1,851.674 1,851.674 ↑ 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.020..0.022 rows=1 loops=84,167)

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

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,028)

  • Index Cond: (msgid = j_4.txref)
76. 202.056 202.056 ↑ 1.0 1 101,028

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,028)

  • Index Cond: (j_4.physicalconfigid = physicalconfigid)
77. 0.000 3,198.346 ↑ 1.0 1 84,167

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

78.          

Initplan (for Limit)

79. 84.167 1,346.672 ↑ 1.0 1 84,167

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

80. 127.763 1,262.505 ↓ 1.5 3 84,167

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

81. 420.835 420.835 ↓ 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.003..0.005 rows=3 loops=84,167)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((source)::text = 'DGO'::text)
82. 713.907 713.907 ↑ 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.003 rows=1 loops=237,969)

  • Index Cond: (physicalconfigid = j1_1.physicalconfigid)
  • Heap Fetches: 265,022
83. 336.668 3,114.179 ↑ 1.0 1 84,167

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.037..0.037 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
84. 218.779 2,777.511 ↑ 1.0 1 84,167

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

85. 201.918 2,356.676 ↑ 1.0 1 84,167

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

86. 1,851.674 1,851.674 ↑ 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.020..0.022 rows=1 loops=84,167)

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

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,028)

  • Index Cond: (msgid = j_4.txref)
88. 202.056 202.056 ↑ 1.0 1 101,028

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,028)

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

Index Scan using idx_index_meteringid on index i2 (cost=0.29..0.38 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
90.          

SubPlan (for Nested Loop Left Join)

91. 0.000 0.000 ↓ 0.0 0

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

92. 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)
93. 0.000 0.000 ↓ 0.0 0

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

  • Group Key: previousmet_1.meteringid
94. 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)

95. 0.000 0.000 ↓ 0.0 0

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

96. 0.000 0.000 ↓ 0.0 0

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

97. 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)
98. 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)
99. 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))
100. 0.947 899.410 ↑ 22.4 1,710 1

Hash Join (cost=3,194.39..5,015.67 rows=38,308 width=8) (actual time=891.117..899.410 rows=1,710 loops=1)

  • Hash Cond: (m3_1.contractdpid = j3_1.contractdpid)
101. 1.323 883.667 ↑ 22.4 1,710 1

Hash Join (cost=2,647.46..3,750.47 rows=38,308 width=8) (actual time=876.288..883.667 rows=1,710 loops=1)

  • Hash Cond: (i3_1.meteringid = m3_1.meteringid)
102. 0.369 846.219 ↑ 22.4 1,710 1

Nested Loop (cost=41.90..282.97 rows=38,308 width=12) (actual time=840.130..846.219 rows=1,710 loops=1)

103. 0.656 840.370 ↓ 5.5 1,096 1

HashAggregate (cost=41.60..43.60 rows=200 width=4) (actual time=840.107..840.370 rows=1,096 loops=1)

  • Group Key: previousmet_2.meteringid
104. 839.714 839.714 ↑ 1.7 1,096 1

CTE Scan on previousmet previousmet_2 (cost=0.00..36.98 rows=1,849 width=4) (actual time=839.046..839.714 rows=1,096 loops=1)

105. 5.480 5.480 ↑ 1.0 2 1,096

Index Scan using idx_index_meteringid on index i3_1 (cost=0.29..1.18 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=1,096)

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,661kB
107. 15.659 15.659 ↑ 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.659 rows=68,114 loops=1)

108. 4.237 14.796 ↑ 1.0 10,619 1

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

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

Seq Scan on contractdpjournal j3_1 (cost=0.00..414.19 rows=10,619 width=8) (actual time=0.101..10.559 rows=10,619 loops=1)

110. 0.000 0.000 ↓ 0.0 0

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

111. 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)
112. 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
113. 32.788 75.706 ↑ 1.0 61,691 1

Hash Join (cost=546.93..3,359.54 rows=61,691 width=4) (actual time=4.984..75.706 rows=61,691 loops=1)

  • Hash Cond: (p_3.contractdpid = cdp_6.contractdpid)
114. 38.017 38.017 ↑ 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.048..38.017 rows=61,691 loops=1)

115. 2.391 4.901 ↑ 1.0 10,619 1

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

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

Seq Scan on contractdpjournal cdp_6 (cost=0.00..414.19 rows=10,619 width=8) (actual time=0.006..2.510 rows=10,619 loops=1)

117. 1.903 2,138.258 ↑ 15.4 2,728 1

Subquery Scan on *SELECT* 2 (cost=44,816.01..1,153,362.49 rows=41,988 width=209) (actual time=1,623.339..2,138.258 rows=2,728 loops=1)

118. 69.818 2,136.355 ↑ 15.4 2,728 1

Nested Loop Left Join (cost=44,816.01..1,152,942.61 rows=41,988 width=209) (actual time=1,623.336..2,136.355 rows=2,728 loops=1)

  • Join Filter: (i_1.previousindexdate = i_1.indexdate)
  • Rows Removed by Join Filter: 44
119. 111.088 1,999.598 ↑ 15.4 2,728 1

Nested Loop Left Join (cost=44,815.72..1,043,298.12 rows=41,988 width=217) (actual time=1,623.175..1,999.598 rows=2,728 loops=1)

  • Join Filter: (dpc_1.dpconfigjid IS NULL)
  • Rows Removed by Join Filter: 2,728
120. 99.585 1,880.326 ↑ 15.4 2,728 1

Nested Loop Left Join (cost=44,800.18..388,108.39 rows=41,988 width=219) (actual time=1,623.021..1,880.326 rows=2,728 loops=1)

121. 15.584 1,772.557 ↑ 15.4 2,728 1

Hash Join (cost=44,792.20..49,920.71 rows=41,988 width=213) (actual time=1,622.907..1,772.557 rows=2,728 loops=1)

  • Hash Cond: (m_1.meteringid = previousmet.meteringid)
122. 106.057 1,755.859 ↓ 1.0 84,080 1

Hash Join (cost=44,746.10..49,557.22 rows=83,976 width=217) (actual time=1,621.730..1,755.859 rows=84,080 loops=1)

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

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

  • Filter: ((previousindexdate IS NOT NULL) AND (previousindex IS NOT NULL))
  • Rows Removed by Filter: 87
124. 101.573 1,621.565 ↑ 1.0 68,114 1

Hash (cost=43,894.67..43,894.67 rows=68,114 width=146) (actual time=1,621.565..1,621.565 rows=68,114 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 11,168kB
125. 68.806 1,519.992 ↑ 1.0 68,114 1

Hash Left Join (cost=13,837.82..43,894.67 rows=68,114 width=146) (actual time=407.345..1,519.992 rows=68,114 loops=1)

  • Hash Cond: (t_1.envid = msg_1.envid)
126. 79.827 1,157.666 ↑ 1.0 68,114 1

Hash Join (cost=4,657.67..33,353.26 rows=68,114 width=133) (actual time=113.340..1,157.666 rows=68,114 loops=1)

  • Hash Cond: (m_1.contractdpid = cdp_1.contractdpid)
127. 525.584 1,019.946 ↑ 1.0 68,114 1

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

  • Hash Cond: (t_1.msgid = m_1.txref)
128. 440.328 440.328 ↑ 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.004..440.328 rows=564,873 loops=1)

129. 29.087 54.034 ↑ 1.0 68,114 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,438kB
130. 24.947 24.947 ↑ 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.010..24.947 rows=68,114 loops=1)

131. 8.414 57.893 ↑ 1.0 10,619 1

Hash (cost=1,919.37..1,919.37 rows=10,619 width=80) (actual time=57.893..57.893 rows=10,619 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 995kB
132. 4.961 49.479 ↑ 1.0 10,619 1

Hash Left Join (cost=592.74..1,919.37 rows=10,619 width=80) (actual time=12.448..49.479 rows=10,619 loops=1)

  • Hash Cond: (contract_1.supplierid = s_1.supplierid)
133. 7.982 44.492 ↑ 1.0 10,619 1

Hash Join (cost=591.70..1,772.31 rows=10,619 width=52) (actual time=12.381..44.492 rows=10,619 loops=1)

  • Hash Cond: (cdp_1.contractid = contract_1.contractid)
134. 7.755 24.207 ↑ 1.0 10,619 1

Merge Join (cost=0.61..982.12 rows=10,619 width=48) (actual time=0.028..24.207 rows=10,619 loops=1)

  • Merge Cond: (dp_1.pointid = cdp_1.pointid)
135. 6.254 6.254 ↑ 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.011..6.254 rows=8,487 loops=1)

136. 10.198 10.198 ↑ 1.0 10,619 1

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_1 (cost=0.29..537.57 rows=10,619 width=25) (actual time=0.010..10.198 rows=10,619 loops=1)

137. 4.011 12.303 ↑ 1.0 10,404 1

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

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

Seq Scan on contract contract_1 (cost=0.00..461.04 rows=10,404 width=8) (actual time=0.173..8.292 rows=10,404 loops=1)

139. 0.003 0.026 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
140. 0.023 0.023 ↑ 1.0 2 1

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

141. 114.301 293.520 ↑ 1.0 194,940 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 10,849kB
142. 179.219 179.219 ↑ 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.021..179.219 rows=194,940 loops=1)

143. 0.331 1.114 ↓ 5.5 1,096 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
144. 0.647 0.783 ↓ 5.5 1,096 1

HashAggregate (cost=41.60..43.60 rows=200 width=4) (actual time=0.617..0.783 rows=1,096 loops=1)

  • Group Key: previousmet.meteringid
145. 0.136 0.136 ↑ 1.7 1,096 1

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

146. 0.000 8.184 ↑ 1.0 1 2,728

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=2,728)

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

SubPlan (for Index Scan)

148. 2.728 92.752 ↑ 1.0 1 2,728

Limit (cost=7.69..7.70 rows=1 width=26) (actual time=0.034..0.034 rows=1 loops=2,728)

149. 13.640 90.024 ↑ 1.0 1 2,728

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.033..0.033 rows=1 loops=2,728)

  • Sort Key: ((tx.msgdate)::date), ((j_1.creationdate)::date), j_1.fromdate, j_1.powerlimiter, m_3.budgetmeter
  • Sort Method: quicksort Memory: 25kB
150. 8.638 76.384 ↓ 3.0 3 2,728

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.014..0.028 rows=3 loops=2,728)

151. 5.152 49.104 ↓ 2.0 2 2,728

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

152. 19.096 19.096 ↓ 2.0 2 2,728

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=2,728)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((fromdate <= i_1.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
153. 24.856 24.856 ↑ 1.0 1 6,214

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=6,214)

  • Index Cond: (msgid = j_1.txref)
154. 18.642 18.642 ↑ 1.0 1 6,214

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=6,214)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
155. 2.728 92.752 ↑ 1.0 1 2,728

Limit (cost=7.69..7.70 rows=1 width=26) (actual time=0.034..0.034 rows=1 loops=2,728)

156. 13.640 90.024 ↑ 1.0 1 2,728

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.033..0.033 rows=1 loops=2,728)

  • Sort Key: ((tx.msgdate)::date), ((j_1.creationdate)::date), j_1.fromdate, j_1.powerlimiter, m_3.budgetmeter
  • Sort Method: quicksort Memory: 25kB
157. 8.638 76.384 ↓ 3.0 3 2,728

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.014..0.028 rows=3 loops=2,728)

158. 5.152 49.104 ↓ 2.0 2 2,728

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

159. 19.096 19.096 ↓ 2.0 2 2,728

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=2,728)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((fromdate <= i_1.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
160. 24.856 24.856 ↑ 1.0 1 6,214

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=6,214)

  • Index Cond: (msgid = j_1.txref)
161. 18.642 18.642 ↑ 1.0 1 6,214

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=6,214)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
162. 0.000 8.184 ↑ 1.0 1 2,728

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

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

SubPlan (for Index Scan)

164. 0.000 106.392 ↑ 1.0 1 2,728

Limit (cost=15.24..15.25 rows=1 width=26) (actual time=0.039..0.039 rows=1 loops=2,728)

165.          

Initplan (for Limit)

166. 2.728 49.104 ↑ 1.0 1 2,728

Aggregate (cost=7.54..7.55 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=2,728)

167. 3.939 46.376 ↓ 2.0 4 2,728

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

168. 13.640 13.640 ↓ 2.0 4 2,728

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

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((source)::text = 'DGO'::text)
169. 28.797 28.797 ↑ 1.0 1 9,599

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=9,599)

  • Index Cond: (physicalconfigid = j1.physicalconfigid)
  • Heap Fetches: 10,919
170. 8.184 103.664 ↑ 1.0 1 2,728

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.038..0.038 rows=1 loops=2,728)

  • Sort Key: ((tx_1.msgdate)::date), ((j_2.creationdate)::date), j_2.powerlimiter, m_4.budgetmeter
  • Sort Method: quicksort Memory: 25kB
171. 9.568 95.480 ↑ 1.0 1 2,728

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.029..0.035 rows=1 loops=2,728)

172. 3.440 79.112 ↑ 1.0 1 2,728

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.026..0.029 rows=1 loops=2,728)

173. 65.472 65.472 ↑ 1.0 1 2,728

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_2 (cost=0.29..2.91 rows=1 width=25) (actual time=0.022..0.024 rows=1 loops=2,728)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $10))
  • Rows Removed by Filter: 2
174. 10.200 10.200 ↑ 1.0 1 3,400

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=3,400)

  • Index Cond: (msgid = j_2.txref)
175. 6.800 6.800 ↑ 1.0 1 3,400

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=3,400)

  • Index Cond: (j_2.physicalconfigid = physicalconfigid)
176. 0.000 106.392 ↑ 1.0 1 2,728

Limit (cost=15.24..15.25 rows=1 width=26) (actual time=0.039..0.039 rows=1 loops=2,728)

177.          

Initplan (for Limit)

178. 2.728 49.104 ↑ 1.0 1 2,728

Aggregate (cost=7.54..7.55 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=2,728)

179. 3.939 46.376 ↓ 2.0 4 2,728

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

180. 13.640 13.640 ↓ 2.0 4 2,728

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

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: ((source)::text = 'DGO'::text)
181. 28.797 28.797 ↑ 1.0 1 9,599

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=9,599)

  • Index Cond: (physicalconfigid = j1.physicalconfigid)
  • Heap Fetches: 10,919
182. 8.184 103.664 ↑ 1.0 1 2,728

Sort (cost=7.69..7.70 rows=1 width=26) (actual time=0.038..0.038 rows=1 loops=2,728)

  • Sort Key: ((tx_1.msgdate)::date), ((j_2.creationdate)::date), j_2.powerlimiter, m_4.budgetmeter
  • Sort Method: quicksort Memory: 25kB
183. 9.568 95.480 ↑ 1.0 1 2,728

Nested Loop Left Join (cost=0.99..7.68 rows=1 width=26) (actual time=0.029..0.035 rows=1 loops=2,728)

184. 3.440 79.112 ↑ 1.0 1 2,728

Nested Loop Left Join (cost=0.71..5.37 rows=1 width=29) (actual time=0.026..0.029 rows=1 loops=2,728)

185. 65.472 65.472 ↑ 1.0 1 2,728

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_2 (cost=0.29..2.91 rows=1 width=25) (actual time=0.022..0.024 rows=1 loops=2,728)

  • Index Cond: (pointid = dp_1.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $10))
  • Rows Removed by Filter: 2
186. 10.200 10.200 ↑ 1.0 1 3,400

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=3,400)

  • Index Cond: (msgid = j_2.txref)
187. 6.800 6.800 ↑ 1.0 1 3,400

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=3,400)

  • Index Cond: (j_2.physicalconfigid = physicalconfigid)
188. 13.640 13.640 ↓ 0.0 0 2,728

Index Scan using idx_index_meteringid on index i2_1 (cost=0.29..0.38 rows=1 width=19) (actual time=0.005..0.005 rows=0 loops=2,728)

  • 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: 4
189.          

SubPlan (for Nested Loop Left Join)

190. 0.000 0.000 ↓ 0.0 0

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

191. 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)
192. 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
193. 35.721 53.299 ↑ 1.0 61,691 1

Hash Join (cost=546.93..3,359.54 rows=61,691 width=4) (actual time=6.932..53.299 rows=61,691 loops=1)

  • Hash Cond: (p_1.contractdpid = cdp_4.contractdpid)
194. 10.713 10.713 ↑ 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.713 rows=61,691 loops=1)

195. 3.770 6.865 ↑ 1.0 10,619 1

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

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

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

197. 5.489 130.834 ↓ 1.0 12,768 1

Subquery Scan on *SELECT* 3 (cost=2,052.11..3,701.75 rows=12,704 width=106) (actual time=47.242..130.834 rows=12,768 loops=1)

198. 69.215 125.345 ↓ 1.0 12,768 1

Hash Join (cost=2,052.11..3,574.71 rows=12,704 width=106) (actual time=47.240..125.345 rows=12,768 loops=1)

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

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

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 839kB
201. 4.342 39.090 ↑ 1.0 10,619 1

Hash Left Join (cost=592.74..1,919.37 rows=10,619 width=68) (actual time=6.836..39.090 rows=10,619 loops=1)

  • Hash Cond: (contract_2.supplierid = s_2.supplierid)
202. 6.818 34.742 ↑ 1.0 10,619 1

Hash Join (cost=591.70..1,772.31 rows=10,619 width=40) (actual time=6.798..34.742 rows=10,619 loops=1)

  • Hash Cond: (cdp_2.contractid = contract_2.contractid)
203. 6.442 21.207 ↑ 1.0 10,619 1

Merge Join (cost=0.61..982.12 rows=10,619 width=36) (actual time=0.029..21.207 rows=10,619 loops=1)

  • Merge Cond: (dp_2.pointid = cdp_2.pointid)
204. 6.502 6.502 ↑ 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.012..6.502 rows=8,487 loops=1)

205. 8.263 8.263 ↑ 1.0 10,619 1

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_2 (cost=0.29..537.57 rows=10,619 width=20) (actual time=0.010..8.263 rows=10,619 loops=1)

206. 3.629 6.717 ↑ 1.0 10,404 1

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

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

Seq Scan on contract contract_2 (cost=0.00..461.04 rows=10,404 width=8) (actual time=0.012..3.088 rows=10,404 loops=1)

208. 0.002 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
209. 0.004 0.004 ↑ 1.0 2 1

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