explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0V7n

Settings
# exclusive inclusive rows x rows loops node
1. 52.464 247.248 ↓ 6,795.2 54,362 1

Merge Join (cost=40,383.27..40,392.16 rows=8 width=1,252) (actual time=181.947..247.248 rows=54,362 loops=1)

  • Merge Cond: ((dependency_match.match_content = declared_component_entry.component_external_id) AND (dependency_match.code_location_id = declared_component_entry.code_location_id))
2.          

CTE declared_component_entry

3. 2.495 31.149 ↓ 36.9 19,535 1

Nested Loop (cost=10.65..2,684.51 rows=529 width=74) (actual time=7.801..31.149 rows=19,535 loops=1)

4. 4.230 4.230 ↑ 1.0 2 1

Seq Scan on cl_main_code_location (cost=0.00..554.59 rows=2 width=16) (actual time=1.756..4.230 rows=2 loops=1)

  • Filter: (release_id = '9478dff6-7737-46c4-bd02-f25d72b00802'::uuid)
  • Rows Removed by Filter: 9725
5. 18.544 24.424 ↓ 37.0 9,768 2

Bitmap Heap Scan on code_location_bom_declared_component (cost=10.65..1,062.32 rows=264 width=74) (actual time=3.017..12.212 rows=9,768 loops=2)

  • Recheck Cond: (code_location_id = cl_main_code_location.id)
  • Filter: (matched IS TRUE)
  • Rows Removed by Filter: 24502
  • Heap Blocks: exact=918
6. 5.880 5.880 ↓ 119.0 34,270 2

Bitmap Index Scan on code_location_bom_declared_component_code_location_id_idx (cost=0.00..10.59 rows=288 width=0) (actual time=2.940..2.940 rows=34,270 loops=2)

  • Index Cond: (code_location_id = cl_main_code_location.id)
7.          

CTE dependency_match

8. 7.472 120.242 ↓ 2.1 1,339 1

Nested Loop (cost=8.86..37,621.62 rows=640 width=388) (actual time=1.038..120.242 rows=1,339 loops=1)

9. 21.456 103.176 ↓ 3.4 738 1

Hash Join (cost=8.30..10,868.73 rows=220 width=156) (actual time=0.939..103.176 rows=738 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 81.703 81.703 ↓ 1.0 210,286 1

Seq Scan on version_bom_component vbc (cost=0.00..10,069.71 rows=210,271 width=132) (actual time=0.015..81.703 rows=210,286 loops=1)

11. 0.005 0.017 ↑ 1.0 1 1

Hash (cost=8.29..8.29 rows=1 width=40) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.012 0.012 ↑ 1.0 1 1

Index Scan using version_bom_release_id_idx on version_bom vb (cost=0.28..8.29 rows=1 width=40) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (release_id = '9478dff6-7737-46c4-bd02-f25d72b00802'::uuid)
13. 9.594 9.594 ↑ 16.0 2 738

Index Scan using version_bom_match_type_idx on version_bom_entry vbe (cost=0.55..121.20 rows=32 width=500) (actual time=0.008..0.013 rows=2 loops=738)

  • Index Cond: ((version_bom_component_id = vbc.id) AND (match_type = ANY ('{FILE_DEPENDENCY,FILE_DEPENDENCY_DIRECT,FILE_DEPENDENCY_TRANSITIVE}'::text[])))
14. 3.799 127.104 ↓ 2.1 1,339 1

Sort (cost=42.63..44.23 rows=640 width=522) (actual time=126.297..127.104 rows=1,339 loops=1)

  • Sort Key: dependency_match.match_content, dependency_match.code_location_id
  • Sort Method: quicksort Memory: 1417kB
15. 123.305 123.305 ↓ 2.1 1,339 1

CTE Scan on dependency_match (cost=0.00..12.80 rows=640 width=522) (actual time=1.043..123.305 rows=1,339 loops=1)

16. 27.009 67.680 ↓ 102.8 54,362 1

Sort (cost=34.51..35.83 rows=529 width=72) (actual time=55.627..67.680 rows=54,362 loops=1)

  • Sort Key: declared_component_entry.component_external_id, declared_component_entry.code_location_id
  • Sort Method: quicksort Memory: 3514kB
17. 40.671 40.671 ↓ 36.9 19,535 1

CTE Scan on declared_component_entry (cost=0.00..10.58 rows=529 width=72) (actual time=7.807..40.671 rows=19,535 loops=1)