explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YZc8

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=3,917,684.78..3,919,652.62 rows=16,064 width=855) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=3,917,684.78..3,917,724.94 rows=16,064 width=855) (actual rows= loops=)

  • Sort Key: dp.gsrn, dp.marketcode, (CASE WHEN ((dp.direction)::text = 'I'::text) THEN 'PRODUCTION'::text ELSE 'CONSUMPTION'::text END), fa.street, fa.number, fa.box, (COALESCE(fa.postalcode, fa.townpostalcode)), (COALESCE(fa.town, (SubPlan 1))), (COALESCE(fa.countrycode, fa.towncountrycode)), (CASE WHEN ((grid.actorcode)::text = ANY ('{Gaselwest,"Gaselwest (V)","Gaselwest (W)"}'::text[])) THEN 'GASELWEST'::character varying WHEN ((grid.actorcode)::text = 'Imea'::text) THEN 'IMEA'::character varying WHEN ((grid.actorcode)::text = 'Imea (ex-INGO)'::text) THEN 'IMEA (EX-IGAO)'::character varying WHEN ((grid.actorcode)::text = 'Imewo'::text) THEN 'IMEWO'::character varying WHEN ((grid.actorcode)::text = 'Infrax West'::text) THEN 'INFRAX WEST'::character varying WHEN ((grid.actorcode)::text = 'Inter-energa'::text) THEN 'INTER-ENERGA'::character varying WHEN ((grid.actorcode)::text = 'Intergem'::text) THEN 'INTERGEM'::character varying WHEN ((grid.actorcode)::text = ANY ('{Iveg,GHA}'::text[])) THEN 'IVEG'::character varying WHEN ((grid.actorcode)::text = 'Agem'::text) THEN 'AGEM'::character varying WHEN ((grid.actorcode)::text = ANY ('{"Iveka Kempen",Iveka-Kempen}'::text[])) THEN 'IVEKA KEMPEN'::character varying WHEN ((grid.actorcode)::text = ANY ('{Iveka-Nete,"Iveka Nete"}'::text[])) THEN 'IVEKA NETE'::character varying WHEN ((grid.actorcode)::text = 'Iverlek I'::text) THEN 'IVERLEK I'::character varying WHEN ((grid.actorcode)::text = 'Iverlek II'::text) THEN 'IVERLEK II'::character varying WHEN ((grid.actorcode)::text = 'ORES (Brabant wallon)'::text) THEN 'SEDILEC'::character varying WHEN ((grid.actorcode)::text = 'ORES (Est)'::text) THEN 'INTEREST'::character varying WHEN ((grid.actorcode)::text = 'ORES (Est) (St Vith)'::text) THEN 'INTEREST (ST VITH)'::character varying WHEN ((grid.actorcode)::text = 'ORES (Hainaut Elec) 1'::text) THEN 'IEH 1'::character varying WHEN ((grid.actorcode)::text = 'ORES (Hainaut Elec) 2'::text) THEN 'IEH 2 (TOURNAI)'::character varying WHEN ((grid.actorcode)::text = 'ORES (Hainaut Gaz) 1'::text) THEN 'IGH1'::character varying WHEN ((grid.actorcode)::text = 'ORES (Hainaut Gaz) 2'::text) THEN 'IGH2'::character varying WHEN ((grid.actorcode)::text = 'ORES (Luxembourg)'::text) THEN 'INTERLUX'::character varying WHEN ((grid.actorcode)::text = 'ORES (Mouscron)'::text) THEN 'SIMOGEL'::character varying WHEN ((grid.actorcode)::text = 'ORES (Namur)'::text) THEN 'IDEG'::character varying WHEN ((grid.actorcode)::text = 'ORES (Verviers)'::text) THEN 'INTERMOSANE 1'::character varying WHEN ((grid.actorcode)::text = ANY ('{"ORES (Verviers) 2 (V)","ORES (Verviers) 2 (W)"}'::text[])) THEN 'INTERMOSANE 2'::character varying WHEN ((grid.actorcode)::text = 'PBE (V)'::text) THEN 'PBE (V)'::character varying WHEN ((grid.actorcode)::text = 'R�gie de Wavre'::text) THEN 'REGIE DE WAVRE'::character varying WHEN ((grid.actorcode)::text = 'Sibelga IE'::text) THEN 'SIBELGA IE'::character varying WHEN ((grid.actorcode)::text = 'Sibelga IG'::text) THEN 'SIBELGA IG'::character varying WHEN ((grid.actorcode)::text = 'Sibelga SE'::text) THEN 'SIBELGA SE'::character varying WHEN ((grid.actorcode)::text = 'Sibelga SG'::text) THEN 'SIBELGA SG'::character varying WHEN ((grid.actorcode)::text = 'Sibelgas Noord'::text) THEN 'SIBELGAS NOORD'::character varying WHEN ((grid.actorcode)::text = 'Brussels Airport'::text) THEN 'DNBBA'::character varying WHEN ((grid.actorcode)::text = 'Elia'::text) THEN 'ELIA'::character varying ELSE grid.actorcode END), reg.actorcode, a.actorcode, dpc.fromdate, ((SubPlan 2)), (CASE WHEN ((dpc.switchcateg)::text = 'C'::text) THEN 'CONTINUOUS'::text WHEN ((dpc.switchcateg)::text = 'M'::text) THEN 'MONTH'::text WHEN ((dpc.switchcateg)::text = 'Y'::text) THEN 'YEAR'::text ELSE 'UNKNOWN'::text END), dpc.gridrate, dpc.slpid, (CASE WHEN (dpc.nextreading IS NULL) THEN 0 ELSE (date_part('month'::text, (dpc.nextreading)::timestamp without time zone))::integer END), dp1.aggrcitygate, dpc.pressurelevel, dpc.voltagelevel, dpc.copperloss, dpc.ironloss, dpc.powerlimiter, dpc.decprod, (NULL::text), (NULL::text), (NULL::text), dpc.contractedcapacity, (NULL::text), (NULL::text), (NULL::text), dpc.source, (array_agg(m.meternumber ORDER BY m.meterid)), (array_agg(CASE WHEN m.electronicmeter THEN 'ELECTRONIC_METER'::text WHEN m.budgetmeter THEN 'BUDGET_METER'::text ELSE 'MECHANICAL_METER'::text END ORDER BY m.meterid)), (NULL::text), stdconfig.configname, (array_agg(r.registernbr ORDER BY r.registernbr)), (NULL::text), (array_agg(CASE WHEN ((r.timeframecode)::text = 'B36'::text) THEN 'NIGHT_EXCLUSIVE'::text WHEN ((r.timeframecode)::text = 'E11'::text) THEN 'HIGH'::text WHEN ((r.timeframecode)::text = 'E10'::text) THEN 'LOW'::text WHEN ((r.timeframecode)::text = 'B37'::text) THEN 'TOTAL_HOUR'::text WHEN ((r.timeframecode)::text = 'NPK'::text) THEN 'NON_PEAK'::text WHEN ((r.timeframecode)::text = 'NPH'::text) THEN 'NON_PEAK_HIGH'::text WHEN ((r.timeframecode)::text = 'NPL'::text) THEN 'NON_PEAK_LOW'::text WHEN ((r.timeframecode)::text = 'E12'::text) THEN 'PEAK'::text WHEN ((r.timeframecode)::text = 'PKH'::text) THEN 'PEAK_HIGH'::text WHEN ((r.timeframecode)::text = 'PKL'::text) THEN 'PEAK_LOW'::text ELSE 'UNKNOWN'::text END ORDER BY r.registernbr)), (array_agg(CASE WHEN (((r.nature)::text = 'B32'::text) AND ((r.type)::text = 'B31'::text)) THEN 'ACTIVE_POWER'::text WHEN (((r.nature)::text = 'B33'::text) AND ((r.type)::text = 'B31'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((r.nature)::text = 'B34'::text) AND ((r.type)::text = 'B29'::text)) THEN 'REACTIVE_CAPACITIVE_POWER'::text WHEN (((r.nature)::text = 'B34'::text) AND ((r.type)::text = 'B30'::text)) THEN 'REACTIVE_INDUCTIVE_POWER'::text WHEN (((r.nature)::text = 'B35'::text) AND ((r.type)::text = 'B29'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((r.nature)::text = 'B35'::text) AND ((r.type)::text = 'B30'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text ELSE 'UNKNOWN'::text END ORDER BY r.registernbr)), (NULL::text), (NULL::text), (NULL::text), (NULL::text), dpc.dpconfigjid
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=168.13..3,910,521.58 rows=16,064 width=855) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=157.90..2,538,334.70 rows=16,064 width=847) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=157.61..2,529,634.38 rows=16,064 width=806) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..6,558.16 rows=16,064 width=55) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..3,775.12 rows=16,064 width=47) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using idx_deliverypoint_addressid_pk on deliverypoint dp (cost=0.29..992.08 rows=16,064 width=39) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using actor_pkey on actor grid (cost=0.14..0.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (actorid = dp.gridoperatorid)
10. 0.000 0.000 ↓ 0.0

Index Scan using actor_pkey on actor reg (cost=0.14..0.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (actorid = dp.regulatorid)
11. 0.000 0.000 ↓ 0.0

Limit (cost=157.04..157.04 rows=1 width=296) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Sort (cost=157.04..157.05 rows=4 width=296) (actual rows= loops=)

  • Sort Key: dpc.fromdate, dpc.creationdate
13. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=115.27..157.02 rows=4 width=296) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Sort (cost=115.27..115.28 rows=4 width=296) (actual rows= loops=)

  • Sort Key: dpc.dpconfigjid, dpc.fromdate, dpc.switchcateg, dpc.gridrate, dpc.slpid, dpc.nextreading, dp1.aggrcitygate, dpc.pressurelevel, dpc.voltagelevel, dpc.copperloss, dpc.ironloss, dpc.powerlimiter, dpc.decprod, dpc.contractedcapacity, dpc.source, stdconfig.configname, (array_agg(r.registernbr ORDER BY r.registernbr)), (array_agg(CASE WHEN ((r.timeframecode)::text = 'B36'::text) THEN 'NIGHT_EXCLUSIVE'::text WHEN ((r.timeframecode)::text = 'E11'::text) THEN 'HIGH'::text WHEN ((r.timeframecode)::text = 'E10'::text) THEN 'LOW'::text WHEN ((r.timeframecode)::text = 'B37'::text) THEN 'TOTAL_HOUR'::text WHEN ((r.timeframecode)::text = 'NPK'::text) THEN 'NON_PEAK'::text WHEN ((r.timeframecode)::text = 'NPH'::text) THEN 'NON_PEAK_HIGH'::text WHEN ((r.timeframecode)::text = 'NPL'::text) THEN 'NON_PEAK_LOW'::text WHEN ((r.timeframecode)::text = 'E12'::text) THEN 'PEAK'::text WHEN ((r.timeframecode)::text = 'PKH'::text) THEN 'PEAK_HIGH'::text WHEN ((r.timeframecode)::text = 'PKL'::text) THEN 'PEAK_LOW'::text ELSE 'UNKNOWN'::text END ORDER BY r.registernbr)), (array_agg(CASE WHEN (((r.nature)::text = 'B32'::text) AND ((r.type)::text = 'B31'::text)) THEN 'ACTIVE_POWER'::text WHEN (((r.nature)::text = 'B33'::text) AND ((r.type)::text = 'B31'::text)) THEN 'ACTIVE_ENERGY'::text WHEN (((r.nature)::text = 'B34'::text) AND ((r.type)::text = 'B29'::text)) THEN 'REACTIVE_CAPACITIVE_POWER'::text WHEN (((r.nature)::text = 'B34'::text) AND ((r.type)::text = 'B30'::text)) THEN 'REACTIVE_INDUCTIVE_POWER'::text WHEN (((r.nature)::text = 'B35'::text) AND ((r.type)::text = 'B29'::text)) THEN 'REACTIVE_CAPACITIVE_ENERGY'::text WHEN (((r.nature)::text = 'B35'::text) AND ((r.type)::text = 'B30'::text)) THEN 'REACTIVE_INDUCTIVE_ENERGY'::text ELSE 'UNKNOWN'::text END ORDER BY r.registernbr)), dp1.pointid
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=63.11..115.23 rows=4 width=296) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using deliverypoint_pkey on deliverypoint dp1 (cost=0.29..8.30 rows=1 width=23) (actual rows= loops=)

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

