explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OuMe

Settings
# exclusive inclusive rows x rows loops node
1. 56.827 3,083.782 ↑ 45,826.0 2 1

Hash Left Join (cost=359,549.32..362,762.67 rows=91,652 width=144) (actual time=2,596.699..3,083.782 rows=2 loops=1)

  • Hash 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: 301610
2.          

CTE file

3. 29.962 2,603.520 ↓ 3.3 301,612 1

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

4. 78.288 210.568 ↓ 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=137.666..210.568 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 0.095 213.580 ↑ 59.5 2 1

Hash Join (cost=74.33..15,343.75 rows=119 width=39) (actual time=114.092..213.580 rows=2 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 0.008 212.930 ↑ 59.5 2 1

Nested Loop (cost=28.54..15,296.32 rows=119 width=47) (actual time=113.496..212.930 rows=2 loops=1)

11. 0.015 212.890 ↑ 59.5 2 1

Nested Loop (cost=28.12..14,358.53 rows=119 width=47) (actual time=113.472..212.890 rows=2 loops=1)

12. 4.624 209.491 ↑ 43.5 2 1

Nested Loop (cost=27.69..13,639.10 rows=87 width=47) (actual time=111.361..209.491 rows=2 loops=1)

13. 65.951 74.715 ↓ 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=8.858..74.715 rows=5,423 loops=1)

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
15. 130.152 130.152 ↓ 0.0 0 5,423

Index Scan using version_bom_string_search_match_scan_composite_idx on version_bom_string_search_match vbssm (cost=0.42..11.61 rows=1 width=39) (actual time=0.024..0.024 rows=0 loops=5,423)

  • Index Cond: ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (composite_id = vbf.composite_id))
  • Filter: (license_id = '39692bc6-4d1c-4466-a02c-fa6f21170587'::uuid)
  • Rows Removed by Filter: 0
16. 3.384 3.384 ↑ 1.0 1 2

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=1.692..1.692 rows=1 loops=2)

  • Index Cond: (version_bom_file_id = vbf.id)
17. 0.032 0.032 ↑ 1.0 1 2

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.87 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=2)

  • Index Cond: (id = vbe.version_bom_component_id)
18. 0.285 0.555 ↓ 1.0 1,328 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
19. 0.270 0.270 ↓ 1.0 1,328 1

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

20. 296.559 3,026.955 ↓ 3.3 301,612 1

Hash Left Join (cost=4.17..2,527.38 rows=91,654 width=88) (actual time=351.304..3,026.955 rows=301,612 loops=1)

  • Hash Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
21. 2,730.396 2,730.396 ↓ 3.3 301,612 1

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