explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UVC3

Settings
# exclusive inclusive rows x rows loops node
1. 66.692 8,681.608 ↓ 11.7 116,248 1

Merge Left Join (cost=775,769.61..775,855.97 rows=9,907 width=513) (actual time=8,557.560..8,681.608 rows=116,248 loops=1)

  • Merge Cond: ((file.scan_id = vbf2.scan_id) AND (file.root_bom_consumer_node_id = vbf2.composite_id))
  • Filter: ((vbf1.composite_id IS NOT NULL) OR (vbf2.composite_id IS NOT NULL))
  • Rows Removed by Filter: 34544
2.          

CTE file

3. 24.453 3,712.008 ↓ 3.3 301,612 1

Append (cost=155.54..344,197.24 rows=91,654 width=32) (actual time=258.341..3,712.008 rows=301,612 loops=1)

4. 108.259 359.325 ↓ 4.3 28,320 1

Bitmap Heap Scan on scan_composite_element sce (cost=155.54..25,208.41 rows=6,578 width=32) (actual time=258.339..359.325 rows=28,320 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Heap Blocks: exact=4120
5. 251.066 251.066 ↓ 7.1 46,918 1

Bitmap Index Scan on scan_id_idx (cost=0.00..153.90 rows=6,578 width=0) (actual time=251.066..251.066 rows=46,918 loops=1)

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
6. 591.062 3,328.230 ↓ 3.2 273,292 1

Bitmap Heap Scan on scan_composite_leaf scl (cost=1,979.91..318,072.28 rows=85,076 width=32) (actual time=2,754.656..3,328.230 rows=273,292 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Heap Blocks: exact=20347
7. 2,737.168 2,737.168 ↓ 6.4 546,481 1

Bitmap Index Scan on scan_id_leaf_idx (cost=0.00..1,958.64 rows=85,076 width=0) (actual time=2,737.168..2,737.168 rows=546,481 loops=1)

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
8.          

CTE vbf

9. 3.050 377.023 ↓ 4.5 5,425 1

Hash Join (cost=73.91..20,243.02 rows=1,208 width=24) (actual time=21.697..377.023 rows=5,425 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 4.376 370.913 ↓ 4.5 5,425 1

Nested Loop (cost=28.12..20,180.62 rows=1,208 width=32) (actual time=18.605..370.913 rows=5,425 loops=1)

11. 3.119 355.687 ↓ 4.5 5,425 1

Nested Loop (cost=27.70..10,680.18 rows=1,208 width=32) (actual time=18.580..355.687 rows=5,425 loops=1)

12. 93.556 108.443 ↓ 6.1 5,425 1

Bitmap Heap Scan on version_bom_file vbf (cost=27.27..3,378.42 rows=883 width=32) (actual time=14.984..108.443 rows=5,425 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Heap Blocks: exact=574
13. 14.887 14.887 ↓ 6.1 5,425 1

Bitmap Index Scan on scan_composite_index (cost=0.00..27.05 rows=883 width=0) (actual time=14.887..14.887 rows=5,425 loops=1)

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
14. 244.125 244.125 ↑ 1.0 1 5,425

Index Scan using idx_version_bom_entry_version_bom_file_id on version_bom_entry vbe (cost=0.43..8.26 rows=1 width=24) (actual time=0.042..0.045 rows=1 loops=5,425)

  • Index Cond: (version_bom_file_id = vbf.id)
15. 10.850 10.850 ↑ 1.0 1 5,425

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.85 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=5,425)

  • Index Cond: (id = vbe.version_bom_component_id)
16. 0.160 3.060 ↓ 1.0 1,328 1

Hash (cost=29.24..29.24 rows=1,324 width=8) (actual time=3.060..3.060 rows=1,328 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
17. 2.900 2.900 ↓ 1.0 1,328 1

Seq Scan on version_bom vb (cost=0.00..29.24 rows=1,324 width=8) (actual time=0.783..2.900 rows=1,328 loops=1)

18. 458.475 8,614.916 ↓ 15.2 150,792 1

Sort (cost=411,243.36..411,268.12 rows=9,907 width=489) (actual time=8,553.503..8,614.916 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.root_bom_consumer_node_id
  • Sort Method: external sort Disk: 70176kB
19. 431.911 8,156.441 ↓ 15.2 150,792 1

Merge Left Join (cost=410,499.46..410,585.82 rows=9,907 width=489) (actual time=8,015.027..8,156.441 rows=150,792 loops=1)

  • Merge Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
20. 7,724.530 7,724.530 ↓ 15.2 150,792 1

Sort (cost=410,413.46..410,438.23 rows=9,907 width=465) (actual time=7,630.090..7,724.530 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.id
  • Sort Method: external merge Disk: 69736kB