explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OSzU

Settings
# exclusive inclusive rows x rows loops node
1. 179.325 3,408.601 ↓ 187.8 150,623 1

HashAggregate (cost=361,383.47..361,391.49 rows=802 width=56) (actual time=3,338.703..3,408.601 rows=150,623 loops=1)

  • Group Key: file.id, file.scan_id, file.root_bom_consumer_node_id, vbf.scan_id, vbf.composite_id
2.          

CTE file

3. 25.865 2,882.985 ↓ 3.3 301,612 1

Append (cost=155.54..344,197.24 rows=91,654 width=32) (actual time=11.806..2,882.985 rows=301,612 loops=1)

4. 59.978 70.862 ↓ 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=11.805..70.862 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.513 54.908 ↓ 19.3 3,377 1

Hash Join (cost=73.91..12,106.94 rows=175 width=24) (actual time=1.938..54.908 rows=3,377 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 3.408 52.778 ↓ 19.3 3,377 1

Nested Loop (cost=28.12..12,058.75 rows=175 width=32) (actual time=1.276..52.778 rows=3,377 loops=1)

11. 0.777 42.616 ↓ 19.3 3,377 1

Nested Loop (cost=27.70..10,682.39 rows=175 width=32) (actual time=1.246..42.616 rows=3,377 loops=1)

12. 8.249 9.301 ↓ 6.1 5,423 1

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

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

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

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

  • Index Cond: (version_bom_file_id = vbf_2.id)
  • Filter: (match_type = 'FILE_EXACT'::text)
  • Rows Removed by Filter: 0
15. 6.754 6.754 ↑ 1.0 1 3,377

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=3,377)

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

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

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

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

18. 12.022 3,229.276 ↓ 192.0 153,998 1

Append (cost=6.12..5,069.26 rows=802 width=56) (actual time=72.167..3,229.276 rows=153,998 loops=1)

19. 68.072 3,117.910 ↓ 8.4 3,377 1

Hash Join (cost=6.12..2,530.62 rows=401 width=56) (actual time=72.166..3,117.910 rows=3,377 loops=1)

  • Hash Cond: ((file.scan_id = vbf.scan_id) AND (file.id = vbf.composite_id))
20. 2,991.503 2,991.503 ↓ 3.3 301,612 1

CTE Scan on file (cost=0.00..1,833.08 rows=91,654 width=32) (actual time=11.812..2,991.503 rows=301,612 loops=1)

21. 1.500 58.335 ↓ 19.3 3,377 1

Hash (cost=3.50..3.50 rows=175 width=24) (actual time=58.335..58.335 rows=3,377 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
22. 56.835 56.835 ↓ 19.3 3,377 1

CTE Scan on vbf (cost=0.00..3.50 rows=175 width=24) (actual time=1.942..56.835 rows=3,377 loops=1)

23. 62.297 99.344 ↓ 375.6 150,621 1

Hash Join (cost=6.12..2,530.62 rows=401 width=56) (actual time=0.876..99.344 rows=150,621 loops=1)

  • Hash Cond: ((file_1.scan_id = vbf_1.scan_id) AND (file_1.root_bom_consumer_node_id = vbf_1.composite_id))
24. 36.217 36.217 ↓ 3.3 301,612 1

CTE Scan on file file_1 (cost=0.00..1,833.08 rows=91,654 width=32) (actual time=0.001..36.217 rows=301,612 loops=1)

25. 0.543 0.830 ↓ 19.3 3,377 1

Hash (cost=3.50..3.50 rows=175 width=24) (actual time=0.830..0.830 rows=3,377 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
26. 0.287 0.287 ↓ 19.3 3,377 1

CTE Scan on vbf vbf_1 (cost=0.00..3.50 rows=175 width=24) (actual time=0.000..0.287 rows=3,377 loops=1)

Planning time : 2.103 ms
Execution time : 3,426.515 ms