explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DoCy

Settings
# exclusive inclusive rows x rows loops node
1. 131.806 20,318.474 ↑ 3.6 56 1

GroupAggregate (cost=1,192,141.07..1,193,181.33 rows=200 width=48) (actual time=20,148.967..20,318.474 rows=56 loops=1)

  • Group Key: s.sample_hour
2. 416.155 20,186.668 ↓ 21.6 385,047 1

Sort (cost=1,192,141.07..1,192,185.69 rows=17,848 width=92) (actual time=20,146.123..20,186.668 rows=385,047 loops=1)

  • Sort Key: s.sample_hour
  • Sort Method: quicksort Memory: 32,727kB
3. 466.383 19,770.513 ↓ 21.6 385,047 1

Hash Left Join (cost=40,075.27..1,190,880.70 rows=17,848 width=92) (actual time=2,147.217..19,770.513 rows=385,047 loops=1)

  • Hash Cond: (s.id = nin2.sample_id)
4. 434.648 19,149.027 ↓ 21.6 385,047 1

Hash Left Join (cost=37,399.41..1,188,157.99 rows=17,848 width=96) (actual time=1,985.998..19,149.027 rows=385,047 loops=1)

  • Hash Cond: (s.id = norig2.sample_id)
5. 453.684 18,604.671 ↓ 21.6 385,047 1

Hash Left Join (cost=34,466.44..1,185,178.17 rows=17,848 width=92) (actual time=1,864.723..18,604.671 rows=385,047 loops=1)

  • Hash Cond: (s.id = norig.sample_id)
6. 453.571 18,065.874 ↓ 21.6 385,047 1

Hash Left Join (cost=31,533.47..1,182,198.35 rows=17,848 width=88) (actual time=1,777.246..18,065.874 rows=385,047 loops=1)

  • Hash Cond: (s.id = nin.sample_id)
7. 204.466 17,508.960 ↓ 21.6 385,047 1

Nested Loop Left Join (cost=28,857.70..1,179,475.73 rows=17,848 width=84) (actual time=1,666.362..17,508.960 rows=385,047 loops=1)

8. 156.775 16,149.353 ↓ 21.6 385,047 1

Nested Loop Left Join (cost=28,857.26..1,043,922.89 rows=17,848 width=80) (actual time=1,666.234..16,149.353 rows=385,047 loops=1)

9. 240.623 14,837.437 ↓ 21.6 385,047 1

Nested Loop Left Join (cost=28,856.83..908,370.05 rows=17,848 width=76) (actual time=1,666.123..14,837.437 rows=385,047 loops=1)

10. 178.931 13,441.673 ↓ 21.6 385,047 1

Nested Loop Left Join (cost=28,856.39..772,813.21 rows=17,848 width=72) (actual time=1,665.974..13,441.673 rows=385,047 loops=1)

11. 369.676 11,722.554 ↓ 21.6 385,047 1

Nested Loop Left Join (cost=28,855.95..637,256.37 rows=17,848 width=68) (actual time=1,665.866..11,722.554 rows=385,047 loops=1)

12. 327.720 9,812.690 ↓ 21.6 385,047 1

Nested Loop Left Join (cost=28,855.39..492,927.91 rows=17,848 width=64) (actual time=1,665.655..9,812.690 rows=385,047 loops=1)

13. 431.107 7,174.688 ↓ 21.6 385,047 1

Hash Left Join (cost=28,854.82..348,599.45 rows=17,848 width=60) (actual time=1,660.131..7,174.688 rows=385,047 loops=1)

  • Hash Cond: (s.id = inefdeny.sample_id)
14. 427.265 6,589.425 ↓ 21.6 385,047 1

Hash Left Join (cost=26,545.89..346,243.66 rows=17,848 width=56) (actual time=1,504.972..6,589.425 rows=385,047 loops=1)

  • Hash Cond: (s.id = ccbovfl.sample_id)
