explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3EL2

Settings
# exclusive inclusive rows x rows loops node
1. 785.037 103,674.634 ↓ 3.0 604 1

GroupAggregate (cost=526,100.81..526,472.74 rows=200 width=48) (actual time=102,641.561..103,674.634 rows=604 loops=1)

  • Group Key: s.sample_hour
2. 1,737.927 102,889.597 ↓ 391.2 2,435,335 1

Sort (cost=526,100.81..526,116.37 rows=6,225 width=92) (actual time=102,638.493..102,889.597 rows=2,435,335 loops=1)

  • Sort Key: s.sample_hour
  • Sort Method: quicksort Memory: 229,027kB
3. 1,546.830 101,151.670 ↓ 391.2 2,435,335 1

Hash Left Join (cost=42,553.46..525,708.51 rows=6,225 width=92) (actual time=1,470.270..101,151.670 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = nin2.sample_id)
4. 1,575.066 99,574.849 ↓ 391.2 2,435,335 1

Hash Left Join (cost=39,861.94..523,000.64 rows=6,225 width=96) (actual time=1,439.446..99,574.849 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = norig2.sample_id)
5. 1,562.924 97,965.711 ↓ 391.2 2,435,335 1

Hash Left Join (cost=36,807.27..519,929.63 rows=6,225 width=92) (actual time=1,403.369..97,965.711 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = norig.sample_id)
6. 1,639.341 96,367.099 ↓ 391.2 2,435,335 1

Hash Left Join (cost=33,752.60..516,858.62 rows=6,225 width=88) (actual time=1,365.601..96,367.099 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = nin.sample_id)
7. 2,268.153 94,688.967 ↓ 391.2 2,435,335 1

Nested Loop Left Join (cost=31,061.19..514,150.87 rows=6,225 width=84) (actual time=1,324.970..94,688.967 rows=2,435,335 loops=1)

8. 2,125.743 85,114.809 ↓ 391.2 2,435,335 1

Nested Loop Left Join (cost=31,060.75..463,426.50 rows=6,225 width=80) (actual time=1,323.653..85,114.809 rows=2,435,335 loops=1)

9. 2,054.453 75,683.061 ↓ 391.2 2,435,335 1

Nested Loop Left Join (cost=31,060.31..412,702.12 rows=6,225 width=76) (actual time=1,322.673..75,683.061 rows=2,435,335 loops=1)

10. 2,041.302 66,322.603 ↓ 391.2 2,435,335 1

Nested Loop Left Join (cost=31,059.87..361,973.75 rows=6,225 width=72) (actual time=1,321.727..66,322.603 rows=2,435,335 loops=1)

11. 370.186 56,975.296 ↓ 391.2 2,435,335 1

Nested Loop Left Join (cost=31,059.44..311,245.37 rows=6,225 width=68) (actual time=1,320.722..56,975.296 rows=2,435,335 loops=1)

12. 1,104.433 39,557.765 ↓ 391.2 2,435,335 1

Nested Loop Left Join (cost=31,058.87..258,867.31 rows=6,225 width=64) (actual time=1,319.708..39,557.765 rows=2,435,335 loops=1)

13. 1,519.140 21,405.987 ↓ 391.2 2,435,335 1

Hash Left Join (cost=31,058.31..206,489.25 rows=6,225 width=60) (actual time=1,312.202..21,405.987 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = inefdeny.sample_id)
14. 1,455.810 19,851.010 ↓ 391.2 2,435,335 1

Hash Left Join (cost=28,596.57..204,011.17 rows=6,225 width=56) (actual time=1,273.900..19,851.010 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = ccbovfl.sample_id)
15. 1,468.371 18,360.949 ↓ 391.2 2,435,335 1

Hash Left Join (cost=26,139.81..201,538.07 rows=6,225 width=52) (actual time=1,236.832..18,360.949 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = cploovfl.sample_id)
16. 1,494.478 16,856.111 ↓ 391.2 2,435,335 1

Hash Left Join (cost=23,683.05..199,064.97 rows=6,225 width=48) (actual time=1,198.413..16,856.111 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = origdeny.sample_id)
17. 1,446.211 15,324.715 ↓ 391.2 2,435,335 1

Hash Left Join (cost=21,226.29..196,591.87 rows=6,225 width=44) (actual time=1,159.769..15,324.715 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = tcmvact.sample_id)
18. 1,376.145 13,841.395 ↓ 391.2 2,435,335 1

Hash Left Join (cost=18,874.95..194,224.18 rows=6,225 width=40) (actual time=1,120.703..13,841.395 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = trsgnct.sample_id)
19. 1,398.795 12,430.405 ↓ 391.2 2,435,335 1

Hash Left Join (cost=15,966.32..191,299.21 rows=6,225 width=36) (actual time=1,083.126..12,430.405 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = trsncrt.sample_id)
20. 1,398.294 10,994.714 ↓ 391.2 2,435,335 1

Hash Left Join (cost=13,051.60..188,368.16 rows=6,225 width=32) (actual time=1,044.378..10,994.714 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = cintegfl.sample_id)
21. 1,395.105 9,560.869 ↓ 391.2 2,435,335 1

