explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qfrf

Settings
# exclusive inclusive rows x rows loops node
1. 44.299 10,062.734 ↓ 65.7 73,043 1

WindowAgg (cost=20,647.09..20,672.11 rows=1,112 width=497) (actual time=9,982.009..10,062.734 rows=73,043 loops=1)

2. 340.833 10,018.435 ↓ 65.7 73,043 1

Sort (cost=20,647.09..20,649.87 rows=1,112 width=489) (actual time=9,981.991..10,018.435 rows=73,043 loops=1)

  • Sort Key: vbf.file_name, vbf.composite_id, vbe.code_location_id
  • Sort Method: external merge Disk: 36928kB
3. 16.862 9,677.602 ↓ 65.7 73,043 1

Result (cost=12.66..20,590.83 rows=1,112 width=489) (actual time=25.564..9,677.602 rows=73,043 loops=1)

4. 10.711 9,660.740 ↓ 65.7 73,043 1

Append (cost=12.66..20,579.71 rows=1,112 width=489) (actual time=25.562..9,660.740 rows=73,043 loops=1)

5. 45.878 9,418.489 ↓ 86.0 72,226 1

Nested Loop (cost=12.66..14,784.12 rows=840 width=483) (actual time=25.562..9,418.489 rows=72,226 loops=1)

6. 28.630 680.494 ↓ 87.0 73,043 1

Nested Loop (cost=12.10..10,260.84 rows=840 width=454) (actual time=0.696..680.494 rows=73,043 loops=1)

7. 26.217 359.692 ↓ 87.0 73,043 1

Nested Loop (cost=11.68..3,839.66 rows=840 width=447) (actual time=0.660..359.692 rows=73,043 loops=1)

8. 4.713 4.713 ↓ 3.5 7 1

Seq Scan on cl_main_code_location (cost=0.00..554.59 rows=2 width=16) (actual time=0.508..4.713 rows=7 loops=1)

  • Filter: (release_id = '3e6d43b5-0a7e-4181-9bc5-c56f36b7a9be'::uuid)
  • Rows Removed by Filter: 9720
9. 317.618 328.762 ↓ 24.8 10,435 7

Bitmap Heap Scan on version_bom_entry vbe (cost=11.68..1,638.34 rows=420 width=447) (actual time=2.032..46.966 rows=10,435 loops=7)

  • Recheck Cond: (code_location_id = cl_main_code_location.id)
  • Heap Blocks: exact=19118
10. 11.144 11.144 ↓ 24.9 10,439 7

Bitmap Index Scan on version_bom_entry_code_location_id_idx (cost=0.00..11.58 rows=420 width=0) (actual time=1.592..1.592 rows=10,439 loops=7)

  • Index Cond: (code_location_id = cl_main_code_location.id)
11. 292.172 292.172 ↑ 1.0 1 73,043

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.63 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=73,043)

  • Index Cond: (id = vbe.version_bom_component_id)
12. 8,692.117 8,692.117 ↑ 1.0 1 73,043

Index Only Scan using idx_version_bom_file_id_name on version_bom_file vbf (cost=0.55..5.37 rows=1 width=45) (actual time=0.119..0.119 rows=1 loops=73,043)

  • Index Cond: (id = vbe.version_bom_file_id)
  • Heap Fetches: 26066
13. 0.395 231.540 ↓ 3.0 817 1

Nested Loop (cost=12.03..5,784.48 rows=272 width=506) (actual time=1.202..231.540 rows=817 loops=1)

14. 0.532 227.060 ↓ 3.0 817 1

Nested Loop (cost=11.61..3,836.99 rows=272 width=475) (actual time=1.171..227.060 rows=817 loops=1)

15. 3.137 3.137 ↓ 3.5 7 1

Seq Scan on cl_main_code_location cl_main_code_location_1 (cost=0.00..554.59 rows=2 width=16) (actual time=0.884..3.137 rows=7 loops=1)

  • Filter: (release_id = '3e6d43b5-0a7e-4181-9bc5-c56f36b7a9be'::uuid)
  • Rows Removed by Filter: 9720
16. 212.380 223.391 ↑ 1.2 117 7

Bitmap Heap Scan on version_bom_entry vbe_1 (cost=11.61..1,639.84 rows=136 width=475) (actual time=31.089..31.913 rows=117 loops=7)

  • Recheck Cond: (code_location_id = cl_main_code_location_1.id)
  • Filter: (match_type = ANY ('{FILE_DEPENDENCY,FILE_DEPENDENCY_DIRECT,FILE_DEPENDENCY_TRANSITIVE}'::text[]))
  • Rows Removed by Filter: 10318
  • Heap Blocks: exact=19118
17. 11.011 11.011 ↓ 24.9 10,439 7

Bitmap Index Scan on version_bom_entry_code_location_id_idx (cost=0.00..11.58 rows=420 width=0) (actual time=1.573..1.573 rows=10,439 loops=7)

  • Index Cond: (code_location_id = cl_main_code_location_1.id)
18. 4.085 4.085 ↑ 1.0 1 817

Index Scan using version_bom_component_pkey on version_bom_component vbc_1 (cost=0.42..7.15 rows=1 width=23) (actual time=0.004..0.005 rows=1 loops=817)

  • Index Cond: (id = vbe_1.version_bom_component_id)
Planning time : 11.157 ms
Execution time : 10,076.677 ms