explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A0Lt5

Settings
# exclusive inclusive rows x rows loops node
1. 54.321 2,231.977 ↑ 2.8 53,954 1

Merge Join (cost=480,680.67..484,523.10 rows=151,333 width=328) (actual time=1,987.372..2,231.977 rows=53,954 loops=1)

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

CTE file

3. 25.102 348.419 ↓ 3.3 301,612 1

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

4. 40.571 45.261 ↓ 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=5.319..45.261 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.708 46.713 ↓ 4.5 5,425 1

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

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

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

11. 3.048 33.568 ↓ 4.5 5,425 1

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

12. 8.050 8.820 ↓ 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=0.881..8.820 rows=5,425 loops=1)

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

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

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

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

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

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

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

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

18.          

CTE vbssm

19. 148.813 397.639 ↓ 1.0 68,856 1

Bitmap Heap Scan on version_bom_string_search_match (cost=5,090.60..91,782.92 rows=66,047 width=433) (actual time=250.558..397.639 rows=68,856 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Filter: (match_type = 'LICENSE_VSL'::text)
  • Rows Removed by Filter: 81,936
  • Heap Blocks: exact=8,779
20. 248.826 248.826 ↓ 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=248.826..248.826 rows=150,792 loops=1)

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
21. 82.322 1,230.218 ↓ 2.2 199,724 1

Merge Left Join (cost=17,849.09..18,584.93 rows=91,652 width=80) (actual time=1,076.585..1,230.218 rows=199,724 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,888
22. 397.575 1,141.879 ↓ 3.3 301,612 1

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

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

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

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

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

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

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

26. 21.616 70.606 ↓ 165.4 199,774 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=53.319..70.606 rows=199,774 loops=1)

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

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

28. 5.490 6.017 ↓ 4.5 5,425 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=4.178..6.017 rows=5,425 loops=1)

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

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

30. 461.247 947.438 ↓ 1.0 68,856 1

Sort (cost=6,608.40..6,773.52 rows=66,047 width=248) (actual time=910.724..947.438 rows=68,856 loops=1)

  • Sort Key: vbssm.scan_id, vbssm.composite_id
  • Sort Method: external sort Disk: 30,024kB
31. 486.191 486.191 ↓ 1.0 68,856 1

CTE Scan on vbssm (cost=0.00..1,320.94 rows=66,047 width=248) (actual time=250.562..486.191 rows=68,856 loops=1)

Planning time : 1.400 ms
Execution time : 2,278.979 ms