explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X1e4 : 3M Default

Settings
# exclusive inclusive rows x rows loops node
1. 3,130.159 250,355.128 ↓ 5.6 1,505,358 1

Gather (cost=4,516,304.44..5,993,152.33 rows=268,086 width=10,932) (actual time=28,411.877..250,355.128 rows=1,505,358 loops=1)

  • Buffers: shared hit=9125645 read=1927498, temp read=285114 written=305318
2. 209,088.144 247,224.969 ↓ 4.5 501,786 3

Nested Loop (cost=4,515,304.44..5,965,343.73 rows=111,702 width=10,932) (actual time=28,398.261..247,224.969 rows=501,786 loops=3)

  • Buffers: shared hit=9125645 read=1927498, temp read=285114 written=305318
3. 580.254 38,136.412 ↓ 4.5 501,787 3

Hash Join (cost=4,515,304.01..5,159,840.48 rows=111,702 width=10,700) (actual time=28,398.163..38,136.412 rows=501,787 loops=3)

  • Buffers: shared hit=3754601 read=1268879, temp read=285114 written=305318
4. 616.926 37,556.095 ↓ 4.5 501,787 3

Hash Join (cost=4,515,301.88..5,159,520.29 rows=111,702 width=9,673) (actual time=28,397.914..37,556.095 rows=501,787 loops=3)

  • Buffers: shared hit=3754502 read=1268878, temp read=285114 written=305318
5. 648.171 36,939.123 ↓ 4.5 501,787 3

Hash Join (cost=4,515,299.76..5,159,200.09 rows=111,702 width=8,641) (actual time=28,397.839..36,939.123 rows=501,787 loops=3)

  • Buffers: shared hit=3754500 read=1268877, temp read=285114 written=305318
6. 9,411.281 36,290.931 ↓ 4.5 501,787 3

Hash Join (cost=4,515,289.53..5,158,896.65 rows=111,702 width=8,011) (actual time=28,397.792..36,290.931 rows=501,787 loops=3)

  • Buffers: shared hit=3754500 read=1268877, temp read=285114 written=305318
7. 2,125.453 2,125.453 ↑ 1.3 669,014 3

Seq Scan on led l8 (cost=0..510,123.68 rows=836,268 width=80) (actual time=0.058..2,125.453 rows=669,014 loops=3)

  • Buffers: shared read=501761
8. 884.596 24,754.197 ↓ 4.5 501,787 3

Hash (cost=4,405,245.26..4,405,245.26 rows=111,702 width=7,943) (actual time=24,754.197..24,754.197 rows=501,787 loops=3)

  • Buffers: shared hit=3754469 read=767116, temp read=210345 written=273962
9. 1,759.654 23,869.601 ↓ 4.5 501,787 3

Hash Join (cost=2,836,159.95..4,405,245.26 rows=111,702 width=7,943) (actual time=23,165.961..23,869.601 rows=501,787 loops=3)

  • Buffers: shared hit=3754469 read=767116, temp read=210345 written=223786
10. 276.630 21,773.333 ↓ 1.1 501,787 3

Merge Join (cost=2,798,947.8..3,483,412.52 rows=454,321 width=7,923) (actual time=12,935.088..21,773.333 rows=501,787 loops=3)

  • Buffers: shared hit=3754467 read=746426, temp read=165686 written=178986
11. 216.553 21,496.686 ↓ 1.1 501,787 3

Merge Join (cost=2,798,934.47..3,482,262.93 rows=454,321 width=7,443) (actual time=12,935.059..21,496.686 rows=501,787 loops=3)

  • Buffers: shared hit=3754467 read=746426, temp read=165686 written=178986
12. 186.172 21,280.114 ↓ 1.1 501,787 3

Merge Join (cost=2,798,920.15..3,481,112.23 rows=454,321 width=7,203) (actual time=12,935.032..21,280.114 rows=501,787 loops=3)

  • Buffers: shared hit=3754467 read=746426, temp read=165686 written=178986
