explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iKyl

Settings
# exclusive inclusive rows x rows loops node
1. 1.550 937.682 ↑ 1.0 1 1

Aggregate (cost=206,488.60..206,488.61 rows=1 width=8) (actual time=937.682..937.682 rows=1 loops=1)

  • Execution Time: 946.399 ms(107 строк)
2. 3.097 936.132 ↑ 3.6 32,574 1

Subquery Scan on f (cost=7,844.69..206,191.79 rows=118,727 width=0) (actual time=930.072..936.132 rows=32,574 loops=1)

3. 7.595 933.035 ↑ 3.6 32,574 1

HashSetOp Except (cost=7,844.69..205,004.52 rows=118,727 width=8) (actual time=930.071..933.035 rows=32,574 loops=1)

4. 2.302 925.440 ↑ 5.2 32,614 1

Append (cost=7,844.69..204,579.55 rows=169,985 width=8) (actual time=804.787..925.440 rows=32,614 loops=1)

5. 3.176 811.383 ↑ 3.6 32,594 1

Result (cost=7,844.69..168,223.58 rows=118,727 width=8) (actual time=804.786..811.383 rows=32,594 loops=1)

6. 11.245 808.207 ↑ 3.6 32,594 1

HashSetOp Except (cost=7,844.69..167,036.31 rows=118,727 width=8) (actual time=804.785..808.207 rows=32,594 loops=1)

7. 3.886 796.962 ↑ 2.6 53,814 1

Append (cost=7,844.69..166,686.21 rows=140,039 width=8) (actual time=588.132..796.962 rows=53,814 loops=1)

8. 4.358 597.660 ↑ 2.7 43,204 1

Result (cost=7,844.69..125,538.84 rows=118,727 width=8) (actual time=588.131..597.660 rows=43,204 loops=1)

9. 28.477 593.302 ↑ 2.7 43,204 1

HashSetOp Except (cost=7,844.69..124,351.57 rows=118,727 width=8) (actual time=588.129..593.302 rows=43,204 loops=1)

10. 13.523 564.825 ↓ 1.3 188,272 1

Append (cost=7,844.69..123,975.31 rows=150,502 width=8) (actual time=248.521..564.825 rows=188,272 loops=1)

11. 11.537 268.536 ↑ 1.0 115,716 1

Result (cost=7,844.69..76,435.38 rows=118,727 width=8) (actual time=248.521..268.536 rows=115,716 loops=1)

12. 23.941 256.999 ↑ 1.0 115,716 1

HashSetOp Except (cost=7,844.69..75,248.11 rows=118,727 width=8) (actual time=248.520..256.999 rows=115,716 loops=1)

13. 8.282 233.058 ↑ 1.0 116,317 1

Append (cost=7,844.69..74,950.58 rows=119,015 width=8) (actual time=203.786..233.058 rows=116,317 loops=1)

14. 11.322 223.789 ↑ 1.0 116,015 1

Result (cost=7,844.69..74,119.39 rows=118,727 width=8) (actual time=203.785..223.789 rows=116,015 loops=1)

15. 23.687 212.467 ↑ 1.0 116,015 1

HashSetOp Except (cost=7,844.69..72,932.12 rows=118,727 width=8) (actual time=203.784..212.467 rows=116,015 loops=1)

16. 8.241 188.780 ↑ 1.0 116,133 1

Append (cost=7,844.69..72,635.05 rows=118,825 width=8) (actual time=153.138..188.780 rows=116,133 loops=1)

17. 11.386 179.917 ↑ 1.0 116,074 1

Result (cost=7,844.69..71,883.03 rows=118,727 width=8) (actual time=153.138..179.917 rows=116,074 loops=1)

18. 51.229 168.531 ↑ 1.0 116,074 1

HashSetOp Except (cost=7,844.69..70,695.76 rows=118,727 width=8) (actual time=153.136..168.531 rows=116,074 loops=1)

19. 16.844 117.302 ↑ 1.0 116,074 1

Append (cost=7,844.69..70,398.94 rows=118,728 width=8) (actual time=19.800..117.302 rows=116,074 loops=1)

20. 22.414 100.431 ↑ 1.0 116,074 1

Subquery Scan on *SELECT* 1 (cost=7,844.69..69,803.05 rows=118,727 width=8) (actual time=19.799..100.431 rows=116,074 loops=1)

21. 61.003 78.017 ↑ 1.0 116,074 1

Bitmap Heap Scan on file (cost=7,844.69..68,615.78 rows=118,727 width=4) (actual time=19.796..78.017 rows=116,074 loops=1)

  • Recheck Cond: (project = 198)
  • Heap Blocks: exact=10,431
22. 17.014 17.014 ↑ 1.0 116,203 1

Bitmap Index Scan on file_project_name_hash_executable_is_binary_key (cost=0.00..7,815.01 rows=118,727 width=0) (actual time=17.014..17.014 rows=116,203 loops=1)

  • Index Cond: (project = 198)
23. 0.001 0.027 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=2.23..2.25 rows=1 width=8) (actual time=0.026..0.027 rows=0 loops=1)

