explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jgaZ

Settings
# exclusive inclusive rows x rows loops node
1. 75.769 16,392.226 ↑ 2.9 116,248 1

Merge Join (cost=506,705.02..514,224.39 rows=340,229 width=328) (actual time=16,055.395..16,392.226 rows=116,248 loops=1)

  • Merge Cond: ((file.scan_id = vbssm.scan_id) AND (file.id = vbssm.composite_id))
2.          

CTE file

3. 27.196 8,272.250 ↓ 3.3 301,612 1

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

4. 189.683 354.008 ↓ 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=170.035..354.008 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 3.173 262.044 ↓ 4.5 5,423 1

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

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

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

11. 3.814 239.773 ↓ 4.5 5,423 1

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

12. 40.115 51.577 ↓ 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=11.541..51.577 rows=5,423 loops=1)

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

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

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

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

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

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

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

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

18.          

CTE vbssm

19. 5,481.854 5,605.833 ↓ 1.0 150,792 1

Bitmap Heap Scan on version_bom_string_search_match (cost=5,111.21..91,432.31 rows=148,488 width=433) (actual time=126.712..5,605.833 rows=150,792 loops=1)

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
21. 70.671 9,533.497 ↓ 2.2 199,722 1

Merge Left Join (cost=17,849.09..18,584.93 rows=91,652 width=80) (actual time=9,393.580..9,533.497 rows=199,722 loops=1)

  • Merge Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
  • Filter: ((vbf1.composite_id IS NOT NULL) OR (vbf2.composite_id IS NOT NULL))
  • Rows Removed by Filter: 101,890
22. 451.055 9,456.193 ↓ 3.3 301,612 1

Sort (cost=17,763.09..17,992.22 rows=91,654 width=56) (actual time=9,389.349..9,456.193 rows=301,612 loops=1)

  • Sort Key: file.scan_id, file.id
  • Sort Method: external merge Disk: 16,272kB
23. 90.131 9,005.138 ↓ 3.3 301,612 1

Merge Left Join (cost=9,473.16..10,209.01 rows=91,654 width=56) (actual time=8,839.626..9,005.138 rows=301,612 loops=1)

  • Merge Cond: ((file.scan_id = vbf2.scan_id) AND (file.root_bom_consumer_node_id = vbf2.composite_id))
24. 220.456 8,622.645 ↓ 3.3 301,612 1

Sort (cost=9,387.16..9,616.30 rows=91,654 width=32) (actual time=8,566.866..8,622.645 rows=301,612 loops=1)

  • Sort Key: file.scan_id, file.root_bom_consumer_node_id
  • Sort Method: quicksort Memory: 31,126kB
25. 8,402.189 8,402.189 ↓ 3.3 301,612 1

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

26. 26.519 292.362 ↓ 165.4 199,772 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=272.746..292.362 rows=199,772 loops=1)

  • Sort Key: vbf2.scan_id, vbf2.composite_id
  • Sort Method: quicksort Memory: 616kB
27. 265.843 265.843 ↓ 4.5 5,423 1

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

28. 6.079 6.633 ↓ 4.5 5,423 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=4.212..6.633 rows=5,423 loops=1)

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

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

30. 22.433 6,782.960 ↓ 1.0 150,791 1

Materialize (cost=32,983.37..33,725.81 rows=148,488 width=248) (actual time=6,661.754..6,782.960 rows=150,791 loops=1)

31. 935.758 6,760.527 ↓ 1.0 150,791 1

Sort (cost=32,983.37..33,354.59 rows=148,488 width=248) (actual time=6,661.750..6,760.527 rows=150,791 loops=1)

  • Sort Key: vbssm.scan_id, vbssm.composite_id
  • Sort Method: external merge Disk: 65,168kB
32. 5,824.769 5,824.769 ↓ 1.0 150,792 1

CTE Scan on vbssm (cost=0.00..2,969.76 rows=148,488 width=248) (actual time=126.725..5,824.769 rows=150,792 loops=1)

Planning time : 8.398 ms
Execution time : 16,463.072 ms