explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ohMJ

Settings
# exclusive inclusive rows x rows loops node
1. 54.081 9,665.818 ↓ 11.7 116,248 1

Merge Left Join (cost=775,769.61..775,855.97 rows=9,907 width=513) (actual time=9,532.820..9,665.818 rows=116,248 loops=1)

  • Merge 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: 34544
2.          

CTE file

3. 27.649 4,264.485 ↓ 3.3 301,612 1

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

4. 74.840 81.351 ↓ 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=7.278..81.351 rows=28,320 loops=1)

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

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

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

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

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

CTE vbf

9. 1.999 60.197 ↓ 4.5 5,423 1

Hash Join (cost=73.91..20,243.02 rows=1,208 width=24) (actual time=1.450..60.197 rows=5,423 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
10. 0.803 57.860 ↓ 4.5 5,423 1

Nested Loop (cost=28.12..20,180.62 rows=1,208 width=32) (actual time=1.092..57.860 rows=5,423 loops=1)

11. 4.442 40.788 ↓ 4.5 5,423 1

Nested Loop (cost=27.70..10,680.18 rows=1,208 width=32) (actual time=1.061..40.788 rows=5,423 loops=1)

12. 8.286 9.231 ↓ 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=1.019..9.231 rows=5,423 loops=1)

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

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

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

  • Index Cond: (version_bom_file_id = vbf.id)
15. 16.269 16.269 ↑ 1.0 1 5,423

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=5,423)

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

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

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

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

18. 495.794 9,596.496 ↓ 15.2 150,792 1

Sort (cost=411,243.36..411,268.12 rows=9,907 width=489) (actual time=9,528.734..9,596.496 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.root_bom_consumer_node_id
  • Sort Method: external sort Disk: 70176kB
19. 45.175 9,100.702 ↓ 15.2 150,792 1

Merge Left Join (cost=410,499.46..410,585.82 rows=9,907 width=489) (actual time=8,953.948..9,100.702 rows=150,792 loops=1)

  • Merge Cond: ((file.scan_id = vbf1.scan_id) AND (file.id = vbf1.composite_id))
20. 637.744 8,986.552 ↓ 15.2 150,792 1

Sort (cost=410,413.46..410,438.23 rows=9,907 width=465) (actual time=8,886.639..8,986.552 rows=150,792 loops=1)

  • Sort Key: file.scan_id, file.id
  • Sort Method: external merge Disk: 69736kB
21. 1,678.583 8,348.808 ↓ 15.2 150,792 1

Hash Join (cost=197,413.50..409,755.92 rows=9,907 width=465) (actual time=2,713.474..8,348.808 rows=150,792 loops=1)

  • Hash Cond: ((file.id = vbssm.composite_id) AND (file.scan_id = vbssm.scan_id))
22. 4,427.437 4,427.437 ↓ 3.3 301,612 1

CTE Scan on file (cost=0.00..1,833.08 rows=91,654 width=32) (actual time=7.284..4,427.437 rows=301,612 loops=1)

23. 1,566.057 2,242.788 ↓ 1.0 1,407,152 1

Hash (cost=96,613.20..96,613.20 rows=1,407,020 width=433) (actual time=2,242.788..2,242.788 rows=1,407,152 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 21182kB
24. 676.731 676.731 ↓ 1.0 1,407,152 1

Seq Scan on version_bom_string_search_match vbssm (cost=0.00..96,613.20 rows=1,407,020 width=433) (actual time=0.038..676.731 rows=1,407,152 loops=1)

25. 6.061 68.975 ↓ 4.5 5,427 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=67.302..68.975 rows=5,427 loops=1)

  • Sort Key: vbf1.scan_id, vbf1.composite_id
  • Sort Method: quicksort Memory: 616kB
26. 62.914 62.914 ↓ 4.5 5,423 1

CTE Scan on vbf vbf1 (cost=0.00..24.16 rows=1,208 width=24) (actual time=1.453..62.914 rows=5,423 loops=1)

27. 14.685 15.241 ↓ 99.5 120,211 1

Sort (cost=86.00..89.02 rows=1,208 width=24) (actual time=4.077..15.241 rows=120,211 loops=1)

  • Sort Key: vbf2.scan_id, vbf2.composite_id
  • Sort Method: quicksort Memory: 616kB
28. 0.556 0.556 ↓ 4.5 5,423 1

CTE Scan on vbf vbf2 (cost=0.00..24.16 rows=1,208 width=24) (actual time=0.002..0.556 rows=5,423 loops=1)