13. 2,106.307 21,093.905 ↓ 1.1 501,787 3

Merge Join (cost=2,798,905.83..3,479,961.52 rows=454,321 width=7,083) (actual time=12,934.988..21,093.905 rows=501,787 loops=3)

  • Buffers: shared hit=3754467 read=746426, temp read=165686 written=178986
14. 340.916 13,691.041 ↓ 1.1 501,787 3

Merge Join (cost=2,798,882.7..2,801,154.66 rows=454,321 width=3,423) (actual time=12,934.887..13,691.041 rows=501,787 loops=3)

  • Buffers: shared hit=25 read=228215, temp read=165686 written=178986
15. 2,804.469 13,350.080 ↓ 1.1 501,787 3

Sort (cost=2,798,870.33..2,800,006.13 rows=454,321 width=3,253) (actual time=12,934.829..13,350.08 rows=501,787 loops=3)

  • Sort Key: l4.id
  • Buffers: shared hit=25 read=228215, temp read=165686 written=178986
16. 3,941.915 10,545.611 ↓ 1.1 501,787 3

Hash Join (cost=348,137.41..846,166.26 rows=454,321 width=3,253) (actual time=9,140.142..10,545.611 rows=501,787 loops=3)

  • Buffers: shared hit=13 read=228215, temp read=115360 written=128552
17. 3,070.857 4,862.094 ↓ 1.1 2,007,043 3

Hash Join (cost=170,605.03..337,587.48 rows=1,765,480 width=567) (actual time=2,933.527..4,862.094 rows=2,007,043 loops=3)

  • Buffers: shared hit=11 read=141344, temp read=49232 written=55156
18. 885.245 885.245 ↓ 1.1 2,007,043 3

Seq Scan on lkm l4 (cost=0..82,857.05 rows=1,765,480 width=24) (actual time=0.083..885.245 rows=2,007,043 loops=3)

  • Filter: ((l4.aggregation_level)::text = ANY ('{l,CHUCK1,CHUCK2}'::text[]))
  • Buffers: shared hit=9 read=48358
19. 451.757 905.992 ↑ 1.3 669,014 3

Hash (cost=101,350.68..101,350.68 rows=836,268 width=549) (actual time=905.991..905.992 rows=669,014 loops=3)

  • Buffers: shared hit=2 read=92986, temp written=18252
20. 454.235 454.235 ↑ 1.3 669,014 3

Seq Scan on lms l3 (cost=0..101,350.68 rows=836,268 width=549) (actual time=0.046..454.235 rows=669,014 loops=3)

  • Buffers: shared hit=2 read=92986
21. 234.569 1,741.602 ↑ 1.3 167,262 3

Hash (cost=103,598.36..103,598.36 rows=215,202 width=2,686) (actual time=1,741.602..1,741.602 rows=167,262 loops=3)

  • Buffers: shared hit=2 read=86871, temp written=12256
22. 1,507.033 1,507.033 ↑ 1.3 167,262 3

Seq Scan on les l1 (cost=0..103,598.36 rows=215,202 width=2,686) (actual time=0.082..1,507.033 rows=167,262 loops=3)

  • Filter: ((l1.l_start >= to_timestamp('2018-01-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS.FF'::text)) AND (l1.l_start <= to_timestamp('2018-03-31 23:59:00'::text, 'YYYY-MM-DD HH24:MI:SS.FF'::text)))
  • Buffers: shared hit=2 read=86871
23. 0.015 0.045 ↓ 0.0 0 3

Sort (cost=12.37..12.52 rows=60 width=190) (actual time=0.045..0.045 rows=0 loops=3)

  • Sort Key: l9.id
24. 0.030 0.030 ↓ 0.0 0 3

Seq Scan on lerk l9 (cost=0..10.6 rows=60 width=190) (actual time=0.03..0.03 rows=0 loops=3)

25. 5,296.557 5,296.557 ↑ 1.0 6,021,102 3