Merge Right Join (cost=62.82..106.88 rows=4 width=277) (actual rows= loops=)

  • Merge Cond: (r.configid = dpc.configid)
18. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=35.86..74.36 rows=440 width=24) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=35.86..36.96 rows=440 width=24) (actual rows= loops=)

  • Sort Key: r.configid, stdconfig.configname
20. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.09..16.54 rows=440 width=24) (actual rows= loops=)

  • Hash Cond: (r.configid = stdconfig.configid)
21. 0.000 0.000 ↓ 0.0

Seq Scan on stdconfigregister r (cost=0.00..7.40 rows=440 width=20) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=1.93..1.93 rows=93 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on stdconfig (cost=0.00..1.93 rows=93 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Sort (cost=26.96..26.97 rows=2 width=181) (actual rows= loops=)

  • Sort Key: dpc.configid
25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..26.95 rows=2 width=181) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal dpc (cost=0.29..10.32 rows=2 width=170) (actual rows= loops=)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((dpconfigjid IS NOT NULL) AND ((fromdate <> todate) OR (todate IS NULL)))
27. 0.000 0.000 ↓ 0.0

Index Scan using idx_meter_physicalconfigid_pk on meter m (cost=0.29..8.30 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (physicalconfigid = dpc.physicalconfigid)
28.          

SubPlan (forGroupAggregate)

29. 0.000 0.000 ↓ 0.0

Limit (cost=10.35..10.35 rows=1 width=4) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=10.35..10.35 rows=1 width=4) (actual rows= loops=)

  • Sort Key: dpc1.fromdate
