explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uhKd

Settings
# exclusive inclusive rows x rows loops node
1. 1.723 763.868 ↑ 1.0 1 1

Aggregate (cost=194,277.72..194,277.73 rows=1 width=8) (actual time=763.867..763.868 rows=1 loops=1)

  • Execution Time: 765.172 ms(88 строк)
2. 71.660 762.145 ↑ 1.8 32,574 1

Bitmap Heap Scan on file (cost=133,061.40..194,129.31 rows=59,363 width=0) (actual time=707.860..762.145 rows=32,574 loops=1)

  • Recheck Cond: (project = 198)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 83,500
  • Heap Blocks: exact=10,431
3. 20.333 20.333 ↑ 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=20.333..20.333 rows=116,203 loops=1)

  • Index Cond: (project = 198)
4.          

SubPlan (for Bitmap Heap Scan)

5. 25.882 670.152 ↑ 1.3 83,500 1

HashAggregate (cost=123,922.41..124,969.73 rows=104,732 width=4) (actual time=661.118..670.152 rows=83,500 loops=1)

  • Group Key: plugin.file
6. 6.097 644.270 ↑ 1.3 83,547 1

Append (cost=2.23..123,660.58 rows=104,732 width=4) (actual time=1.387..644.270 rows=83,547 loops=1)

7. 0.000 0.032 ↓ 0.0 0 1

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

8. 0.010 0.032 ↓ 0.0 0 1

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

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

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

  • Filter: (project = 198)
  • Rows Removed by Filter: 8
10. 0.067 1.371 ↑ 1.7 59 1

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

  • Group Key: f.file
11. 0.599 1.304 ↑ 1.4 70 1

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

  • Hash Cond: (f.node = n.id)
12. 0.512 0.512 ↑ 1.0 2,643 1

Seq Scan on node_file f (cost=0.00..43.48 rows=2,648 width=8) (actual time=0.011..0.512 rows=2,643 loops=1)

13. 0.039 0.193 ↑ 1.0 87 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
14. 0.154 0.154 ↑ 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.021..0.154 rows=87 loops=1)

  • Index Cond: (project = 198)
15. 0.279 2.054 ↓ 1.0 302 1

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

  • Group Key: h.file
16. 0.092 1.775 ↓ 1.1 307 1

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

17. 0.579 1.193 ↑ 1.4 70 1

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

  • Hash Cond: (f_1.node = n_1.id)
18. 0.481 0.481 ↑ 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.009..0.481 rows=2,643 loops=1)

19. 0.039 0.133 ↑ 1.0 87 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
20. 0.094 0.094 ↑ 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.016..0.094 rows=87 loops=1)

  • Index Cond: (project = 198)
21. 0.490 0.490 ↑ 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.004..0.007 rows=4 loops=70)

  • Index Cond: (node_file = f_1.id)
  • Heap Fetches: 307
22. 82.157 331.745 ↓ 2.3 72,556 1

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

  • Group Key: f_2.file
23. 32.421 249.588 ↓ 1.2 322,145 1

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

24. 49.211 93.743 ↑ 1.4 7,714 1

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

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

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

26. 0.035 0.124 ↑ 1.0 87 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
27. 0.089 0.089 ↑ 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.011..0.089 rows=87 loops=1)

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

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

  • Index Cond: (instance = i.id)
  • Heap Fetches: 322,145
29. 20.982 194.916 ↑ 2.0 10,610 1

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

  • Group Key: f_3.file
30. 30.311 173.934 ↑ 1.1 119,749 1

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

  • Workers Planned: 2
  • Workers Launched: 2
31. 8.446 143.623 ↑ 1.3 39,916 3 / 3

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

32. 43.825 84.564 ↓ 1.0 12,653 3 / 3

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

  • Hash Cond: (p.node = n_3.id)
33. 40.317 40.317 ↑ 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.014..40.317 rows=271,715 loops=3)

34. 0.040 0.422 ↑ 1.0 87 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
35. 0.382 0.382 ↑ 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.051..0.382 rows=87 loops=3)

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

Index Only Scan using package_file_pkey on package_file f_3 (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
37. 0.212 108.055 ↑ 2,562.9 20 1

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

  • Group Key: i_1.file
38. 4.121 107.843 ↑ 1,971.5 26 1

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

  • Workers Planned: 2
  • Workers Launched: 2
39. 0.000 103.722 ↑ 2,373.1 9 3 / 3

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

40. 35.182 66.626 ↓ 1.0 12,653 3 / 3

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

  • Hash Cond: (p_1.node = n_4.id)
41. 31.168 31.168 ↑ 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.011..31.168 rows=271,715 loops=3)

42. 0.028 0.276 ↑ 1.0 87 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
43. 0.248 0.248 ↑ 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.034..0.248 rows=87 loops=3)

  • Index Cond: (project = 198)
44. 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.489 ms