explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tvrw

Settings
# exclusive inclusive rows x rows loops node
1. 72.298 39,288.491 ↓ 150,623.0 150,623 1

Nested Loop Left Join (cost=344,207.07..352,191.44 rows=1 width=5,583) (actual time=13.792..39,288.491 rows=150,623 loops=1)

2.          

CTE file

3. 37.589 534.861 ↓ 3.3 301,612 1

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

4. 61.964 66.882 ↓ 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=5.517..66.882 rows=28,320 loops=1)

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

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

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
8. 18,774.929 38,914.947 ↓ 150,623.0 150,623 1

Nested Loop (cost=9.55..7,986.80 rows=1 width=5,169) (actual time=13.784..38,914.947 rows=150,623 loops=1)

  • Join Filter: (vbc.version_bom_id = vb.id)
  • Rows Removed by Join Filter: 199876721
9. 198.069 4,324.603 ↓ 150,623.0 150,623 1

Nested Loop (cost=9.55..7,941.01 rows=1 width=5,104) (actual time=13.549..4,324.603 rows=150,623 loops=1)

10. 157.874 3,825.288 ↓ 150,623.0 150,623 1

Nested Loop (cost=9.13..7,933.15 rows=1 width=3,630) (actual time=13.530..3,825.288 rows=150,623 loops=1)

11. 580.662 2,868.302 ↓ 39,955.6 199,778 1

Nested Loop (cost=8.70..7,891.79 rows=5 width=811) (actual time=11.454..2,868.302 rows=199,778 loops=1)

12. 779.580 779.580 ↓ 658.5 301,612 1

CTE Scan on file (cost=0.00..2,062.22 rows=458 width=32) (actual time=5.527..779.580 rows=301,612 loops=1)

  • Filter: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
13. 301.612 1,508.060 ↑ 1.0 1 301,612

Bitmap Heap Scan on version_bom_file vbf (cost=8.70..12.72 rows=1 width=779) (actual time=0.005..0.005 rows=1 loops=301,612)

  • Recheck Cond: (((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (composite_id = file.root_bom_consumer_node_id)) OR ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (composite_id = file.id)))
  • Heap Blocks: exact=199777
14. 0.000 1,206.448 ↓ 0.0 0 301,612

BitmapOr (cost=8.70..8.70 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=301,612)

15. 603.224 603.224 ↑ 1.0 1 301,612

Bitmap Index Scan on scan_composite_index (cost=0.00..4.35 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=301,612)

  • Index Cond: ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (composite_id = file.root_bom_consumer_node_id))
16. 603.224 603.224 ↓ 0.0 0 301,612

Bitmap Index Scan on scan_composite_index (cost=0.00..4.35 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=301,612)

  • Index Cond: ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (composite_id = file.id))
17. 799.112 799.112 ↑ 1.0 1 199,778

Index Scan using idx_version_bom_entry_version_bom_file_id on version_bom_entry vbe (cost=0.43..8.26 rows=1 width=2,819) (actual time=0.004..0.004 rows=1 loops=199,778)

  • Index Cond: (version_bom_file_id = vbf.id)
  • Filter: (match_type = 'FILE_EXACT'::text)
  • Rows Removed by Filter: 0
18. 301.246 301.246 ↑ 1.0 1 150,623

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.85 rows=1 width=1,474) (actual time=0.002..0.002 rows=1 loops=150,623)

  • Index Cond: (id = vbe.version_bom_component_id)
19. 15,815.415 15,815.415 ↓ 1.0 1,328 150,623

Seq Scan on version_bom vb (cost=0.00..29.24 rows=1,324 width=65) (actual time=0.002..0.105 rows=1,328 loops=150,623)

20. 301.246 301.246 ↓ 0.0 0 150,623

Index Scan using version_bom_snippet_pkey on version_bom_snippet vbs (cost=0.28..7.39 rows=1 width=414) (actual time=0.002..0.002 rows=0 loops=150,623)

  • Index Cond: (id = vbe.id)