explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4T2s

Settings
# exclusive inclusive rows x rows loops node
1. 84.526 1,534.808 ↓ 18,120.7 54,362 1

Hash Join (cost=40,294.06..40,339.07 rows=3 width=1,252) (actual time=253.618..1,534.808 rows=54,362 loops=1)

  • Hash Cond: ((declared_component_entry.component_external_id = dependency_match.match_content) AND (declared_component_entry.code_location_id = dependency_match.code_location_id))
  • Planning time: 21.015 ms
  • Execution time: 1540.176 ms
2.          

CTE declared_component_entry

3. 3.922 1,222.405 ↓ 36.9 19,535 1

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

4. 5.487 5.487 ↑ 1.0 2 1

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

  • Filter: (release_id = '9478dff6-7737-46c4-bd02-f25d72b00802'::uuid)
  • Rows Removed by Filter: 9725
5. 1,186.480 1,212.996 ↓ 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=18.880..606.498 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. 26.516 26.516 ↓ 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=13.258..13.258 rows=34,270 loops=2)

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

CTE dependency_match

8. 8.059 207.671 ↓ 5.5 1,339 1

Nested Loop (cost=8.86..37,600.97 rows=245 width=388) (actual time=4.575..207.671 rows=1,339 loops=1)

9. 21.152 106.624 ↓ 3.4 738 1

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

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

Seq Scan on version_bom_component vbc (cost=0.00..10,069.71 rows=210,271 width=132) (actual time=0.019..85.446 rows=210,277 loops=1)

11. 0.006 0.026 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.020 0.020 ↑ 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.020..0.020 rows=1 loops=1)

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

Index Scan using version_bom_match_type_idx on version_bom_entry vbe (cost=0.55..121.36 rows=12 width=500) (actual time=0.114..0.126 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[])))
  • Filter: ((match_type = 'FILE_EXACT'::text) OR (match_type = 'FILE_DEPENDENCY'::text))
14. 1,236.946 1,236.946 ↓ 36.9 19,535 1

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

15. 2.251 213.336 ↓ 5.5 1,339 1

Hash (cost=4.90..4.90 rows=245 width=522) (actual time=213.336..213.336 rows=1,339 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 871kB
16. 211.085 211.085 ↓ 5.5 1,339 1

CTE Scan on dependency_match (cost=0.00..4.90 rows=245 width=522) (actual time=4.584..211.085 rows=1,339 loops=1)