Hash Left Join (cost=9,960.42..185,260.64 rows=6,225 width=28) (actual time=1,005.801..9,560.869 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = trmmfl.sample_id)
22. 1,408.555 8,131.797 ↓ 391.2 2,435,335 1

Hash Left Join (cost=6,905.75..182,189.62 rows=6,225 width=24) (actual time=971.000..8,131.797 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = inlkt.sample_id)
23. 1,393.185 6,692.647 ↓ 391.2 2,435,335 1

Hash Left Join (cost=3,832.83..179,100.36 rows=6,225 width=20) (actual time=939.594..6,692.647 rows=2,435,335 loops=1)

  • Hash Cond: (s.id = origlkt.sample_id)
24. 283.651 5,267.433 ↓ 391.2 2,435,335 1

Append (cost=778.16..176,029.35 rows=6,225 width=16) (actual time=906.555..5,267.433 rows=2,435,335 loops=1)

  • Subplans Removed: 3
25. 2,158.838 3,063.387 ↓ 104.8 528,320 1

Bitmap Heap Scan on sample_2019_08_alvjcabb00t s (cost=21,826.72..87,881.44 rows=5,041 width=16) (actual time=906.553..3,063.387 rows=528,320 loops=1)

  • Recheck Cond: (sample_hour >= (now() - '30 days'::interval))
  • Filter: ((switch_name)::text = 'ALVJCABB00T'::text)
  • Heap Blocks: exact=8,368
26. 904.549 904.549 ↑ 1.9 528,320 1

Bitmap Index Scan on sample_2019_08_alvjcabb00t_sample_hour_idx (cost=0.00..21,825.45 rows=1,008,136 width=0) (actual time=904.549..904.549 rows=528,320 loops=1)

  • Index Cond: (sample_hour >= (now() - '30 days'::interval))
27. 1,920.395 1,920.395 ↓ 5,642.1 1,907,015 1

Seq Scan on sample_2019_09_alvjcabb00t s_1 (cost=0.00..32,995.02 rows=338 width=16) (actual time=0.189..1,920.395 rows=1,907,015 loops=1)

  • Filter: (((switch_name)::text = 'ALVJCABB00T'::text) AND (sample_hour >= (now() - '30 days'::interval)))
28. 16.113 32.029 ↑ 1.2 94,948 1

