explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PwjY

Settings
# exclusive inclusive rows x rows loops node
1. 54.859 9,211.561 ↓ 11.7 116,248 1

Merge Left Join (cost=775,769.61..775,855.97 rows=9,907 width=513) (actual time=9,080.915..9,211.561 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: 34,544
2.          

CTE file

3. 25.353 4,257.716 ↓ 3.3 301,612 1

Append (cost=155.54..344,197.24 rows=91,654 width=32) (actual time=378.487..4,257.716 rows=301,612 loops=1)

4. 64.723 436.553 ↓ 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=378.486..436.553 rows=28,320 loops=1)

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
6. 1,503.844 3,795.810 ↓ 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,315.014..3,795.810 rows=273,292 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Heap Blocks: exact=20,347
7. 2,291.966 2,291.966 ↓ 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,291.966..2,291.966 rows=546,481 loops=1)

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

CTE vbf

9. 3.251 308.812 ↓ 4.5 5,425 1

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

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

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

11. 3.310 286.520 ↓ 4.5 5,425 1

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

12. 9.623 22.810 ↓ 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=13.284..22.810 rows=5,425 loops=1)

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
14. 260.400 260.400 ↑ 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.046..0.048 rows=1 loops=5,425)

  • Index Cond: (version_bom_file_id = vbf.id)
15. 16.275 16.275 ↑ 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.003..0.003 rows=1 loops=5,425)

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 68kB
17. 0.185 0.185 ↓ 1.0 1,328 1

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

18. 515.663 9,142.492 ↓ 15.2 150,792 1

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

  • Sort Key: file.scan_id, file.root_bom_consumer_node_id
  • Sort Method: external sort Disk: 70,176kB
19. 50.210 8,626.829 ↓ 15.2 150,792 1

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

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

Sort (cost=410,413.46..410,438.23 rows=9,907 width=465) (actual time=8,150.822..8,257.241 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.id
  • Sort Method: external merge Disk: 69,736kB
21. 1,380.955 7,636.358 ↓ 15.2 150,792 1

Hash Join (cost=197,413.50..409,755.92 rows=9,907 width=465) (actual time=4,741.436..7,636.358 rows=150,792 loops=1)

  • Hash Cond: ((file.id = vbssm.composite_id) AND (file.scan_id = vbssm.scan_id))
22. 4,375.458 4,375.458 ↓ 3.3 301,612 1

CTE Scan on file (cost=0.00..1,833.08 rows=91,654 width=32) (actual time=378.490..4,375.458 rows=301,612 loops=1)

23. 1,150.852 1,879.945 ↓ 1.0 1,407,152 1

Hash (cost=96,613.20..96,613.20 rows=1,407,020 width=433) (actual time=1,879.945..1,879.945 rows=1,407,152 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 21,182kB
24. 729.093 729.093 ↓ 1.0 1,407,152 1

Seq Scan on version_bom_string_search_match vbssm (cost=0.00..96,613.20 rows=1,407,020 width=433) (actual time=2.171..729.093 rows=1,407,152 loops=1)

25. 7.012 319.378 ↓ 4.5 5,429 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=317.516..319.378 rows=5,429 loops=1)

  • Sort Key: vbf1.scan_id, vbf1.composite_id
  • Sort Method: quicksort Memory: 616kB
26. 312.366 312.366 ↓ 4.5 5,425 1

CTE Scan on vbf vbf1 (cost=0.00..24.16 rows=1,208 width=24) (actual time=21.057..312.366 rows=5,425 loops=1)

27. 13.739 14.210 ↓ 99.5 120,213 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=3.799..14.210 rows=120,213 loops=1)

  • Sort Key: vbf2.scan_id, vbf2.composite_id
  • Sort Method: quicksort Memory: 616kB
28. 0.471 0.471 ↓ 4.5 5,425 1

CTE Scan on vbf vbf2 (cost=0.00..24.16 rows=1,208 width=24) (actual time=0.002..0.471 rows=5,425 loops=1)

Planning time : 2.034 ms
Execution time : 9,268.469 ms