Index Scan using idx_lealgk_id on leak l10 (cost=0.43..658,129.37 rows=6,021,129 width=3,666) (actual time=0.053..5,296.557 rows=6,021,102 loops=3)

  • Buffers: shared hit=3754442 read=518211
26. 0.006 0.037 ↓ 0.0 0 3

Sort (cost=14.32..14.57 rows=100 width=140) (actual time=0.037..0.037 rows=0 loops=3)

  • Sort Key: l12.id
27. 0.031 0.031 ↓ 0.0 0 3

Seq Scan on locck l12 (cost=0..11 rows=100 width=140) (actual time=0.031..0.031 rows=0 loops=3)

28. 0.004 0.019 ↓ 0.0 0 3

Sort (cost=14.32..14.57 rows=100 width=260) (actual time=0.019..0.019 rows=0 loops=3)

  • Sort Key: l13.id
29. 0.015 0.015 ↓ 0.0 0 3

Seq Scan on losck l13 (cost=0..11 rows=100 width=260) (actual time=0.015..0.015 rows=0 loops=3)

30. 0.004 0.017 ↓ 0.0 0 3

Sort (cost=13.33..13.53 rows=80 width=500) (actual time=0.017..0.017 rows=0 loops=3)

  • Sort Key: l11.id
31. 0.013 0.013 ↓ 0.0 0 3

Seq Scan on loack l11 (cost=0..10.8 rows=80 width=500) (actual time=0.013..0.013 rows=0 loops=3)

32. 77.350 336.614 ↑ 1.2 167,262 3

Hash (cost=33,236.02..33,236.02 rows=205,610 width=26) (actual time=336.614..336.614 rows=167,262 loops=3)

  • Buffers: shared hit=2 read=20690, temp written=3064
33. 259.264 259.264 ↑ 1.2 167,262 3

Seq Scan on l_step l2 (cost=0..33,236.02 rows=205,610 width=26) (actual time=0.054..259.264 rows=167,262 loops=3)

  • Filter: ((l2.month_of_year >= '1'::numeric) AND (l2.month_of_year <= '3'::numeric))
  • Buffers: shared hit=2 read=20690
34. 0.001 0.021 ↓ 0.0 0 3

Hash (cost=10.1..10.1 rows=10 width=650) (actual time=0.021..0.021 rows=0 loops=3)

35. 0.020 0.020 ↓ 0.0 0 3

Seq Scan on loapk l14 (cost=0..10.1 rows=10 width=650) (actual time=0.02..0.02 rows=0 loops=3)

36. 0.021 0.046 ↑ 1.0 50 3

Hash (cost=1.5..1.5 rows=50 width=1,032) (actual time=0.046..0.046 rows=50 loops=3)

  • Buffers: shared hit=2 read=1
37. 0.025 0.025 ↑ 1.0 50 3

Seq Scan on eosure_machine e6 (cost=0..1.5 rows=50 width=1,032) (actual time=0.017..0.025 rows=50 loops=3)

  • Buffers: shared hit=2 read=1
38. 0.024 0.063 ↑ 1.0 50 3

Hash (cost=1.5..1.5 rows=50 width=1,032) (actual time=0.062..0.063 rows=50 loops=3)

  • Buffers: shared hit=2 read=1
39. 0.039 0.039 ↑ 1.0 50 3

Seq Scan on mm m5 (cost=0..1.5 rows=50 width=1,032) (actual time=0.03..0.039 rows=50 loops=3)

  • Buffers: shared hit=2 read=1
40. 0.413 0.413 ↑ 1.0 1 1,505,361

Index Scan using idx_lmok_id on lmok l7 (cost=0.43..7.2 rows=1 width=78) (actual time=0.413..0.413 rows=1 loops=1,505,361)

  • Index Cond: (l7.id = l4.id)
  • Buffers: shared hit=5371044 read=658619
Planning time : 22.792 ms
Execution time : 250,550.422 ms