explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9enr

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

Hash Left Join (cost=346,442.58..349,650.51 rows=91,652 width=144) (actual time=2,639.623..3,025.466 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: 301,610
2.          

CTE file

3. 24.525 2,630.268 ↓ 3.3 301,612 1

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

4. 65.536 267.447 ↓ 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=208.030..267.447 rows=28,320 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Heap Blocks: exact=4,120
5. 201.911 201.911 ↓ 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.911..201.911 rows=46,918 loops=1)

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

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

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

CTE vbf

9. 0.022 190.805 ↓ 2.0 2 1

Nested Loop (cost=106.20..2,245.27 rows=1 width=39) (actual time=71.315..190.805 rows=2 loops=1)

  • Join Filter: (vb.id = vbc.version_bom_id)
10. 0.183 190.733 ↑ 59.5 2 1

Merge Join (cost=105.78..1,307.18 rows=119 width=63) (actual time=71.259..190.733 rows=2 loops=1)

  • Merge Cond: (vbssm.version_bom_id = vb.id)
11. 0.009 186.801 ↑ 59.5 2 1

Nested Loop (cost=1.27..142,825.06 rows=119 width=55) (actual time=67.332..186.801 rows=2 loops=1)

12. 8.189 181.042 ↑ 43.5 2 1

Nested Loop (cost=0.85..142,105.64 rows=87 width=55) (actual time=62.343..181.042 rows=2 loops=1)

13. 148.933 148.933 ↑ 1.3 11,960 1

Index Scan using version_bom_string_search_match_scan_version_bom_id_idx on version_bom_string_search_match vbssm (cost=0.42..15,786.30 rows=15,745 width=47) (actual time=43.642..148.933 rows=11,960 loops=1)

  • Filter: ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (license_id = '39692bc6-4d1c-4466-a02c-fa6f21170587'::uuid))
  • Rows Removed by Filter: 163,934
14. 23.920 23.920 ↓ 0.0 0 11,960

Index Scan using scan_composite_index on version_bom_file vbf (cost=0.43..8.01 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=11,960)

  • Index Cond: ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (composite_id = vbssm.composite_id))
15. 5.750 5.750 ↑ 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=2.874..2.875 rows=1 loops=2)

  • Index Cond: (version_bom_file_id = vbf.id)
16. 3.429 3.749 ↓ 1.0 1,328 1

Sort (cost=97.89..101.20 rows=1,324 width=8) (actual time=3.590..3.749 rows=1,328 loops=1)

  • Sort Key: vb.id
  • Sort Method: quicksort Memory: 111kB
17. 0.320 0.320 ↓ 1.0 1,328 1

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

18. 0.050 0.050 ↑ 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.025..0.025 rows=1 loops=2)

  • Index Cond: (id = vbe.version_bom_component_id)
19. 63.240 2,983.104 ↓ 3.3 301,612 1

Hash Left Join (cost=0.04..2,520.54 rows=91,654 width=88) (actual time=398.904..2,983.104 rows=301,612 loops=1)

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

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

21. 0.012 190.827 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=56) (actual time=190.827..190.827 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 190.815 190.815 ↓ 2.0 2 1

CTE Scan on vbf vbf1 (cost=0.00..0.02 rows=1 width=56) (actual time=71.320..190.815 rows=2 loops=1)

23. 0.019 0.022 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=56) (actual time=0.022..0.022 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.003 0.003 ↓ 2.0 2 1

CTE Scan on vbf vbf2 (cost=0.00..0.02 rows=1 width=56) (actual time=0.003..0.003 rows=2 loops=1)

Planning time : 20.312 ms
Execution time : 3,033.692 ms