explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bbiu

Settings
# exclusive inclusive rows x rows loops node
1. 36.103 3,131.028 ↑ 3.8 52 1

GroupAggregate (cost=386,561.05..386,856.74 rows=200 width=48) (actual time=3,088.507..3,131.028 rows=52 loops=1)

  • Group Key: s.sample_hour
2. 60.754 3,094.925 ↓ 23.4 114,736 1

Sort (cost=386,561.05..386,573.30 rows=4,899 width=92) (actual time=3,087.608..3,094.925 rows=114,736 loops=1)

  • Sort Key: s.sample_hour
  • Sort Method: quicksort Memory: 9,757kB
3. 63.923 3,034.171 ↓ 23.4 114,736 1

Hash Left Join (cost=42,171.17..386,260.78 rows=4,899 width=92) (actual time=516.381..3,034.171 rows=114,736 loops=1)

  • Hash Cond: (s.id = nin2.sample_id)
4. 65.510 2,938.895 ↓ 23.4 114,736 1

Hash Left Join (cost=39,479.64..383,556.40 rows=4,899 width=96) (actual time=484.183..2,938.895 rows=114,736 loops=1)

  • Hash Cond: (s.id = norig2.sample_id)
5. 64.939 2,841.867 ↓ 23.4 114,736 1

Hash Left Join (cost=36,424.97..380,488.87 rows=4,899 width=92) (actual time=451.713..2,841.867 rows=114,736 loops=1)

  • Hash Cond: (s.id = norig.sample_id)
6. 68.947 2,744.942 ↓ 23.4 114,736 1

Hash Left Join (cost=33,370.30..377,421.34 rows=4,899 width=88) (actual time=418.837..2,744.942 rows=114,736 loops=1)

  • Hash Cond: (s.id = nin.sample_id)
7. 0.107 2,643.352 ↓ 23.4 114,736 1

Nested Loop Left Join (cost=30,678.89..374,717.06 rows=4,899 width=84) (actual time=385.215..2,643.352 rows=114,736 loops=1)

8. 4.874 2,413.773 ↓ 23.4 114,736 1

Nested Loop Left Join (cost=30,678.45..334,484.02 rows=4,899 width=80) (actual time=385.177..2,413.773 rows=114,736 loops=1)

9. 0.000 2,179.427 ↓ 23.4 114,736 1

Nested Loop Left Join (cost=30,678.01..294,250.97 rows=4,899 width=76) (actual time=385.131..2,179.427 rows=114,736 loops=1)

10. 16.315 1,962.877 ↓ 23.4 114,736 1

Nested Loop Left Join (cost=30,677.58..254,017.93 rows=4,899 width=72) (actual time=385.092..1,962.877 rows=114,736 loops=1)

11. 51.130 1,717.090 ↓ 23.4 114,736 1

Nested Loop Left Join (cost=30,677.14..213,784.88 rows=4,899 width=68) (actual time=385.051..1,717.090 rows=114,736 loops=1)

12. 71.611 1,436.488 ↓ 23.4 114,736 1

Nested Loop Left Join (cost=30,676.57..172,387.22 rows=4,899 width=64) (actual time=385.019..1,436.488 rows=114,736 loops=1)

13. 65.431 1,135.405 ↓ 23.4 114,736 1

Hash Left Join (cost=30,676.01..130,989.55 rows=4,899 width=60) (actual time=384.901..1,135.405 rows=114,736 loops=1)

  • Hash Cond: (s.id = inefdeny.sample_id)
14. 63.261 1,038.574 ↓ 23.4 114,736 1

Hash Left Join (cost=28,214.28..128,514.96 rows=4,899 width=56) (actual time=351.322..1,038.574 rows=114,736 loops=1)

  • Hash Cond: (s.id = ccbovfl.sample_id)
15. 61.697 938.663 ↓ 23.4 114,736 1

Hash Left Join (cost=25,757.51..126,045.34 rows=4,899 width=52) (actual time=313.721..938.663 rows=114,736 loops=1)

  • Hash Cond: (s.id = cploovfl.sample_id)
16. 66.507 841.786 ↓ 23.4 114,736 1

Hash Left Join (cost=23,300.75..123,575.72 rows=4,899 width=48) (actual time=277.580..841.786 rows=114,736 loops=1)

  • Hash Cond: (s.id = origdeny.sample_id)
17. 63.096 742.371 ↓ 23.4 114,736 1

Hash Left Join (cost=20,843.99..121,106.10 rows=4,899 width=44) (actual time=242.430..742.371 rows=114,736 loops=1)

  • Hash Cond: (s.id = tcmvact.sample_id)
18. 64.163 647.278 ↓ 23.4 114,736 1

Hash Left Join (cost=18,492.65..118,741.89 rows=4,899 width=40) (actual time=209.598..647.278 rows=114,736 loops=1)

  • Hash Cond: (s.id = trsgnct.sample_id)
19. 61.861 551.886 ↓ 23.4 114,736 1

Hash Left Join (cost=15,584.02..115,820.40 rows=4,899 width=36) (actual time=177.583..551.886 rows=114,736 loops=1)

  • Hash Cond: (s.id = trsncrt.sample_id)
20. 63.999 460.933 ↓ 23.4 114,736 1

Hash Left Join (cost=12,669.30..112,892.83 rows=4,899 width=32) (actual time=147.654..460.933 rows=114,736 loops=1)

  • Hash Cond: (s.id = cintegfl.sample_id)
21. 63.142 364.581 ↓ 23.4 114,736 1