15. 417.874 6,044.571 ↓ 21.6 385,047 1

Hash Left Join (cost=24,235.98..343,886.90 rows=17,848 width=52) (actual time=1,382.832..6,044.571 rows=385,047 loops=1)

  • Hash Cond: (s.id = cploovfl.sample_id)
16. 417.013 5,497.872 ↓ 21.6 385,047 1

Hash Left Join (cost=21,926.06..341,530.13 rows=17,848 width=48) (actual time=1,250.348..5,497.872 rows=385,047 loops=1)

  • Hash Cond: (s.id = origdeny.sample_id)
17. 446.305 4,986.560 ↓ 21.6 385,047 1

Hash Left Join (cost=19,616.15..339,173.37 rows=17,848 width=44) (actual time=1,153.589..4,986.560 rows=385,047 loops=1)

  • Hash Cond: (s.id = tcmvact.sample_id)
18. 405.415 4,445.719 ↓ 21.6 385,047 1

Hash Left Join (cost=17,366.59..336,876.96 rows=17,848 width=40) (actual time=1,054.288..4,445.719 rows=385,047 loops=1)

  • Hash Cond: (s.id = trsgnct.sample_id)
19. 384.990 3,907.637 ↓ 21.6 385,047 1

Hash Left Join (cost=14,579.66..334,043.18 rows=17,848 width=36) (actual time=918.266..3,907.637 rows=385,047 loops=1)

  • Hash Cond: (s.id = trsncrt.sample_id)
20. 408.460 3,400.026 ↓ 21.6 385,047 1

Hash Left Join (cost=11,786.65..331,203.31 rows=17,848 width=32) (actual time=792.654..3,400.026 rows=385,047 loops=1)

  • Hash Cond: (s.id = cintegfl.sample_id)
21. 399.490 2,868.544 ↓ 21.6 385,047 1

Hash Left Join (cost=8,817.17..328,186.98 rows=17,848 width=28) (actual time=667.438..2,868.544 rows=385,047 loops=1)

  • Hash Cond: (s.id = trmmfl.sample_id)
22. 424.986 2,354.103 ↓ 21.6 385,047 1

Hash Left Join (cost=5,884.20..325,207.16 rows=17,848 width=24) (actual time=550.265..2,354.103 rows=385,047 loops=1)

  • Hash Cond: (s.id = inlkt.sample_id)
23. 415.326 1,789.965 ↓ 21.6 385,047 1

Hash Left Join (cost=2,932.97..322,209.08 rows=17,848 width=20) (actual time=398.901..1,789.965 rows=385,047 loops=1)

  • Hash Cond: (s.id = origlkt.sample_id)
24. 50.637 1,254.905 ↓ 21.6 385,047 1

Append (cost=0.00..319,229.26 rows=17,848 width=16) (actual time=277.206..1,254.905 rows=385,047 loops=1)

  • Subplans Removed: 4
25. 928.549 1,204.268 ↓ 70.6 385,047 1

Bitmap Heap Scan on sample_2019_09_omajnevqds0 s (cost=23,506.84..96,504.28 rows=5,451 width=16) (actual time=277.204..1,204.268 rows=385,047 loops=1)

  • Recheck Cond: (sample_hour >= (now() - '7 days'::interval))
  • Filter: ((switch_name)::text = 'OMAJNEVQDS0'::text)
  • Heap Blocks: exact=6,247
26. 275.719 275.719 ↑ 2.8 385,047 1

Bitmap Index Scan on sample_2019_09_omajnevqds0_sample_hour_idx (cost=0.00..23,505.47 rows=1,090,272 width=0) (actual time=275.719..275.719 rows=385,047 loops=1)

  • Index Cond: (sample_hour >= (now() - '7 days'::interval))
27. 50.281 119.734 ↑ 1.2 90,783 1

