explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nwrF

Settings
# exclusive inclusive rows x rows loops node
1. 130.719 310,031.044 ↓ 252.3 150,623 1

Hash Left Join (cost=2,141,238.78..3,098,544.87 rows=597 width=5,583) (actual time=50,449.621..310,031.044 rows=150,623 loops=1)

  • Hash Cond: (vbe.id = vbs.id)
2.          

CTE file

3. 23.658 3,149.692 ↓ 3.3 301,612 1

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

4. 66.944 241.655 ↓ 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=181.862..241.655 rows=28,320 loops=1)

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

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

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

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

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
8. 178.078 309,898.795 ↓ 252.3 150,623 1

Hash Join (cost=1,796,940.80..2,754,244.64 rows=597 width=5,169) (actual time=50,448.068..309,898.795 rows=150,623 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
9. 322.539 309,720.317 ↓ 252.3 150,623 1

Nested Loop (cost=1,796,895.01..2,754,190.64 rows=597 width=5,104) (actual time=50,447.632..309,720.317 rows=150,623 loops=1)

10. 187,362.310 308,644.663 ↓ 252.3 150,623 1

Merge Join (cost=1,796,894.59..2,749,495.30 rows=597 width=3,630) (actual time=50,447.528..308,644.663 rows=150,623 loops=1)

  • Merge 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
11. 257.311 3,497.184 ↓ 3.3 301,612 1

Sort (cost=9,387.16..9,616.30 rows=91,654 width=32) (actual time=3,290.687..3,497.184 rows=301,612 loops=1)

  • Sort Key: file.scan_id
  • Sort Method: quicksort Memory: 31126kB
12. 3,239.873 3,239.873 ↓ 3.3 301,612 1

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

13. 70,795.695 117,785.169 ↓ 2,671.4 1,018,860,243 1

Materialize (cost=1,787,507.43..1,789,414.39 rows=381,393 width=3,598) (actual time=46,437.048..117,785.169 rows=1,018,860,243 loops=1)

14. 2,361.075 46,989.474 ↑ 1.2 319,896 1

Sort (cost=1,787,507.43..1,788,460.91 rows=381,393 width=3,598) (actual time=46,437.035..46,989.474 rows=319,896 loops=1)

  • Sort Key: vbf.scan_id
  • Sort Method: external merge Disk: 487144kB
15. 6,462.887 44,628.399 ↓ 1.0 394,843 1

Hash Join (cost=427,313.08..1,161,623.54 rows=381,393 width=3,598) (actual time=3,731.734..44,628.399 rows=394,843 loops=1)

  • Hash Cond: (vbe.version_bom_file_id = vbf.id)
16. 34,490.907 34,856.588 ↓ 1.0 394,843 1

Bitmap Heap Scan on version_bom_entry vbe (cost=12,324.23..287,150.69 rows=381,393 width=2,819) (actual time=414.799..34,856.588 rows=394,843 loops=1)

  • Recheck Cond: (match_type = 'FILE_EXACT'::text)
  • Heap Blocks: exact=121504
17. 365.681 365.681 ↓ 1.1 409,440 1

Bitmap Index Scan on match_type_idx (cost=0.00..12,228.88 rows=381,393 width=0) (actual time=365.681..365.681 rows=409,440 loops=1)

  • Index Cond: (match_type = 'FILE_EXACT'::text)
18. 2,091.869 3,308.924 ↑ 1.0 1,850,363 1

Hash (cost=201,228.60..201,228.60 rows=1,923,460 width=779) (actual time=3,308.924..3,308.924 rows=1,850,363 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 21308kB
19. 1,217.055 1,217.055 ↑ 1.0 1,850,363 1

Seq Scan on version_bom_file vbf (cost=0.00..201,228.60 rows=1,923,460 width=779) (actual time=0.035..1,217.055 rows=1,850,363 loops=1)

20. 753.115 753.115 ↑ 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.005..0.005 rows=1 loops=150,623)

  • Index Cond: (id = vbe.version_bom_component_id)
21. 0.237 0.400 ↓ 1.0 1,328 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 136kB
22. 0.163 0.163 ↓ 1.0 1,328 1

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

23. 0.657 1.530 ↑ 1.1 1,173 1

Hash (cost=84.22..84.22 rows=1,322 width=414) (actual time=1.530..1.530 rows=1,173 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 501kB
24. 0.873 0.873 ↑ 1.1 1,173 1

Seq Scan on version_bom_snippet vbs (cost=0.00..84.22 rows=1,322 width=414) (actual time=0.021..0.873 rows=1,173 loops=1)