explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OuI7

Settings
# exclusive inclusive rows x rows loops node
1. 0.452 1,927.193 ↓ 435.0 870 1

Unique (cost=11,311.17..11,311.20 rows=2 width=160) (actual time=1,926.583..1,927.193 rows=870 loops=1)

2. 2.352 1,926.741 ↓ 549.5 1,099 1

Sort (cost=11,311.17..11,311.17 rows=2 width=160) (actual time=1,926.581..1,926.741 rows=1,099 loops=1)

  • Sort Key: vbf.file_name, vbc.project_name, vbc.release_version, vbe.match_type, vbe.usage
  • Sort Method: quicksort Memory: 200kB
3. 0.380 1,924.389 ↓ 549.5 1,099 1

Append (cost=288.79..11,311.16 rows=2 width=160) (actual time=1,271.720..1,924.389 rows=1,099 loops=1)

4. 0.026 1,283.331 ↓ 6.0 6 1

Nested Loop (cost=288.79..316.09 rows=1 width=69) (actual time=1,271.720..1,283.331 rows=6 loops=1)

5. 0.035 1,279.243 ↓ 6.0 6 1

Nested Loop (cost=288.37..308.44 rows=1 width=56) (actual time=1,270.888..1,279.243 rows=6 loops=1)

6. 0.010 1,270.010 ↓ 6.0 6 1

Nested Loop (cost=287.94..299.99 rows=1 width=21) (actual time=1,269.900..1,270.010 rows=6 loops=1)

7. 0.025 0.025 ↑ 1.0 1 1

Index Scan using version_bom_release_id_idx on version_bom vb (cost=0.28..8.29 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: (release_id = '3e6d43b5-0a7e-4181-9bc5-c56f36b7a9be'::uuid)
8. 0.137 1,269.975 ↓ 6.0 6 1

Bitmap Heap Scan on version_bom_file vbf (cost=287.67..291.68 rows=1 width=29) (actual time=1,269.870..1,269.975 rows=6 loops=1)

  • Recheck Cond: ((file_name ~~ '%zuul%'::text) AND (version_bom_id = vb.id))
  • Heap Blocks: exact=6
9. 0.282 1,269.838 ↓ 0.0 0 1

BitmapAnd (cost=287.67..287.67 rows=1 width=0) (actual time=1,269.838..1,269.838 rows=0 loops=1)

10. 25.756 25.756 ↑ 19.0 6 1

Bitmap Index Scan on idx_version_bom_file_name (cost=0.00..96.85 rows=114 width=0) (actual time=25.756..25.756 rows=6 loops=1)

  • Index Cond: (file_name ~~ '%zuul%'::text)
11. 1,243.800 1,243.800 ↓ 24.5 72,276 1

Bitmap Index Scan on idx_version_bom_file_version_bom_id (cost=0.00..190.54 rows=2,948 width=0) (actual time=1,243.800..1,243.800 rows=72,276 loops=1)

  • Index Cond: (version_bom_id = vb.id)
12. 9.198 9.198 ↑ 1.0 1 6

Index Scan using idx_version_bom_entry_version_bom_file_id on version_bom_entry vbe (cost=0.43..8.45 rows=1 width=51) (actual time=1.533..1.533 rows=1 loops=6)

  • Index Cond: (version_bom_file_id = vbf.id)
13. 4.062 4.062 ↑ 1.0 1 6

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.63 rows=1 width=29) (actual time=0.677..0.677 rows=1 loops=6)

  • Index Cond: (id = vbe.version_bom_component_id)
14. 1.407 640.678 ↓ 1,093.0 1,093 1

Nested Loop (cost=1.13..10,995.05 rows=1 width=69) (actual time=4.234..640.678 rows=1,093 loops=1)

15. 0.516 137.551 ↓ 1,110.0 1,110 1

Nested Loop (cost=0.70..10,987.38 rows=1 width=64) (actual time=4.193..137.551 rows=1,110 loops=1)

16. 0.195 104.080 ↓ 15.0 15 1

Nested Loop (cost=0.28..10,603.87 rows=1 width=29) (actual time=4.129..104.080 rows=15 loops=1)

  • Join Filter: (vbc_1.version_bom_id = vb_1.id)
  • Rows Removed by Join Filter: 638
17. 0.014 0.014 ↑ 1.0 1 1

Index Scan using version_bom_release_id_idx on version_bom vb_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=1)

  • Index Cond: (release_id = '3e6d43b5-0a7e-4181-9bc5-c56f36b7a9be'::uuid)
18. 103.871 103.871 ↓ 43.5 653 1

Seq Scan on version_bom_component vbc_1 (cost=0.00..10,595.39 rows=15 width=37) (actual time=0.024..103.871 rows=653 loops=1)

  • Filter: (project_name ~~ '%erl%'::text)
  • Rows Removed by Filter: 209618
19. 32.955 32.955 ↑ 1.3 74 15

Index Scan using idx_version_bom_entry_version_bom_component_id on version_bom_entry vbe_1 (cost=0.43..382.53 rows=99 width=51) (actual time=0.967..2.197 rows=74 loops=15)

  • Index Cond: (version_bom_component_id = vbc_1.id)
20. 501.720 501.720 ↑ 1.0 1 1,110

Index Scan using version_bom_file_pkey on version_bom_file vbf_1 (cost=0.43..7.66 rows=1 width=21) (actual time=0.451..0.452 rows=1 loops=1,110)

  • Index Cond: (id = vbe_1.version_bom_file_id)
Planning time : 19.989 ms
Execution time : 1,927.616 ms