Hash (cost=1,571.32..1,571.32 rows=108,932 width=8) (actual time=119.733..119.734 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
28. 69.453 69.453 ↑ 1.2 90,783 1

Seq Scan on reg_origlkt origlkt (cost=0.00..1,571.32 rows=108,932 width=8) (actual time=0.103..69.453 rows=90,783 loops=1)

29. 68.006 139.152 ↑ 1.2 90,783 1

Hash (cost=1,581.10..1,581.10 rows=109,610 width=8) (actual time=139.151..139.152 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
30. 71.146 71.146 ↑ 1.2 90,783 1

Seq Scan on reg_inlkt inlkt (cost=0.00..1,581.10 rows=109,610 width=8) (actual time=0.190..71.146 rows=90,783 loops=1)

31. 42.796 114.951 ↑ 1.2 90,783 1

Hash (cost=1,571.32..1,571.32 rows=108,932 width=8) (actual time=114.951..114.951 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
32. 72.155 72.155 ↑ 1.2 90,783 1

Seq Scan on reg_trmmfl trmmfl (cost=0.00..1,571.32 rows=108,932 width=8) (actual time=0.117..72.155 rows=90,783 loops=1)

33. 48.089 123.022 ↑ 1.2 90,791 1

Hash (cost=1,590.88..1,590.88 rows=110,288 width=8) (actual time=123.022..123.022 rows=90,791 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
34. 74.933 74.933 ↑ 1.2 90,791 1

Seq Scan on reg_cintegfl cintegfl (cost=0.00..1,590.88 rows=110,288 width=8) (actual time=0.078..74.933 rows=90,791 loops=1)

35. 62.797 122.621 ↑ 1.2 85,223 1

Hash (cost=1,496.34..1,496.34 rows=103,734 width=8) (actual time=122.620..122.621 rows=85,223 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,354kB
36. 59.824 59.824 ↑ 1.2 85,223 1

Seq Scan on reg_trsncrt trsncrt (cost=0.00..1,496.34 rows=103,734 width=8) (actual time=0.152..59.824 rows=85,223 loops=1)

37. 72.844 132.667 ↑ 1.2 85,223 1

Hash (cost=1,493.08..1,493.08 rows=103,508 width=8) (actual time=132.667..132.667 rows=85,223 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,354kB
38. 59.823 59.823 ↑ 1.2 85,223 1

Seq Scan on reg_trsgnct trsgnct (cost=0.00..1,493.08 rows=103,508 width=8) (actual time=0.086..59.823 rows=85,223 loops=1)

39. 50.849 94.536 ↓ 1.1 88,985 1

Hash (cost=1,245.36..1,245.36 rows=80,336 width=8) (actual time=94.536..94.536 rows=88,985 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,500kB
40. 43.687 43.687 ↓ 1.1 88,985 1

Seq Scan on reg_tcmvact tcmvact (cost=0.00..1,245.36 rows=80,336 width=8) (actual time=0.082..43.687 rows=88,985 loops=1)

41. 51.473 94.299 ↓ 1.1 90,797 1

Hash (cost=1,253.85..1,253.85 rows=84,485 width=8) (actual time=94.299..94.299 rows=90,797 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
42. 42.826 42.826 ↓ 1.1 90,797 1

Seq Scan on reg_origdeny origdeny (cost=0.00..1,253.85 rows=84,485 width=8) (actual time=0.067..42.826 rows=90,797 loops=1)

43. 56.485 128.825 ↓ 1.1 90,797 1

Hash (cost=1,253.85..1,253.85 rows=84,485 width=8) (actual time=128.825..128.825 rows=90,797 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
44. 72.340 72.340 ↓ 1.1 90,797 1

Seq Scan on reg_cploovfl cploovfl (cost=0.00..1,253.85 rows=84,485 width=8) (actual time=0.052..72.340 rows=90,797 loops=1)

45. 47.609 117.589 ↓ 1.1 90,797 1

Hash (cost=1,253.85..1,253.85 rows=84,485 width=8) (actual time=117.589..117.589 rows=90,797 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
46. 69.980 69.980 ↓ 1.1 90,797 1

Seq Scan on reg_ccbovfl ccbovfl (cost=0.00..1,253.85 rows=84,485 width=8) (actual time=0.115..69.980 rows=90,797 loops=1)

47. 67.643 154.156 ↓ 1.1 90,797 1

Hash (cost=1,252.86..1,252.86 rows=84,486 width=8) (actual time=154.156..154.156 rows=90,797 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
48. 86.513 86.513 ↓ 1.1 90,797 1

Seq Scan on reg_inefdeny inefdeny (cost=0.00..1,252.86 rows=84,486 width=8) (actual time=0.061..86.513 rows=90,797 loops=1)

49. 2,310.282 2,310.282 ↓ 0.0 0 385,047

Index Scan using reg_infail_pkey on reg_infail infail (cost=0.56..8.09 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=385,047)

  • Index Cond: (s.id = sample_id)
50. 1,540.188 1,540.188 ↓ 0.0 0 385,047

Index Scan using reg_outfail_pkey on reg_outfail outfail (cost=0.56..8.09 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=385,047)

  • Index Cond: (s.id = sample_id)
51. 1,540.188 1,540.188 ↓ 0.0 0 385,047

Index Scan using reg_perclfl_pkey on reg_perclfl perclfl (cost=0.44..7.60 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=385,047)

  • Index Cond: (s.id = sample_id)
52. 1,155.141 1,155.141 ↓ 0.0 0 385,047

Index Scan using reg_origfail_pkey on reg_origfail origfail (cost=0.44..7.60 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=385,047)

  • Index Cond: (s.id = sample_id)
53. 1,155.141 1,155.141 ↓ 0.0 0 385,047

Index Scan using reg_origblk_pkey on reg_origblk origblk (cost=0.44..7.59 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=385,047)

  • Index Cond: (s.id = sample_id)
54. 1,155.141 1,155.141 ↓ 0.0 0 385,047

Index Scan using reg_termblk_pkey on reg_termblk termblk (cost=0.44..7.59 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=385,047)

  • Index Cond: (s.id = sample_id)
55. 67.151 103.343 ↑ 1.1 90,783 1

Hash (cost=1,474.23..1,474.23 rows=96,123 width=8) (actual time=103.343..103.343 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
56. 36.192 36.192 ↑ 1.1 90,783 1

Seq Scan on reg_nin nin (cost=0.00..1,474.23 rows=96,123 width=8) (actual time=0.084..36.192 rows=90,783 loops=1)

57. 35.876 85.113 ↑ 1.2 90,783 1

Hash (cost=1,571.32..1,571.32 rows=108,932 width=8) (actual time=85.113..85.113 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
58. 49.237 49.237 ↑ 1.2 90,783 1

Seq Scan on reg_norig norig (cost=0.00..1,571.32 rows=108,932 width=8) (actual time=0.081..49.237 rows=90,783 loops=1)

59. 50.232 109.708 ↑ 1.2 90,783 1

Hash (cost=1,571.32..1,571.32 rows=108,932 width=8) (actual time=109.708..109.708 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
60. 59.476 59.476 ↑ 1.2 90,783 1

Seq Scan on reg_norig2 norig2 (cost=0.00..1,571.32 rows=108,932 width=8) (actual time=0.064..59.476 rows=90,783 loops=1)

61. 84.593 155.103 ↑ 1.1 90,783 1

Hash (cost=1,474.27..1,474.27 rows=96,127 width=8) (actual time=155.102..155.103 rows=90,783 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,571kB
62. 70.510 70.510 ↑ 1.1 90,783 1

Seq Scan on reg_nin2 nin2 (cost=0.00..1,474.27 rows=96,127 width=8) (actual time=0.114..70.510 rows=90,783 loops=1)