31. 0.000 0.000 ↓ 0.0

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal dpc1 (cost=0.29..10.34 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (pointid = dp1.pointid)
  • Filter: ((dpconfigjid <> dpc.dpconfigjid) AND (((dpconfigjid > dpc.dpconfigjid) AND (fromdate = dpc.fromdate)) OR (fromdate > dpc.fromdate)))
32. 0.000 0.000 ↓ 0.0

Index Scan using freeaddresses_pkey on freeaddresses fa (cost=0.29..0.53 rows=1 width=49) (actual rows= loops=)

  • Index Cond: (addressid = dp.addressid)
33. 0.000 0.000 ↓ 0.0

Limit (cost=10.23..10.24 rows=1 width=16) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=10.23..10.24 rows=1 width=16) (actual rows= loops=)

  • Sort Key: cdp.fromdate
35. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.32..10.22 rows=1 width=16) (actual rows= loops=)

  • Hash Cond: (a.actorid = cdp.brpid)
36. 0.000 0.000 ↓ 0.0

Seq Scan on actor a (cost=0.00..1.65 rows=65 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=8.30..8.30 rows=1 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp (cost=0.29..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (pointid = dp.pointid)
39.          

SubPlan (forNested Loop)

40. 0.000 0.000 ↓ 0.0

Seq Scan on town (cost=0.00..75.07 rows=1 width=10) (actual rows= loops=)

  • Filter: (((postalcode)::text = (fa.townpostalcode)::text) AND (towncode = fa.towntowncode))