explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oE3E

Settings
# exclusive inclusive rows x rows loops node
1. 141,570.095 145,477.726 ↓ 188.3 150,623 1

Hash Join (cost=356,309.87..359,689.61 rows=800 width=56) (actual time=827.452..145,477.726 rows=150,623 loops=1)

  • Hash Cond: (file.scan_id = vbf.scan_id)
  • Join Filter: ((vbf.composite_id = file.root_bom_consumer_node_id) OR (vbf.composite_id = file.id))
  • Rows Removed by Join Filter: 1018393101
2.          

CTE file

3. 44.717 3,201.159 ↓ 3.3 301,612 1

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

4. 128.065 531.722 ↓ 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=418.164..531.722 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.542 362.209 ↓ 19.3 3,377 1

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

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

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

11. 0.863 349.389 ↓ 19.3 3,377 1

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

12. 45.748 71.953 ↓ 6.1 5,423 1

Bitmap Heap Scan on version_bom_file vbf_1 (cost=27.27..3,378.42 rows=883 width=32) (actual time=26.295..71.953 rows=5,423 loops=1)

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

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

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

  • Index Cond: (version_bom_file_id = vbf_1.id)
  • Filter: (match_type = 'FILE_EXACT'::text)
  • Rows Removed by Filter: 0
15. 10.131 10.131 ↑ 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.003 rows=1 loops=3,377)

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

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

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

18. 3,541.911 3,541.911 ↓ 3.3 301,612 1

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

19. 1.256 365.720 ↓ 19.3 3,377 1

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

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

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