explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dD1t

Settings
# exclusive inclusive rows x rows loops node
1. 0.646 1.028 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.13..233.55 rows=1 width=364) (actual time=1.025..1.028 rows=1 loops=1)

2. 0.005 0.122 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.69..8.58 rows=1 width=148) (actual time=0.119..0.122 rows=1 loops=1)

3. 0.004 0.108 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.27..7.70 rows=1 width=134) (actual time=0.105..0.108 rows=1 loops=1)

4. 0.005 0.094 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.85..6.85 rows=1 width=122) (actual time=0.091..0.094 rows=1 loops=1)

5. 0.005 0.072 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.43..5.96 rows=1 width=120) (actual time=0.070..0.072 rows=1 loops=1)

6. 0.009 0.057 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.29..5.81 rows=1 width=113) (actual time=0.056..0.057 rows=1 loops=1)

  • Filter: ((COALESCE(consumptionext.contractual, false) IS NULL) OR (COALESCE(consumptionext.contractual, false) IS FALSE))
7. 0.004 0.039 ↑ 1.0 1 1

Nested Loop (cost=0.86..4.90 rows=1 width=105) (actual time=0.038..0.039 rows=1 loops=1)

8. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

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

Index Scan using consumption_pkey on consumption (cost=0.43..2.45 rows=1 width=105) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (consumptionid = ic.consumptionid)
  • Filter: ((deleted IS FALSE) AND ((measurementfrequency)::text <> ALL ('{HOURLY,QUARTER_HOURLY}'::text[])))
10. 0.009 0.009 ↑ 1.0 1 1

Index Scan using consumptionext_pkey on consumptionext (cost=0.43..0.90 rows=1 width=13) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
11. 0.010 0.010 ↑ 1.0 1 1

Index Scan using meteringcontext_context_pk on meteringcontext (cost=0.14..0.16 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((context)::text = (consumption.context)::text)
12. 0.017 0.017 ↑ 1.0 1 1

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cd (cost=0.42..0.88 rows=1 width=20) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (deliveryperiodid = consumption.deliveryperiodid)
  • Filter: (((todate IS NULL) OR (consumption.fromdate < todate)) AND (consumption.todate > fromdate) AND ((consumption.externalref IS NULL) OR ((consumption.externalref)::integer = contractid)))
13. 0.010 0.010 ↑ 1.0 1 1

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.42..0.86 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (deliverypointcomponentid = consumption.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
14. 0.009 0.009 ↑ 1.0 1 1

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.42..0.88 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (deliverypointid = deliverypointcomponent.deliverypointid)
  • Filter: (deleted IS FALSE)
15. 0.010 0.010 ↑ 1.0 1 1

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

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

SubPlan (for Nested Loop Left Join)

17. 0.005 0.096 ↑ 1.0 1 1

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

18.          

Initplan (for Result)

19. 0.016 0.091 ↑ 21.0 1 1

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

  • Group Key: measurementnature.unit, consumptiondetail.measurementnature
20. 0.009 0.075 ↑ 21.0 1 1

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

  • Sort Key: measurementnature.unit, consumptiondetail.measurementnature
  • Sort Method: quicksort Memory: 25kB
21. 0.013 0.066 ↑ 21.0 1 1

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

  • Hash Cond: ((consumptiondetail.measurementnature)::text = (measurementnature.nature)::text)
22. 0.015 0.015 ↑ 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.013..0.015 rows=1 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
  • Filter: (ignored IS FALSE)
23. 0.017 0.038 ↑ 29.0 30 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
24. 0.021 0.021 ↑ 29.0 30 1

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

25. 0.003 0.022 ↑ 1.0 1 1

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

26.          

Initplan (for Result)

27. 0.001 0.019 ↓ 0.0 0 1

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

  • Group Key: measurementnature_1.unit, consumptiondetail_1.measurementnature
28. 0.003 0.018 ↓ 0.0 0 1

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

  • Sort Key: measurementnature_1.unit, consumptiondetail_1.measurementnature
  • Sort Method: quicksort Memory: 25kB
29. 0.001 0.015 ↓ 0.0 0 1

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

30. 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))
31. 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
32. 0.000 0.000 ↓ 0.0 0

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

33. 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)
34. 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)
35. 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)
36. 0.004 0.094 ↑ 1.0 1 1

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

37.          

Initplan (for Result)

38. 0.014 0.090 ↑ 5.0 1 1

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

  • Group Key: measurementnature_2.unit, consumptiondetail_2.measurementnature
39. 0.003 0.076 ↑ 5.0 1 1

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

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

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

41. 0.010 0.058 ↑ 5.0 1 1

Hash Left Join (cost=2.69..48.51 rows=5 width=27) (actual time=0.056..0.058 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))
42. 0.003 0.016 ↑ 10.0 1 1

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

43. 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))))
44. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Index Cond: (logicalregisterid = consumptiondetail_2.logicalregisterid)
  • Filter: (ignored IS FALSE)
45. 0.011 0.032 ↑ 1.0 37 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
46. 0.021 0.021 ↑ 1.0 37 1

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

47. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

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

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

49. 0.003 0.012 ↓ 0.0 0 1

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

  • Sort Key: transactioncrossref.creationdate
  • Sort Method: quicksort Memory: 25kB
50. 0.000 0.009 ↓ 0.0 0 1

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

51. 0.001 0.009 ↓ 0.0 0 1

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

52. 0.001 0.008 ↓ 0.0 0 1

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

53. 0.007 0.007 ↓ 0.0 0 1

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

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

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

55. 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)
56. 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
57. 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
58. 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))
59. 0.008 0.015 ↑ 1.0 1 1

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

60. 0.007 0.007 ↑ 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.006..0.007 rows=1 loops=1)

  • Index Cond: (consumptionid = consumption.consumptionid)
61. 0.001 0.010 ↓ 0.0 0 1

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

62. 0.009 0.009 ↓ 0.0 0 1

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

  • Index Cond: (targetconsumptionid = consumption.consumptionid)
63. 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[])))
64. 0.000 0.000 ↓ 0.0 0

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

65. 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)
66. 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 : 13.929 ms
Execution time : 1.516 ms