explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oTqE

Settings
# exclusive inclusive rows x rows loops node
1. 154,400.162 380,372.545 ↓ 2.0 2,863,807 1

Nested Loop Left Join (cost=2,565,924.26..61,354,225.69 rows=1,420,734 width=218) (actual time=56,321.481..380,372.545 rows=2,863,807 loops=1)

  • Join Filter: (dpc.dpconfigjid IS NULL)
  • Rows Removed by Join Filter: 2,863,799
2. 113,337.267 203,953.366 ↓ 2.0 2,863,807 1

Nested Loop Left Join (cost=2,565,898.06..19,286,436.55 rows=1,420,734 width=220) (actual time=56,321.217..203,953.366 rows=2,863,807 loops=1)

3. 7,784.914 79,160.871 ↓ 2.0 2,863,807 1

Hash Left Join (cost=2,565,887.18..2,753,755.10 rows=1,420,734 width=214) (actual time=56,320.879..79,160.871 rows=2,863,807 loops=1)

  • Hash Cond: (t.envid = msg.envid)
4. 6,604.927 68,467.327 ↓ 2.0 2,863,807 1

Merge Left Join (cost=2,417,537.84..2,484,268.46 rows=1,420,734 width=200) (actual time=53,412.134..68,467.327 rows=2,863,807 loops=1)

  • Merge Cond: ((i.meteringid = i2.meteringid) AND ((i.timeframe)::text = (i2.timeframe)::text) AND ((i.unit)::text = (i2.unit)::text) AND ((i.type)::text = (i2.type)::text))
  • Join Filter: ((i.meterid <> i2.meterid) AND (i.previousindexdate = i.indexdate))
  • Rows Removed by Join Filter: 2,967,277
5. 8,685.188 55,182.140 ↓ 2.0 2,862,636 1

Sort (cost=1,983,758.30..1,987,310.13 rows=1,420,734 width=204) (actual time=53,412.128..55,182.140 rows=2,862,636 loops=1)

  • Sort Key: i.meteringid, i.timeframe, i.unit, i.type
  • Sort Method: external merge Disk: 582,032kB
6. 1,209.719 46,496.952 ↓ 2.0 2,862,636 1

Hash Left Join (cost=781,119.66..1,758,100.03 rows=1,420,734 width=204) (actual time=21,080.606..46,496.952 rows=2,862,636 loops=1)

  • Hash Cond: (contract.supplierid = s.supplierid)
7. 2,949.896 45,287.184 ↓ 2.0 2,862,636 1

Hash Join (cost=781,118.61..1,738,563.89 rows=1,420,734 width=176) (actual time=21,080.541..45,287.184 rows=2,862,636 loops=1)

  • Hash Cond: (cdp.pointid = dp.pointid)
8. 4,704.764 42,110.514 ↓ 2.0 2,862,636 1

Merge Right Join (cost=767,303.61..1,698,110.12 rows=1,420,734 width=152) (actual time=20,853.673..42,110.514 rows=2,862,636 loops=1)

  • Merge Cond: (t.msgid = m.txref)
9. 13,310.581 13,310.581 ↑ 1.0 19,590,444 1

Index Scan using idx_transaction_msgid_pk on transaction t (cost=0.44..858,133.38 rows=19,593,892 width=48) (actual time=0.018..13,310.581 rows=19,590,444 loops=1)

10. 700.332 24,095.169 ↓ 2.0 2,862,636 1

Materialize (cost=767,303.17..774,406.84 rows=1,420,734 width=112) (actual time=20,853.102..24,095.169 rows=2,862,636 loops=1)

11. 7,241.157 23,394.837 ↓ 2.0 2,862,636 1

Sort (cost=767,303.17..770,855.00 rows=1,420,734 width=112) (actual time=20,853.095..23,394.837 rows=2,862,636 loops=1)

  • Sort Key: m.txref
  • Sort Method: external merge Disk: 362,432kB
12. 2,919.718 16,153.680 ↓ 2.0 2,862,636 1