Hash Left Join (cost=9,578.12..109,788.79 rows=4,899 width=28) (actual time=114.453..364.581 rows=114,736 loops=1)

  • Hash Cond: (s.id = trmmfl.sample_id)
22. 62.430 268.574 ↓ 23.4 114,736 1

Hash Left Join (cost=6,523.45..106,721.26 rows=4,899 width=24) (actual time=80.781..268.574 rows=114,736 loops=1)

  • Hash Cond: (s.id = inlkt.sample_id)
23. 61.845 175.631 ↓ 23.4 114,736 1

Hash Left Join (cost=3,450.53..103,635.47 rows=4,899 width=20) (actual time=49.389..175.631 rows=114,736 loops=1)

  • Hash Cond: (s.id = origlkt.sample_id)
24. 12.058 82.518 ↓ 23.4 114,736 1

Append (cost=395.86..100,567.94 rows=4,899 width=16) (actual time=17.239..82.518 rows=114,736 loops=1)

  • Subplans Removed: 4
25. 53.421 70.460 ↓ 66.2 114,736 1

Bitmap Heap Scan on sample_2019_09_hnsonv37ds0 s (cost=7,581.36..28,549.02 rows=1,734 width=16) (actual time=17.238..70.460 rows=114,736 loops=1)

  • Recheck Cond: (sample_hour >= (now() - '7 days'::interval))
  • Filter: ((switch_name)::text = 'HNSONV37DS0'::text)
  • Heap Blocks: exact=1,636
26. 17.039 17.039 ↑ 3.0 114,736 1

Bitmap Index Scan on sample_2019_09_hnsonv37ds0_sample_hour_idx (cost=0.00..7,580.93 rows=346,733 width=0) (actual time=17.039..17.039 rows=114,736 loops=1)

  • Index Cond: (sample_hour >= (now() - '7 days'::interval))
27. 15.987 31.268 ↑ 1.2 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
28. 15.281 15.281 ↑ 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.025..15.281 rows=94,948 loops=1)

29. 15.418 30.513 ↑ 1.2 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
30. 15.095 15.095 ↑ 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.023..15.095 rows=94,948 loops=1)

31. 17.067 32.865 ↑ 1.2 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
32. 15.798 15.798 ↑ 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.024..15.798 rows=94,948 loops=1)

33. 16.271 32.353 ↑ 1.2 94,954 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
34. 16.082 16.082 ↑ 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.034..16.082 rows=94,954 loops=1)

35. 14.546 29.092 ↑ 1.2 89,387 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,516kB
36. 14.546 14.546 ↑ 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.024..14.546 rows=89,387 loops=1)

37. 15.416 31.229 ↑ 1.2 89,387 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,516kB
38. 15.813 15.813 ↑ 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.026..15.813 rows=89,387 loops=1)

39. 16.281 31.997 ↓ 1.1 93,148 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,663kB
40. 15.716 15.716 ↓ 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.021..15.716 rows=93,148 loops=1)

41. 17.666 32.908 ↓ 1.1 94,962 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
42. 15.242 15.242 ↓ 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.026..15.242 rows=94,962 loops=1)

43. 18.662 35.180 ↓ 1.1 94,962 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
44. 16.518 16.518 ↓ 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.026..16.518 rows=94,962 loops=1)

45. 19.472 36.650 ↓ 1.1 94,962 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
46. 17.178 17.178 ↓ 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.027..17.178 rows=94,962 loops=1)

47. 16.778 31.400 ↓ 1.1 94,962 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,734kB
48. 14.622 14.622 ↓ 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.029..14.622 rows=94,962 loops=1)

49. 229.472 229.472 ↓ 0.0 0 114,736

Index Scan using reg_infail_pkey on reg_infail infail (cost=0.56..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=114,736)

  • Index Cond: (s.id = sample_id)
50. 229.472 229.472 ↓ 0.0 0 114,736

Index Scan using reg_outfail_pkey on reg_outfail outfail (cost=0.56..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=114,736)

  • Index Cond: (s.id = sample_id)
51. 229.472 229.472 ↓ 0.0 0 114,736

Index Scan using reg_perclfl_pkey on reg_perclfl perclfl (cost=0.44..8.21 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=114,736)

  • Index Cond: (s.id = sample_id)
52. 229.472 229.472 ↓ 0.0 0 114,736

Index Scan using reg_origfail_pkey on reg_origfail origfail (cost=0.44..8.21 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=114,736)

  • Index Cond: (s.id = sample_id)
53. 229.472 229.472 ↓ 0.0 0 114,736

Index Scan using reg_origblk_pkey on reg_origblk origblk (cost=0.44..8.21 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=114,736)

  • Index Cond: (s.id = sample_id)
54. 229.472 229.472 ↓ 0.0 0 114,736

Index Scan using reg_termblk_pkey on reg_termblk termblk (cost=0.44..8.21 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=114,736)

  • Index Cond: (s.id = sample_id)
55. 16.289 32.643 ↑ 1.0 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
56. 16.354 16.354 ↑ 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.032..16.354 rows=94,948 loops=1)

57. 16.419 31.986 ↑ 1.2 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
58. 15.567 15.567 ↑ 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.026..15.567 rows=94,948 loops=1)

59. 15.963 31.518 ↑ 1.2 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
60. 15.555 15.555 ↑ 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.025..15.555 rows=94,948 loops=1)

61. 16.153 31.353 ↑ 1.0 94,948 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,733kB
62. 15.200 15.200 ↑ 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.024..15.200 rows=94,948 loops=1)