explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lnyU

Settings
# exclusive inclusive rows x rows loops node
1. 3.205 1,176.629 ↑ 7.3 4,804 1

Merge Left Join (cost=471,131.20..471,413.67 rows=35,043 width=328) (actual time=1,169.569..1,176.629 rows=4,804 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: 7,156
2.          

CTE file

3. 26.520 418.181 ↓ 3.3 301,612 1

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

4. 53.896 60.268 ↓ 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=7.101..60.268 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.431 47.992 ↓ 4.5 5,423 1

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

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

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

11. 3.696 33.932 ↓ 4.5 5,423 1

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

12. 7.758 8.544 ↓ 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.871..8.544 rows=5,423 loops=1)

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
14. 21.692 21.692 ↑ 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.004..0.004 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.256 0.562 ↓ 1.0 1,328 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 68kB
17. 0.306 0.306 ↓ 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.306 rows=1,328 loops=1)

18.          

CTE vbssm

19. 118.028 139.660 ↑ 1.3 11,960 1

Bitmap Heap Scan on version_bom_string_search_match (cost=5,077.91..91,770.23 rows=15,294 width=433) (actual time=34.196..139.660 rows=11,960 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Filter: (license_id = '39692bc6-4d1c-4466-a02c-fa6f21170587'::uuid)
  • Rows Removed by Filter: 138,832
  • Heap Blocks: exact=8,779
20. 21.632 21.632 ↓ 1.0 150,792 1

Bitmap Index Scan on version_bom_string_search_match_scan_composite_idx (cost=0.00..5,074.09 rows=148,488 width=0) (actual time=21.632..21.632 rows=150,792 loops=1)

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
21. 19.609 1,167.963 ↑ 2.9 11,960 1

Sort (cost=14,834.71..14,922.32 rows=35,044 width=304) (actual time=1,165.128..1,167.963 rows=11,960 loops=1)

  • Sort Key: file.scan_id, file.root_bom_consumer_node_id
  • Sort Method: quicksort Memory: 9,771kB
22. 3.704 1,148.354 ↑ 2.9 11,960 1

Merge Left Join (cost=10,842.03..12,189.44 rows=35,044 width=304) (actual time=1,046.799..1,148.354 rows=11,960 loops=1)

  • Merge Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
23. 41.832 1,086.010 ↑ 2.9 11,960 1

Merge Join (cost=10,756.03..11,908.58 rows=35,044 width=280) (actual time=989.864..1,086.010 rows=11,960 loops=1)

  • Merge Cond: ((vbssm.scan_id = file.scan_id) AND (vbssm.composite_id = file.id))
24. 39.323 189.653 ↑ 1.3 11,960 1

Sort (cost=1,368.86..1,407.10 rows=15,294 width=248) (actual time=182.906..189.653 rows=11,960 loops=1)

  • Sort Key: vbssm.scan_id, vbssm.composite_id
  • Sort Method: quicksort Memory: 9,766kB
25. 150.330 150.330 ↑ 1.3 11,960 1

CTE Scan on vbssm (cost=0.00..305.88 rows=15,294 width=248) (actual time=34.202..150.330 rows=11,960 loops=1)

26. 332.523 854.525 ↓ 3.3 301,915 1

Sort (cost=9,387.16..9,616.30 rows=91,654 width=32) (actual time=805.529..854.525 rows=301,915 loops=1)

  • Sort Key: file.scan_id, file.id
  • Sort Method: quicksort Memory: 31,126kB
27. 522.002 522.002 ↓ 3.3 301,612 1

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

28. 8.409 58.640 ↓ 4.5 5,396 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=56.853..58.640 rows=5,396 loops=1)

  • Sort Key: vbf1.scan_id, vbf1.composite_id
  • Sort Method: quicksort Memory: 616kB
29. 50.231 50.231 ↓ 4.5 5,423 1

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

30. 4.896 5.461 ↓ 8.3 10,073 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=4.395..5.461 rows=10,073 loops=1)

  • Sort Key: vbf2.scan_id, vbf2.composite_id
  • Sort Method: quicksort Memory: 616kB
31. 0.565 0.565 ↓ 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.565 rows=5,423 loops=1)

Planning time : 2.311 ms
Execution time : 1,199.990 ms