Hash Right Join (cost=462,086.44..574,942.90 rows=1,420,734 width=112) (actual time=13,197.929..16,153.680 rows=2,862,636 loops=1)

  • Hash Cond: ((pmet.pointid = cdp.pointid) AND (pmet.previousindexdate = i.indexdate))
  • Join Filter: (pmet.meteringid <> m.meteringid)
13. 39.652 39.652 ↑ 1.0 176,671 1

Seq Scan on previousmet pmet (cost=0.00..2,721.71 rows=176,671 width=12) (actual time=0.023..39.652 rows=176,671 loops=1)

14. 2,692.777 13,194.310 ↓ 2.0 2,841,468 1

Hash (cost=417,188.43..417,188.43 rows=1,420,734 width=112) (actual time=13,194.310..13,194.310 rows=2,841,468 loops=1)

  • Buckets: 32,768 Batches: 16 (originally 8) Memory Usage: 32,769kB
15. 3,279.110 10,501.533 ↓ 2.0 2,841,468 1

Hash Join (cost=69,461.78..417,188.43 rows=1,420,734 width=112) (actual time=1,373.137..10,501.533 rows=2,841,468 loops=1)

  • Hash Cond: (m.contractdpid = cdp.contractdpid)
16. 1,996.099 5,849.416 ↓ 2.0 2,841,468 1

Merge Join (cost=5.79..317,541.84 rows=1,420,734 width=87) (actual time=0.033..5,849.416 rows=2,841,468 loops=1)

  • Merge Cond: (m.meteringid = i.meteringid)
17. 2,170.992 2,170.992 ↓ 2.0 2,942,103 1

Index Scan using idx_metering_contractdpid on metering m (cost=0.43..163,706.21 rows=1,471,152 width=18) (actual time=0.014..2,170.992 rows=2,942,103 loops=1)

  • Filter: (migration IS TRUE)
18. 1,682.325 1,682.325 ↑ 1.0 2,841,468 1

Index Scan using idx_index_meteringid on index i (cost=0.43..128,858.57 rows=2,841,468 width=69) (actual time=0.013..1,682.325 rows=2,841,468 loops=1)

19. 131.719 1,373.007 ↑ 1.0 454,413 1

