explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NpXn

Settings
# exclusive inclusive rows x rows loops node
1. 45.748 5,643.183 ↓ 50.6 116,248 1

Hash Left Join (cost=444,442.17..505,417.80 rows=2,297 width=512) (actual time=4,044.588..5,643.183 rows=116,248 loops=1)

  • Hash 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. 24.371 3,327.814 ↓ 3.3 301,612 1

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

4. 68.265 219.699 ↓ 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=160.266..219.699 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 2.110 190.735 ↓ 4.5 5,423 1

Hash Join (cost=73.91..20,243.02 rows=1,208 width=24) (actual time=5.133..190.735 rows=5,423 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 3.269 188.247 ↓ 4.5 5,423 1

Nested Loop (cost=28.12..20,180.62 rows=1,208 width=32) (actual time=4.722..188.247 rows=5,423 loops=1)

11. 5.299 174.132 ↓ 4.5 5,423 1

Nested Loop (cost=27.70..10,680.18 rows=1,208 width=32) (actual time=4.681..174.132 rows=5,423 loops=1)

12. 10.887 11.566 ↓ 6.1 5,423 1

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

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

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

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

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.028..0.029 rows=1 loops=5,423)

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

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,423)

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

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

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

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

18. 38.035 5,596.049 ↓ 65.6 150,792 1

Hash Left Join (cost=79,959.63..140,228.25 rows=2,297 width=488) (actual time=4,043.169..5,596.049 rows=150,792 loops=1)

  • Hash Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
19. 954.550 5,361.901 ↓ 65.6 150,792 1

Hash Join (cost=79,917.35..139,478.95 rows=2,297 width=464) (actual time=3,847.037..5,361.901 rows=150,792 loops=1)

  • Hash Cond: ((file.id = vbssm.composite_id) AND (file.scan_id = vbssm.scan_id))
20. 3,429.828 3,429.828 ↓ 3.3 301,612 1

CTE Scan on file (cost=0.00..1,833.08 rows=91,654 width=32) (actual time=160.275..3,429.828 rows=301,612 loops=1)

21. 645.926 977.523 ↓ 1.5 703,576 1

Hash (cost=46,068.14..46,068.14 rows=479,014 width=432) (actual time=977.523..977.523 rows=703,576 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 16 (originally 8) Memory Usage: 31,745kB
22. 331.597 331.597 ↓ 1.5 703,576 1

Seq Scan on version_bom_string_search_match vbssm (cost=0.00..46,068.14 rows=479,014 width=432) (actual time=0.049..331.597 rows=703,576 loops=1)

23. 1.962 196.113 ↓ 4.5 5,423 1

Hash (cost=24.16..24.16 rows=1,208 width=24) (actual time=196.112..196.113 rows=5,423 loops=1)

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 361kB
24. 194.151 194.151 ↓ 4.5 5,423 1

CTE Scan on vbf vbf1 (cost=0.00..24.16 rows=1,208 width=24) (actual time=5.142..194.151 rows=5,423 loops=1)

25. 0.896 1.386 ↓ 4.5 5,423 1

Hash (cost=24.16..24.16 rows=1,208 width=24) (actual time=1.386..1.386 rows=5,423 loops=1)

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 361kB
26. 0.490 0.490 ↓ 4.5 5,423 1

CTE Scan on vbf vbf2 (cost=0.00..24.16 rows=1,208 width=24) (actual time=0.001..0.490 rows=5,423 loops=1)

Planning time : 8.618 ms
Execution time : 5,659.813 ms