explain.depesz.com

PostgreSQL's explain analyze made readable

Result: imgY

Settings
# exclusive inclusive rows x rows loops node
1. 59.865 8,903.424 ↓ 11.8 116,248 1

Merge Left Join (cost=775,769.61..775,856.71 rows=9,863 width=513) (actual time=8,762.067..8,903.424 rows=116,248 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) AND (vbssm.scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)))
  • Rows Removed by Filter: 34544
2.          

CTE file

3. 24.574 3,579.913 ↓ 3.3 301,612 1

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

4. 71.911 272.974 ↓ 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=206.426..272.974 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.788 226.012 ↓ 4.5 5,423 1

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

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

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

11. 4.581 212.305 ↓ 4.5 5,423 1

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

12. 53.551 61.303 ↓ 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=7.859..61.303 rows=5,423 loops=1)

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
14. 146.421 146.421 ↑ 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.027..0.027 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.205 0.418 ↓ 1.0 1,328 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
17. 0.213 0.213 ↓ 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.213 rows=1,328 loops=1)

18. 515.492 8,828.567 ↓ 15.2 150,792 1

Sort (cost=411,243.36..411,268.12 rows=9,907 width=489) (actual time=8,757.809..8,828.567 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.root_bom_consumer_node_id
  • Sort Method: external sort Disk: 70176kB
19. 43.928 8,313.075 ↓ 15.2 150,792 1

Merge Left Join (cost=410,499.46..410,585.82 rows=9,907 width=489) (actual time=8,157.910..8,313.075 rows=150,792 loops=1)

  • Merge Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
20. 685.401 8,033.848 ↓ 15.2 150,792 1

Sort (cost=410,413.46..410,438.23 rows=9,907 width=465) (actual time=7,924.640..8,033.848 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.id
  • Sort Method: external merge Disk: 69736kB
21. 1,470.190 7,348.447 ↓ 15.2 150,792 1

Hash Join (cost=197,413.50..409,755.92 rows=9,907 width=465) (actual time=5,540.354..7,348.447 rows=150,792 loops=1)

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

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

23. 1,321.060 2,178.164 ↓ 1.0 1,407,152 1

Hash (cost=96,613.20..96,613.20 rows=1,407,020 width=433) (actual time=2,178.164..2,178.164 rows=1,407,152 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 21182kB
24. 857.104 857.104 ↓ 1.0 1,407,152 1

Seq Scan on version_bom_string_search_match vbssm (cost=0.00..96,613.20 rows=1,407,020 width=433) (actual time=0.774..857.104 rows=1,407,152 loops=1)

25. 6.626 235.299 ↓ 4.5 5,427 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=233.258..235.299 rows=5,427 loops=1)

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

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

27. 14.374 14.992 ↓ 99.5 120,211 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=4.238..14.992 rows=120,211 loops=1)

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

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

Planning time : 7.598 ms
Execution time : 8,965.920 ms