Hash (cost=63,775.83..63,775.83 rows=454,413 width=29) (actual time=1,373.007..1,373.007 rows=454,413 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 24,382kB
20. 637.986 1,241.288 ↑ 1.0 454,413 1

Hash Join (cost=36,576.43..63,775.83 rows=454,413 width=29) (actual time=446.494..1,241.288 rows=454,413 loops=1)

  • Hash Cond: (cdp.contractid = contract.contractid)
21. 156.980 156.980 ↑ 1.0 454,413 1

Seq Scan on contractdpjournal cdp (cost=0.00..17,543.13 rows=454,413 width=25) (actual time=0.080..156.980 rows=454,413 loops=1)

22. 120.169 446.322 ↑ 1.0 449,530 1

Hash (cost=30,957.30..30,957.30 rows=449,530 width=8) (actual time=446.322..446.322 rows=449,530 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 17,560kB
23. 326.153 326.153 ↑ 1.0 449,530 1

Seq Scan on contract (cost=0.00..30,957.30 rows=449,530 width=8) (actual time=0.027..326.153 rows=449,530 loops=1)

24. 91.698 226.774 ↑ 1.0 350,208 1

Hash (cost=9,436.67..9,436.67 rows=350,267 width=28) (actual time=226.774..226.774 rows=350,208 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 20,520kB
25. 135.076 135.076 ↑ 1.0 350,208 1

Seq Scan on deliverypoint dp (cost=0.00..9,436.67 rows=350,267 width=28) (actual time=0.025..135.076 rows=350,208 loops=1)

26. 0.003 0.049 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
27. 0.046 0.046 ↑ 1.0 2 1

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

28. 504.829 6,680.260 ↓ 1.1 2,991,121 1

Materialize (cost=433,779.55..447,986.89 rows=2,841,468 width=17) (actual time=5,039.546..6,680.260 rows=2,991,121 loops=1)

29. 4,692.904 6,175.431 ↑ 1.0 2,841,468 1

Sort (cost=433,779.55..440,883.22 rows=2,841,468 width=17) (actual time=5,039.542..6,175.431 rows=2,841,468 loops=1)

  • Sort Key: i2.meteringid, i2.timeframe, i2.unit, i2.type
  • Sort Method: external merge Disk: 81,056kB
30. 1,482.527 1,482.527 ↑ 1.0 2,841,468 1

Seq Scan on index i2 (cost=0.00..95,899.68 rows=2,841,468 width=17) (actual time=0.027..1,482.527 rows=2,841,468 loops=1)

31. 1,028.539 2,908.630 ↑ 1.0 2,867,304 1

Hash (cost=95,707.04..95,707.04 rows=2,867,304 width=22) (actual time=2,908.630..2,908.630 rows=2,867,304 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 20,731kB
32. 1,880.091 1,880.091 ↑ 1.0 2,867,304 1

Seq Scan on msgenv msg (cost=0.00..95,707.04 rows=2,867,304 width=22) (actual time=0.032..1,880.091 rows=2,867,304 loops=1)

33. 0.000 11,455.228 ↑ 1.0 1 2,863,807

Index Scan using dpj_un on dpconfigjournal dpc (cost=10.87..11.63 rows=1 width=6) (actual time=0.003..0.004 rows=1 loops=2,863,807)

  • Index Cond: (dpconfigjid = (SubPlan 2))
34.          

SubPlan (for Index Scan)

35. 2,863.807 108,824.666 ↑ 1.0 1 2,863,807

Limit (cost=10.44..10.45 rows=1 width=26) (actual time=0.038..0.038 rows=1 loops=2,863,807)

36. 14,319.035 105,960.859 ↑ 1.0 1 2,863,807

Sort (cost=10.44..10.45 rows=1 width=26) (actual time=0.037..0.037 rows=1 loops=2,863,807)

  • Sort Key: ((tx.msgdate)::date), ((j.creationdate)::date), j.fromdate, j.powerlimiter, m_1.budgetmeter
  • Sort Method: quicksort Memory: 25kB
37. 6,048.457 91,641.824 ↓ 2.0 2 2,863,807

Nested Loop Left Join (cost=1.29..10.43 rows=1 width=26) (actual time=0.016..0.032 rows=2 loops=2,863,807)

38. 5,412.716 60,139.947 ↓ 2.0 2 2,863,807

Nested Loop Left Join (cost=0.86..7.98 rows=1 width=29) (actual time=0.011..0.021 rows=2 loops=2,863,807)

39. 22,910.456 22,910.456 ↓ 2.0 2 2,863,807

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j (cost=0.42..5.51 rows=1 width=25) (actual time=0.005..0.008 rows=2 loops=2,863,807)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((fromdate <= i.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
40. 31,816.775 31,816.775 ↑ 1.0 1 6,363,355

Index Scan using idx_transaction_msgid_pk on transaction tx (cost=0.44..2.46 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=6,363,355)

  • Index Cond: (msgid = j.txref)
41. 25,453.420 25,453.420 ↑ 1.0 1 6,363,355

Index Scan using idx_meter_physicalconfigid_pk on meter m_1 (cost=0.42..2.44 rows=1 width=5) (actual time=0.003..0.004 rows=1 loops=6,363,355)

  • Index Cond: (j.physicalconfigid = physicalconfigid)
42. 2,863.807 108,824.666 ↑ 1.0 1 2,863,807

Limit (cost=10.44..10.45 rows=1 width=26) (actual time=0.038..0.038 rows=1 loops=2,863,807)

43. 14,319.035 105,960.859 ↑ 1.0 1 2,863,807

Sort (cost=10.44..10.45 rows=1 width=26) (actual time=0.037..0.037 rows=1 loops=2,863,807)

  • Sort Key: ((tx.msgdate)::date), ((j.creationdate)::date), j.fromdate, j.powerlimiter, m_1.budgetmeter
  • Sort Method: quicksort Memory: 25kB
44. 6,048.457 91,641.824 ↓ 2.0 2 2,863,807

Nested Loop Left Join (cost=1.29..10.43 rows=1 width=26) (actual time=0.016..0.032 rows=2 loops=2,863,807)

45. 5,412.716 60,139.947 ↓ 2.0 2 2,863,807

Nested Loop Left Join (cost=0.86..7.98 rows=1 width=29) (actual time=0.011..0.021 rows=2 loops=2,863,807)

46. 22,910.456 22,910.456 ↓ 2.0 2 2,863,807

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j (cost=0.42..5.51 rows=1 width=25) (actual time=0.005..0.008 rows=2 loops=2,863,807)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((fromdate <= i.indexdate) AND ((source)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
47. 31,816.775 31,816.775 ↑ 1.0 1 6,363,355

Index Scan using idx_transaction_msgid_pk on transaction tx (cost=0.44..2.46 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=6,363,355)

  • Index Cond: (msgid = j.txref)
48. 25,453.420 25,453.420 ↑ 1.0 1 6,363,355

Index Scan using idx_meter_physicalconfigid_pk on meter m_1 (cost=0.42..2.44 rows=1 width=5) (actual time=0.003..0.004 rows=1 loops=6,363,355)

  • Index Cond: (j.physicalconfigid = physicalconfigid)
49. 0.000 8,591.421 ↑ 1.0 1 2,863,807

Index Scan using dpj_un on dpconfigjournal dpc2 (cost=26.20..26.96 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=2,863,807)

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

SubPlan (for Index Scan)

51. 0.000 105,960.859 ↑ 1.0 1 2,863,807

Limit (cost=25.77..25.78 rows=1 width=26) (actual time=0.037..0.037 rows=1 loops=2,863,807)

52.          

Initplan (for Limit)

53. 2,863.801 48,684.617 ↑ 1.0 1 2,863,801

Aggregate (cost=15.32..15.33 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=2,863,801)

54. 5,118.383 45,820.816 ↑ 1.3 3 2,863,801

Nested Loop Left Join (cost=0.85..15.31 rows=4 width=4) (actual time=0.006..0.016 rows=3 loops=2,863,801)

55. 14,319.005 14,319.005 ↑ 1.3 3 2,863,801

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j1 (cost=0.42..5.50 rows=4 width=12) (actual time=0.003..0.005 rows=3 loops=2,863,801)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((source)::text = 'DGO'::text)
  • Rows Removed by Filter: 0
56. 26,383.428 26,383.428 ↑ 1.0 1 8,794,476

Index Only Scan using idx_meter_physicalconfigid_pk on meter m1 (cost=0.42..2.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8,794,476)

  • Index Cond: (physicalconfigid = j1.physicalconfigid)
  • Heap Fetches: 9,688,357
57. 8,591.421 103,097.052 ↑ 1.0 1 2,863,807

Sort (cost=10.44..10.45 rows=1 width=26) (actual time=0.036..0.036 rows=1 loops=2,863,807)

  • Sort Key: ((tx_1.msgdate)::date), ((j_1.creationdate)::date), j_1.powerlimiter, m_2.budgetmeter
  • Sort Method: quicksort Memory: 25kB
58. 6,986.097 94,505.631 ↑ 1.0 1 2,863,807

Nested Loop Left Join (cost=1.29..10.43 rows=1 width=26) (actual time=0.028..0.033 rows=1 loops=2,863,807)

59. 3,319.628 80,186.596 ↑ 1.0 1 2,863,807

Nested Loop Left Join (cost=0.86..7.98 rows=1 width=29) (actual time=0.025..0.028 rows=1 loops=2,863,807)

60. 65,867.561 65,867.561 ↑ 1.0 1 2,863,807

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_1 (cost=0.42..5.51 rows=1 width=25) (actual time=0.021..0.023 rows=1 loops=2,863,807)

  • Index Cond: (pointid = dp.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $9))
  • Rows Removed by Filter: 2
61. 10,999.407 10,999.407 ↑ 1.0 1 3,666,469

Index Scan using idx_transaction_msgid_pk on transaction tx_1 (cost=0.44..2.46 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=3,666,469)

  • Index Cond: (msgid = j_1.txref)
62. 7,332.938 7,332.938 ↑ 1.0 1 3,666,469

Index Scan using idx_meter_physicalconfigid_pk on meter m_2 (cost=0.42..2.44 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=3,666,469)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
63. 0.000 105,960.859 ↑ 1.0 1 2,863,807

Limit (cost=25.77..25.78 rows=1 width=26) (actual time=0.037..0.037 rows=1 loops=2,863,807)

64.          

Initplan (for Limit)

65. 2,863.801 48,684.617 ↑ 1.0 1 2,863,801

Aggregate (cost=15.32..15.33 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=2,863,801)

66. 5,118.383 45,820.816 ↑ 1.3 3 2,863,801

Nested Loop Left Join (cost=0.85..15.31 rows=4 width=4) (actual time=0.006..0.016 rows=3 loops=2,863,801)

67. 14,319.005 14,319.005 ↑ 1.3 3 2,863,801

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j1 (cost=0.42..5.50 rows=4 width=12) (actual time=0.003..0.005 rows=3 loops=2,863,801)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((source)::text = 'DGO'::text)
  • Rows Removed by Filter: 0
68. 26,383.428 26,383.428 ↑ 1.0 1 8,794,476

Index Only Scan using idx_meter_physicalconfigid_pk on meter m1 (cost=0.42..2.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8,794,476)

  • Index Cond: (physicalconfigid = j1.physicalconfigid)
  • Heap Fetches: 9,688,357
69. 8,591.421 103,097.052 ↑ 1.0 1 2,863,807

Sort (cost=10.44..10.45 rows=1 width=26) (actual time=0.036..0.036 rows=1 loops=2,863,807)

  • Sort Key: ((tx_1.msgdate)::date), ((j_1.creationdate)::date), j_1.powerlimiter, m_2.budgetmeter
  • Sort Method: quicksort Memory: 25kB
70. 6,986.097 94,505.631 ↑ 1.0 1 2,863,807

Nested Loop Left Join (cost=1.29..10.43 rows=1 width=26) (actual time=0.028..0.033 rows=1 loops=2,863,807)

71. 3,319.628 80,186.596 ↑ 1.0 1 2,863,807

Nested Loop Left Join (cost=0.86..7.98 rows=1 width=29) (actual time=0.025..0.028 rows=1 loops=2,863,807)

72. 65,867.561 65,867.561 ↑ 1.0 1 2,863,807

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_1 (cost=0.42..5.51 rows=1 width=25) (actual time=0.021..0.023 rows=1 loops=2,863,807)

  • Index Cond: (pointid = dp.pointid)
  • Filter: (((source)::text = 'DGO'::text) AND (fromdate = $9))
  • Rows Removed by Filter: 2
73. 10,999.407 10,999.407 ↑ 1.0 1 3,666,469

Index Scan using idx_transaction_msgid_pk on transaction tx_1 (cost=0.44..2.46 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=3,666,469)

  • Index Cond: (msgid = j_1.txref)
74. 7,332.938 7,332.938 ↑ 1.0 1 3,666,469

Index Scan using idx_meter_physicalconfigid_pk on meter m_2 (cost=0.42..2.44 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=3,666,469)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
75.          

SubPlan (for Nested Loop Left Join)

76. 933.130 13,427.596 ↑ 12.0 1 959,114

Nested Loop (cost=0.85..18.62 rows=12 width=0) (actual time=0.014..0.014 rows=1 loops=959,114)

77. 4,795.570 4,795.570 ↑ 2.0 1 959,114

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal cdp_1 (cost=0.42..3.45 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=959,114)

  • Index Cond: (pointid = dp.pointid)
78. 7,698.896 7,698.896 ↑ 9.0 1 962,362

Index Only Scan using process_idx4test on process p (cost=0.43..7.50 rows=9 width=4) (actual time=0.008..0.008 rows=1 loops=962,362)

  • Index Cond: (contractdpid = cdp_1.contractdpid)
  • Heap Fetches: 959,114