Hash (cost=1,636.52..1,636.52 rows=113,452 width=8) (actual time=32.028..32.029 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
29. 15.916 15.916 ↑ 1.2 94,948 1

Seq Scan on reg_origlkt origlkt (cost=0.00..1,636.52 rows=113,452 width=8) (actual time=0.040..15.916 rows=94,948 loops=1)

30. 15.232 30.595 ↑ 1.2 94,948 1

Hash (cost=1,646.30..1,646.30 rows=114,130 width=8) (actual time=30.595..30.595 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
31. 15.363 15.363 ↑ 1.2 94,948 1

Seq Scan on reg_inlkt inlkt (cost=0.00..1,646.30 rows=114,130 width=8) (actual time=0.039..15.363 rows=94,948 loops=1)

32. 17.119 33.967 ↑ 1.2 94,948 1

Hash (cost=1,636.52..1,636.52 rows=113,452 width=8) (actual time=33.967..33.967 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
33. 16.848 16.848 ↑ 1.2 94,948 1

Seq Scan on reg_trmmfl trmmfl (cost=0.00..1,636.52 rows=113,452 width=8) (actual time=0.021..16.848 rows=94,948 loops=1)

34. 20.126 35.551 ↑ 1.2 94,954 1

Hash (cost=1,656.08..1,656.08 rows=114,808 width=8) (actual time=35.551..35.551 rows=94,954 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
35. 15.425 15.425 ↑ 1.2 94,954 1

Seq Scan on reg_cintegfl cintegfl (cost=0.00..1,656.08 rows=114,808 width=8) (actual time=0.028..15.425 rows=94,954 loops=1)

36. 21.431 36.896 ↑ 1.2 89,387 1

Hash (cost=1,561.54..1,561.54 rows=108,254 width=8) (actual time=36.896..36.896 rows=89,387 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,516kB
37. 15.465 15.465 ↑ 1.2 89,387 1

Seq Scan on reg_trsncrt trsncrt (cost=0.00..1,561.54 rows=108,254 width=8) (actual time=0.035..15.465 rows=89,387 loops=1)

38. 20.180 34.845 ↑ 1.2 89,387 1

Hash (cost=1,558.28..1,558.28 rows=108,028 width=8) (actual time=34.844..34.845 rows=89,387 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,516kB
39. 14.665 14.665 ↑ 1.2 89,387 1

Seq Scan on reg_trsgnct trsgnct (cost=0.00..1,558.28 rows=108,028 width=8) (actual time=0.027..14.665 rows=89,387 loops=1)

40. 22.554 37.109 ↓ 1.1 93,148 1

Hash (cost=1,301.71..1,301.71 rows=83,971 width=8) (actual time=37.109..37.109 rows=93,148 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,663kB
41. 14.555 14.555 ↓ 1.1 93,148 1

Seq Scan on reg_tcmvact tcmvact (cost=0.00..1,301.71 rows=83,971 width=8) (actual time=0.033..14.555 rows=93,148 loops=1)

42. 22.144 36.918 ↓ 1.1 94,962 1

Hash (cost=1,333.56..1,333.56 rows=89,856 width=8) (actual time=36.918..36.918 rows=94,962 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
43. 14.774 14.774 ↓ 1.1 94,962 1

Seq Scan on reg_origdeny origdeny (cost=0.00..1,333.56 rows=89,856 width=8) (actual time=0.027..14.774 rows=94,962 loops=1)

44. 21.651 36.467 ↓ 1.1 94,962 1

Hash (cost=1,333.56..1,333.56 rows=89,856 width=8) (actual time=36.467..36.467 rows=94,962 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
45. 14.816 14.816 ↓ 1.1 94,962 1

Seq Scan on reg_cploovfl cploovfl (cost=0.00..1,333.56 rows=89,856 width=8) (actual time=0.027..14.816 rows=94,962 loops=1)

46. 20.894 34.251 ↓ 1.1 94,962 1

Hash (cost=1,333.56..1,333.56 rows=89,856 width=8) (actual time=34.251..34.251 rows=94,962 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
47. 13.357 13.357 ↓ 1.1 94,962 1

Seq Scan on reg_ccbovfl ccbovfl (cost=0.00..1,333.56 rows=89,856 width=8) (actual time=0.026..13.357 rows=94,962 loops=1)

48. 21.970 35.837 ↓ 1.1 94,962 1

Hash (cost=1,335.77..1,335.77 rows=90,077 width=8) (actual time=35.837..35.837 rows=94,962 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
49. 13.867 13.867 ↓ 1.1 94,962 1

Seq Scan on reg_inefdeny inefdeny (cost=0.00..1,335.77 rows=90,077 width=8) (actual time=0.023..13.867 rows=94,962 loops=1)

50. 17,047.345 17,047.345 ↓ 0.0 0 2,435,335

Index Scan using reg_infail_pkey on reg_infail infail (cost=0.56..8.41 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=2,435,335)

  • Index Cond: (s.id = sample_id)
51. 17,047.345 17,047.345 ↓ 0.0 0 2,435,335

Index Scan using reg_outfail_pkey on reg_outfail outfail (cost=0.56..8.41 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=2,435,335)

  • Index Cond: (s.id = sample_id)
52. 7,306.005 7,306.005 ↓ 0.0 0 2,435,335

Index Scan using reg_perclfl_pkey on reg_perclfl perclfl (cost=0.44..8.15 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,435,335)

  • Index Cond: (s.id = sample_id)
53. 7,306.005 7,306.005 ↓ 0.0 0 2,435,335

Index Scan using reg_origfail_pkey on reg_origfail origfail (cost=0.44..8.15 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,435,335)

  • Index Cond: (s.id = sample_id)
54. 7,306.005 7,306.005 ↓ 0.0 0 2,435,335

Index Scan using reg_origblk_pkey on reg_origblk origblk (cost=0.44..8.15 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,435,335)

  • Index Cond: (s.id = sample_id)
55. 7,306.005 7,306.005 ↓ 0.0 0 2,435,335

Index Scan using reg_termblk_pkey on reg_termblk termblk (cost=0.44..8.15 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,435,335)

  • Index Cond: (s.id = sample_id)
56. 24.398 38.791 ↑ 1.0 94,948 1

Hash (cost=1,482.85..1,482.85 rows=96,685 width=8) (actual time=38.791..38.791 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
57. 14.393 14.393 ↑ 1.0 94,948 1

Seq Scan on reg_nin nin (cost=0.00..1,482.85 rows=96,685 width=8) (actual time=0.026..14.393 rows=94,948 loops=1)

58. 21.375 35.688 ↑ 1.2 94,948 1

Hash (cost=1,636.52..1,636.52 rows=113,452 width=8) (actual time=35.687..35.688 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
59. 14.313 14.313 ↑ 1.2 94,948 1

Seq Scan on reg_norig norig (cost=0.00..1,636.52 rows=113,452 width=8) (actual time=0.021..14.313 rows=94,948 loops=1)

60. 20.743 34.072 ↑ 1.2 94,948 1

Hash (cost=1,636.52..1,636.52 rows=113,452 width=8) (actual time=34.072..34.072 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
61. 13.329 13.329 ↑ 1.2 94,948 1

Seq Scan on reg_norig2 norig2 (cost=0.00..1,636.52 rows=113,452 width=8) (actual time=0.020..13.329 rows=94,948 loops=1)

62. 15.373 29.991 ↑ 1.0 94,948 1

Hash (cost=1,482.90..1,482.90 rows=96,690 width=8) (actual time=29.991..29.991 rows=94,948 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
63. 14.618 14.618 ↑ 1.0 94,948 1

Seq Scan on reg_nin2 nin2 (cost=0.00..1,482.90 rows=96,690 width=8) (actual time=0.018..14.618 rows=94,948 loops=1)