24. 0.000 0.026 ↓ 0.0 0 1

Unique (cost=2.23..2.24 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1)

25. 0.015 0.026 ↓ 0.0 0 1

Sort (cost=2.23..2.24 rows=1 width=4) (actual time=0.025..0.026 rows=0 loops=1)

  • Sort Key: plugin.file
  • Sort Method: quicksort Memory: 25kB
26. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on plugin (cost=0.00..2.23 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: (project = 198)
  • Rows Removed by Filter: 8
27. 0.008 0.622 ↑ 1.7 59 1

Subquery Scan on *SELECT* 3 (cost=155.94..157.90 rows=98 width=8) (actual time=0.608..0.622 rows=59 loops=1)

28. 0.024 0.614 ↑ 1.7 59 1

HashAggregate (cost=155.94..156.92 rows=98 width=4) (actual time=0.607..0.614 rows=59 loops=1)

  • Group Key: f_1.file
29. 0.240 0.590 ↑ 1.4 70 1

Hash Join (cost=105.25..155.69 rows=98 width=4) (actual time=0.429..0.590 rows=70 loops=1)

  • Hash Cond: (f_1.node = n.id)
30. 0.233 0.233 ↑ 1.0 2,643 1

Seq Scan on node_file f_1 (cost=0.00..43.48 rows=2,648 width=8) (actual time=0.015..0.233 rows=2,643 loops=1)

31. 0.015 0.117 ↑ 1.0 87 1

Hash (cost=104.17..104.17 rows=87 width=4) (actual time=0.117..0.117 rows=87 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
32. 0.102 0.102 ↑ 1.0 87 1

Index Scan using node_project_fk_idx on node n (cost=0.28..104.17 rows=87 width=4) (actual time=0.017..0.102 rows=87 loops=1)

  • Index Cond: (project = 198)
33. 0.030 0.987 ↓ 1.0 302 1

Subquery Scan on *SELECT* 4 (cost=230.36..236.12 rows=288 width=8) (actual time=0.922..0.987 rows=302 loops=1)

34. 0.109 0.957 ↓ 1.0 302 1

HashAggregate (cost=230.36..233.24 rows=288 width=4) (actual time=0.921..0.957 rows=302 loops=1)

  • Group Key: h.file
35. 0.047 0.848 ↓ 1.1 307 1

Nested Loop (cost=105.54..229.64 rows=288 width=4) (actual time=0.444..0.848 rows=307 loops=1)

36. 0.231 0.591 ↑ 1.4 70 1

Hash Join (cost=105.25..155.69 rows=98 width=4) (actual time=0.430..0.591 rows=70 loops=1)

  • Hash Cond: (f_2.node = n_1.id)
37. 0.244 0.244 ↑ 1.0 2,643 1

Seq Scan on node_file f_2 (cost=0.00..43.48 rows=2,648 width=8) (actual time=0.017..0.244 rows=2,643 loops=1)

38. 0.016 0.116 ↑ 1.0 87 1

Hash (cost=104.17..104.17 rows=87 width=4) (actual time=0.115..0.116 rows=87 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
39. 0.100 0.100 ↑ 1.0 87 1

Index Scan using node_project_fk_idx on node n_1 (cost=0.28..104.17 rows=87 width=4) (actual time=0.017..0.100 rows=87 loops=1)

  • Index Cond: (project = 198)
40. 0.210 0.210 ↑ 3.0 4 70

Index Only Scan using node_file_history_node_file_file_key on node_file_history h (cost=0.28..0.63 rows=12 width=8) (actual time=0.002..0.003 rows=4 loops=70)

  • Index Cond: (node_file = f_2.id)
  • Heap Fetches: 307
41. 6.817 282.766 ↓ 2.3 72,556 1

Subquery Scan on *SELECT* 5 (cost=46,151.92..46,787.42 rows=31,775 width=8) (actual time=262.621..282.766 rows=72,556 loops=1)

42. 74.817 275.949 ↓ 2.3 72,556 1

HashAggregate (cost=46,151.92..46,469.67 rows=31,775 width=4) (actual time=262.619..275.949 rows=72,556 loops=1)

  • Group Key: f_3.file
43. 27.285 201.132 ↓ 1.2 322,145 1

Nested Loop (cost=105.69..45,469.36 rows=273,023 width=4) (actual time=4.284..201.132 rows=322,145 loops=1)

44. 30.406 58.137 ↑ 1.4 7,714 1

Hash Join (cost=105.25..11,202.80 rows=10,676 width=4) (actual time=4.266..58.137 rows=7,714 loops=1)

  • Hash Cond: (i.node = n_2.id)
45. 27.612 27.612 ↑ 1.0 278,802 1

Seq Scan on instance i (cost=0.00..10,335.36 rows=289,836 width=8) (actual time=0.015..27.612 rows=278,802 loops=1)

46. 0.018 0.119 ↑ 1.0 87 1

Hash (cost=104.17..104.17 rows=87 width=4) (actual time=0.119..0.119 rows=87 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
47. 0.101 0.101 ↑ 1.0 87 1

Index Scan using node_project_fk_idx on node n_2 (cost=0.28..104.17 rows=87 width=4) (actual time=0.017..0.101 rows=87 loops=1)

  • Index Cond: (project = 198)
48. 115.710 115.710 ↑ 1.9 42 7,714

Index Only Scan using instance_file_pkey on instance_file f_3 (cost=0.43..2.41 rows=80 width=8) (actual time=0.003..0.015 rows=42 loops=7,714)

  • Index Cond: (instance = i.id)
  • Heap Fetches: 322,145
49. 0.972 195.416 ↑ 2.0 10,610 1

Subquery Scan on *SELECT* 6 (cost=40,020.94..40,447.18 rows=21,312 width=8) (actual time=192.914..195.416 rows=10,610 loops=1)

50. 22.801 194.444 ↑ 2.0 10,610 1

HashAggregate (cost=40,020.94..40,234.06 rows=21,312 width=4) (actual time=192.913..194.444 rows=10,610 loops=1)

  • Group Key: f_4.file
51. 27.557 171.643 ↑ 1.1 119,749 1

Gather (cost=1,105.68..39,705.29 rows=126,258 width=4) (actual time=4.588..171.643 rows=119,749 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
52. 10.622 144.086 ↑ 1.3 39,916 3 / 3

Nested Loop (cost=105.68..26,079.49 rows=52,608 width=4) (actual time=2.172..144.086 rows=39,916 loops=3)

53. 43.679 82.851 ↓ 1.0 12,653 3 / 3

Hash Join (cost=105.25..17,269.95 rows=12,462 width=4) (actual time=2.102..82.851 rows=12,653 loops=3)

  • Hash Cond: (p.node = n_3.id)
54. 38.765 38.765 ↑ 1.2 271,715 3 / 3

Parallel Seq Scan on package p (cost=0.00..16,275.27 rows=338,327 width=8) (actual time=0.015..38.765 rows=271,715 loops=3)

55. 0.039 0.407 ↑ 1.0 87 3 / 3

Hash (cost=104.17..104.17 rows=87 width=4) (actual time=0.406..0.407 rows=87 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
56. 0.368 0.368 ↑ 1.0 87 3 / 3

Index Scan using node_project_fk_idx on node n_3 (cost=0.28..104.17 rows=87 width=4) (actual time=0.050..0.368 rows=87 loops=3)

  • Index Cond: (project = 198)
57. 50.613 50.613 ↑ 2.7 3 37,960 / 3

Index Only Scan using package_file_pkey on package_file f_4 (cost=0.43..0.63 rows=8 width=8) (actual time=0.003..0.004 rows=3 loops=37,960)

  • Index Cond: (package = p.id)
  • Heap Fetches: 47,489
58. 0.003 111.755 ↑ 2,562.9 20 1

Subquery Scan on *SELECT* 7 (cost=34,480.89..35,506.05 rows=51,258 width=8) (actual time=111.528..111.755 rows=20 loops=1)

59. 0.213 111.752 ↑ 2,562.9 20 1

HashAggregate (cost=34,480.89..34,993.47 rows=51,258 width=4) (actual time=111.527..111.752 rows=20 loops=1)

  • Group Key: i_1.file
60. 4.837 111.539 ↑ 1,971.5 26 1

Gather (cost=1,105.68..34,352.74 rows=51,258 width=4) (actual time=58.151..111.539 rows=26 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
61. 0.000 106.702 ↑ 2,373.1 9 3 / 3

Nested Loop (cost=105.68..28,226.94 rows=21,358 width=4) (actual time=54.261..106.702 rows=9 loops=3)

62. 36.552 69.547 ↓ 1.0 12,653 3 / 3

Hash Join (cost=105.25..17,269.95 rows=12,462 width=4) (actual time=2.699..69.547 rows=12,653 loops=3)

  • Hash Cond: (p_1.node = n_4.id)
63. 32.678 32.678 ↑ 1.2 271,715 3 / 3

Parallel Seq Scan on package p_1 (cost=0.00..16,275.27 rows=338,327 width=8) (actual time=0.010..32.678 rows=271,715 loops=3)

64. 0.033 0.317 ↑ 1.0 87 3 / 3

Hash (cost=104.17..104.17 rows=87 width=4) (actual time=0.316..0.317 rows=87 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
65. 0.284 0.284 ↑ 1.0 87 3 / 3

Index Scan using node_project_fk_idx on node n_4 (cost=0.28..104.17 rows=87 width=4) (actual time=0.041..0.284 rows=87 loops=3)

  • Index Cond: (project = 198)
66. 37.960 37.960 ↓ 0.0 0 37,960 / 3

Index Only Scan using package_image_pkey on package_image i_1 (cost=0.43..0.73 rows=15 width=8) (actual time=0.003..0.003 rows=0 loops=37,960)

  • Index Cond: (package = p_1.id)
  • Heap Fetches: 26
Planning time : 3.031 ms