explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0dMw

Settings
# exclusive inclusive rows x rows loops node
1. 0.911 20,872.458 ↑ 1.0 1 1

Nested Loop Left Join (cost=66,307.70..547,071.92 rows=1 width=364) (actual time=19,312.946..20,872.458 rows=1 loops=1)

2. 363.584 20,870.987 ↑ 1.0 1 1

Hash Semi Join (cost=66,307.26..546,846.94 rows=1 width=148) (actual time=19,311.476..20,870.987 rows=1 loops=1)

  • Hash Cond: (consumption.consumptionid = ic.consumptionid)
3. 1,246.712 20,507.340 ↓ 2.0 2,538,224 1

Hash Left Join (cost=66,304.80..543,475.58 rows=1,283,393 width=148) (actual time=1,392.998..20,507.340 rows=2,538,224 loops=1)

  • Hash Cond: ((consumption.context)::text = (meteringcontext.context)::text)
4. 3,960.424 19,260.576 ↓ 2.0 2,538,224 1

Hash Left Join (cost=66,303.10..539,644.40 rows=1,283,393 width=141) (actual time=1,392.924..19,260.576 rows=2,538,224 loops=1)

  • Hash Cond: (consumption.deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
5. 3,360.383 14,293.906 ↓ 2.0 2,538,224 1

Hash Left Join (cost=25,720.78..433,865.42 rows=1,283,393 width=115) (actual time=385.631..14,293.906 rows=2,538,224 loops=1)

  • Hash Cond: (consumption.deliveryperiodid = cd.deliveryperiodid)
  • Join Filter: (((cd.todate IS NULL) OR (consumption.fromdate < cd.todate)) AND (consumption.todate > cd.fromdate) AND ((consumption.externalref IS NULL) OR ((consumption.externalref)::integer = cd.contractid)))
  • Rows Removed by Join Filter: 636,018
6. 2,213.527 10,548.119 ↓ 2.0 2,538,211 1

Merge Left Join (cost=2.40..325,738.15 rows=1,283,393 width=113) (actual time=0.041..10,548.119 rows=2,538,211 loops=1)

  • Merge Cond: (consumption.consumptionid = consumptionext.consumptionid)
  • Filter: ((COALESCE(consumptionext.contractual, false) IS NULL) OR (COALESCE(consumptionext.contractual, false) IS FALSE))
7. 5,247.264 5,247.264 ↑ 1.0 2,538,211 1

Index Scan using consumption_pkey on consumption (cost=0.43..166,582.86 rows=2,554,016 width=105) (actual time=0.017..5,247.264 rows=2,538,211 loops=1)

  • Filter: ((deleted IS FALSE) AND ((measurementfrequency)::text <> ALL ('{HOURLY,QUARTER_HOURLY}'::text[])))
  • Rows Removed by Filter: 1,097,650
8. 3,087.328 3,087.328 ↑ 1.0 3,635,848 1

Index Scan using consumptionext_pkey on consumptionext (cost=0.43..118,359.26 rows=3,643,946 width=13) (actual time=0.014..3,087.328 rows=3,635,848 loops=1)

9. 177.242 385.404 ↑ 1.0 461,925 1

Hash (cost=17,237.28..17,237.28 rows=461,928 width=20) (actual time=385.404..385.404 rows=461,925 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,401kB
10. 208.162 208.162 ↓ 1.0 461,932 1

Seq Scan on contractdelivery cd (cost=0.00..17,237.28 rows=461,928 width=20) (actual time=0.035..208.162 rows=461,932 loops=1)

11. 163.622 1,006.246 ↓ 1.0 355,433 1

Hash (cost=33,709.43..33,709.43 rows=355,431 width=30) (actual time=1,006.246..1,006.246 rows=355,433 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,266kB
12. 389.594 842.624 ↓ 1.0 355,433 1

Hash Left Join (cost=18,917.11..33,709.43 rows=355,431 width=30) (actual time=304.212..842.624 rows=355,433 loops=1)

  • Hash Cond: (deliverypointcomponent.deliverypointid = deliverypoint.deliverypointid)
13. 149.812 149.812 ↓ 1.0 355,433 1

Seq Scan on deliverypointcomponent (cost=0.00..8,304.31 rows=355,431 width=16) (actual time=0.036..149.812 rows=355,433 loops=1)

  • Filter: (deleted IS FALSE)
14. 124.965 303.218 ↓ 1.0 355,433 1

Hash (cost=12,391.27..12,391.27 rows=355,427 width=22) (actual time=303.218..303.218 rows=355,433 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,950kB
15. 178.253 178.253 ↓ 1.0 355,433 1

Seq Scan on deliverypoint (cost=0.00..12,391.27 rows=355,427 width=22) (actual time=0.028..178.253 rows=355,433 loops=1)

  • Filter: (deleted IS FALSE)
16. 0.019 0.052 ↓ 1.4 43 1

Hash (cost=1.31..1.31 rows=31 width=20) (actual time=0.052..0.052 rows=43 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
17. 0.033 0.033 ↓ 1.4 43 1

Seq Scan on meteringcontext (cost=0.00..1.31 rows=31 width=20) (actual time=0.024..0.033 rows=43 loops=1)

18. 0.004 0.063 ↑ 1.0 1 1

Hash (cost=2.45..2.45 rows=1 width=4) (actual time=0.063..0.063 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.059 0.059 ↑ 1.0 1 1

Index Scan using invoicedconsumption_invoiceid_idx on invoicedconsumption ic (cost=0.43..2.45 rows=1 width=4) (actual time=0.058..0.059 rows=1 loops=1)

  • Index Cond: (invoiceid = 14,127,008)
  • Filter: ((status)::text ~~ '%involved%'::text)
20. 0.093 0.093 ↑ 1.0 1 1

Index Scan using message_pk on message (cost=0.43..1.07 rows=1 width=12) (actual time=0.093..0.093 rows=1 loops=1)

  • Index Cond: (messageid = consumption.messageid)
21.          

SubPlan (for Nested Loop Left Join)

22. 0.012 0.176 ↑ 1.0 1 1

Result (cost=50.98..50.99 rows=1 width=32) (actual time=0.176..0.176 rows=1 loops=1)

23.          

Initplan (for Result)

24. 0.017 0.164 ↑ 21.0 1 1

GroupAggregate (cost=50.14..50.98 rows=21 width=80) (actual time=0.164..0.164 rows=1 loops=1)

  • Group Key: measurementnature.unit, consumptiondetail.measurementnature
25. 0.015 0.147 ↑ 21.0 1 1

Sort (cost=50.14..50.19 rows=21 width=65) (actual time=0.147..0.147 rows=1 loops=1)

  • Sort Key: measurementnature.unit, consumptiondetail.measurementnature
  • Sort Method: quicksort Memory: 25kB
26. 0.014 0.132 ↑ 21.0 1 1

Hash Join (cost=30.01..49.68 rows=21 width=65) (actual time=0.129..0.132 rows=1 loops=1)

  • Hash Cond: ((consumptiondetail.measurementnature)::text = (measurementnature.nature)::text)
27. 0.072 0.072 ↑ 21.0 1 1

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail (cost=0.43..20.05 rows=21 width=33) (actual time=0.070..0.072 rows=1 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
  • Filter: (ignored IS FALSE)
28. 0.016 0.046 ↑ 29.0 30 1

Hash (cost=18.70..18.70 rows=870 width=64) (actual time=0.046..0.046 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
29. 0.030 0.030 ↑ 29.0 30 1

Seq Scan on measurementnature (cost=0.00..18.70 rows=870 width=64) (actual time=0.025..0.030 rows=30 loops=1)

30. 0.003 0.024 ↑ 1.0 1 1

Result (cost=25.59..25.60 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=1)

31.          

Initplan (for Result)

32. 0.002 0.021 ↓ 0.0 0 1

GroupAggregate (cost=25.53..25.59 rows=1 width=80) (actual time=0.020..0.021 rows=0 loops=1)

  • Group Key: measurementnature_1.unit, consumptiondetail_1.measurementnature
33. 0.003 0.019 ↓ 0.0 0 1

Sort (cost=25.53..25.53 rows=1 width=59) (actual time=0.019..0.019 rows=0 loops=1)

  • Sort Key: measurementnature_1.unit, consumptiondetail_1.measurementnature
  • Sort Method: quicksort Memory: 25kB
34. 0.002 0.016 ↓ 0.0 0 1

Nested Loop (cost=1.15..25.52 rows=1 width=59) (actual time=0.016..0.016 rows=0 loops=1)

35. 0.001 0.014 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.00..23.35 rows=1 width=27) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: ((logicalregister.componentelementid IS NULL) OR ((componentelement.cardinality)::text = 'MANDATORY_BILLABLE'::text) OR ((componentelement.cardinality)::text = 'BILLABLE'::text))
36. 0.013 0.013 ↓ 0.0 0 1

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail consumptiondetail_1 (cost=0.43..20.21 rows=1 width=31) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
  • Filter: ((ignored IS FALSE) AND ((direction)::text = ANY ('{PRODUCTION,INJECTION}'::text[])) AND (((consumption.multiconfigtype IS TRUE) AND ((configtype)::text = 'SUPPLIER'::text)) OR ((consumption.multiconfigtype IS NOT TRUE) AND ((configtype)::text = 'DGO'::text))))
  • Rows Removed by Filter: 1
37. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.56..3.13 rows=1 width=17) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using logicalregister_pk on logicalregister (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (logicalregisterid = consumptiondetail_1.logicalregisterid)
  • Filter: (ignored IS FALSE)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using componentelement_pk on componentelement (cost=0.14..0.66 rows=1 width=13) (never executed)

  • Index Cond: (componentelementid = logicalregister.componentelementid)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using measurementnature_pk on measurementnature measurementnature_1 (cost=0.15..2.17 rows=1 width=64) (never executed)

  • Index Cond: ((nature)::text = (consumptiondetail_1.measurementnature)::text)
41. 0.004 0.137 ↑ 1.0 1 1

Result (cost=56.72..56.73 rows=1 width=32) (actual time=0.137..0.137 rows=1 loops=1)

42.          

Initplan (for Result)

43. 0.014 0.133 ↑ 5.0 1 1

GroupAggregate (cost=56.41..56.72 rows=5 width=80) (actual time=0.133..0.133 rows=1 loops=1)

  • Group Key: measurementnature_2.unit, consumptiondetail_2.measurementnature
44. 0.005 0.119 ↑ 5.0 1 1

Sort (cost=56.41..56.42 rows=5 width=59) (actual time=0.119..0.119 rows=1 loops=1)

  • Sort Key: measurementnature_2.unit, consumptiondetail_2.measurementnature
  • Sort Method: quicksort Memory: 25kB
45. 0.003 0.114 ↑ 5.0 1 1

Nested Loop (cost=2.84..56.35 rows=5 width=59) (actual time=0.113..0.114 rows=1 loops=1)

46. 0.012 0.079 ↑ 5.0 1 1

Hash Left Join (cost=2.69..48.51 rows=5 width=27) (actual time=0.077..0.079 rows=1 loops=1)

  • Hash Cond: (logicalregister_1.componentelementid = ce.componentelementid)
  • Filter: ((logicalregister_1.componentelementid IS NULL) OR ((ce.cardinality)::text = 'MANDATORY_BILLABLE'::text) OR ((ce.cardinality)::text = 'BILLABLE'::text))
47. 0.005 0.026 ↑ 10.0 1 1

Nested Loop Left Join (cost=0.86..46.65 rows=10 width=31) (actual time=0.026..0.026 rows=1 loops=1)

48. 0.011 0.011 ↑ 10.0 1 1

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail consumptiondetail_2 (cost=0.43..20.21 rows=10 width=31) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
  • Filter: ((ignored IS FALSE) AND ((direction)::text = ANY ('{CONSUMPTION,OFFTAKE}'::text[])) AND (((consumption.multiconfigtype IS TRUE) AND ((configtype)::text = 'SUPPLIER'::text)) OR ((consumption.multiconfigtype IS NOT TRUE) AND ((configtype)::text = 'DGO'::text))))
49. 0.010 0.010 ↓ 0.0 0 1

Index Scan using logicalregister_pk on logicalregister logicalregister_1 (cost=0.42..2.44 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (logicalregisterid = consumptiondetail_2.logicalregisterid)
  • Filter: (ignored IS FALSE)
50. 0.010 0.041 ↑ 1.0 37 1

Hash (cost=1.37..1.37 rows=37 width=13) (actual time=0.041..0.041 rows=37 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
51. 0.031 0.031 ↑ 1.0 37 1

Seq Scan on componentelement ce (cost=0.00..1.37 rows=37 width=13) (actual time=0.025..0.031 rows=37 loops=1)

52. 0.032 0.032 ↑ 1.0 1 1

Index Scan using measurementnature_pk on measurementnature measurementnature_2 (cost=0.15..1.57 rows=1 width=64) (actual time=0.032..0.032 rows=1 loops=1)

  • Index Cond: ((nature)::text = (consumptiondetail_2.measurementnature)::text)
53. 0.001 0.035 ↓ 0.0 0 1

Limit (cost=8.92..8.92 rows=1 width=18) (actual time=0.035..0.035 rows=0 loops=1)

54. 0.004 0.034 ↓ 0.0 0 1

Sort (cost=8.92..8.92 rows=1 width=18) (actual time=0.034..0.034 rows=0 loops=1)

  • Sort Key: transactioncrossref.creationdate
  • Sort Method: quicksort Memory: 25kB
55. 0.001 0.030 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.72..8.91 rows=1 width=18) (actual time=0.030..0.030 rows=0 loops=1)

56. 0.001 0.029 ↓ 0.0 0 1

Nested Loop (cost=1.44..8.53 rows=1 width=4) (actual time=0.029..0.029 rows=0 loops=1)

57. 0.001 0.028 ↓ 0.0 0 1

Nested Loop (cost=1.16..7.23 rows=1 width=4) (actual time=0.028..0.028 rows=0 loops=1)

58. 0.027 0.027 ↓ 0.0 0 1

Index Only Scan using processmessages_pk on processmessages (cost=0.29..2.31 rows=1 width=8) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: (messageid = consumption.messageid)
  • Heap Fetches: 0
59. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..4.91 rows=1 width=4) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Index Scan using "IX_consumption_messageid_consumption" on consumption consumption_1 (cost=0.43..2.45 rows=1 width=4) (never executed)

  • Index Cond: (messageid = consumption.messageid)
  • Filter: (deleted IS FALSE)
61. 0.000 0.000 ↓ 0.0 0

Index Only Scan using message_pk on message message_1 (cost=0.43..2.45 rows=1 width=4) (never executed)

  • Index Cond: (messageid = consumption.messageid)
  • Heap Fetches: 0
62. 0.000 0.000 ↓ 0.0 0

Index Only Scan using process_pkey on process (cost=0.28..1.30 rows=1 width=4) (never executed)

  • Index Cond: (processid = processmessages.processid)
  • Heap Fetches: 0
63. 0.000 0.000 ↓ 0.0 0

Index Scan using "IX_transactioncrossref_processid_transactioncrossref" on transactioncrossref (cost=0.29..0.37 rows=1 width=22) (never executed)

  • Index Cond: (processid = process.processid)
  • Filter: (((type)::text = 'STANDARD'::text) AND ((counterpart)::text = 'BackOffice'::text))
64. 0.010 0.034 ↑ 1.0 1 1

Aggregate (cost=2.45..2.46 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=1)

65. 0.024 0.024 ↑ 1.0 1 1

Index Scan using idx_invoicedconsumption_consumptionid on invoicedconsumption ic2 (cost=0.43..2.45 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
66. 0.001 0.061 ↓ 0.0 0 1

Nested Loop (cost=0.86..78.93 rows=1 width=0) (actual time=0.061..0.061 rows=0 loops=1)

67. 0.060 0.060 ↓ 0.0 0 1

Index Scan using consumptionrelation_targetconsumptionid_idx on consumptionrelation (cost=0.43..2.97 rows=31 width=4) (actual time=0.060..0.060 rows=0 loops=1)

  • Index Cond: (targetconsumptionid = consumption.consumptionid)
68. 0.000 0.000 ↓ 0.0 0

Index Scan using consumption_pkey on consumption consumption_2 (cost=0.43..2.45 rows=1 width=4) (never executed)

  • Index Cond: (consumptionid = consumptionrelation.sourceconsumptionid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = ANY ('{USER,AMR_HILO_USER}'::text[])))
69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..1,146.41 rows=110 width=4) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Index Scan using consumption_sourceid_idx on consumption consumption_3 (cost=0.43..276.97 rows=368 width=4) (never executed)

  • Index Cond: ((sourceid)::text = ANY ('{USER,AMR_HILO_USER}'::text[]))
  • Filter: (deleted IS FALSE)
71. 0.000 0.000 ↓ 0.0 0

Index Only Scan using consumptionrelation_pk on consumptionrelation consumptionrelation_1 (cost=0.43..2.35 rows=1 width=8) (never executed)

  • Index Cond: (sourceconsumptionid = consumption_3.consumptionid)
  • Heap Fetches: 0
Planning time : 14.742 ms
Execution time : 20,873.167 ms