explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJF

Settings
# exclusive inclusive rows x rows loops node
1. 78.042 3,669.094 ↓ 1.6 150,623 1

Hash Left Join (cost=356,316.43..359,532.34 rows=91,652 width=80) (actual time=425.520..3,669.094 rows=150,623 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: 150989
2.          

CTE file

3. 24.820 3,121.482 ↓ 3.3 301,612 1

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

4. 654.244 788.205 ↓ 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=141.605..788.205 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.444 269.709 ↓ 19.3 3,377 1

Hash Join (cost=73.91..12,106.94 rows=175 width=24) (actual time=12.315..269.709 rows=3,377 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 3.767 267.628 ↓ 19.3 3,377 1

Nested Loop (cost=28.12..12,058.75 rows=175 width=32) (actual time=11.653..267.628 rows=3,377 loops=1)

11. 3.195 257.107 ↓ 19.3 3,377 1

Nested Loop (cost=27.70..10,682.39 rows=175 width=32) (actual time=11.557..257.107 rows=3,377 loops=1)

12. 77.186 85.799 ↓ 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.714..85.799 rows=5,423 loops=1)

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

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

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

  • Index Cond: (version_bom_file_id = vbf.id)
  • Filter: (match_type = 'FILE_EXACT'::text)
  • Rows Removed by Filter: 0
15. 6.754 6.754 ↑ 1.0 1 3,377

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=3,377)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
17. 0.292 0.292 ↓ 1.0 1,328 1

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

18. 89.895 3,589.552 ↓ 3.3 301,612 1

Hash Left Join (cost=6.12..2,530.62 rows=91,654 width=56) (actual time=415.345..3,589.552 rows=301,612 loops=1)

  • Hash Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
19. 3,226.009 3,226.009 ↓ 3.3 301,612 1

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

20. 1.640 273.648 ↓ 19.3 3,377 1

Hash (cost=3.50..3.50 rows=175 width=24) (actual time=273.648..273.648 rows=3,377 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
21. 272.008 272.008 ↓ 19.3 3,377 1

CTE Scan on vbf vbf1 (cost=0.00..3.50 rows=175 width=24) (actual time=12.321..272.008 rows=3,377 loops=1)

22. 0.926 1.500 ↓ 19.3 3,377 1

Hash (cost=3.50..3.50 rows=175 width=24) (actual time=1.500..1.500 rows=3,377 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
23. 0.574 0.574 ↓ 19.3 3,377 1

CTE Scan on vbf vbf2 (cost=0.00..3.50 rows=175 width=24) (actual time=0.001..0.574 rows=3,377 loops=1)

Planning time : 7.420 ms
Execution time : 3,684.491 ms