explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zHiP : Optimization for: plan #E0e

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 32,520.207 60,113.133 ↓ 408,497.0 408,497 1

Nested Loop Anti Join (cost=19,853.20..95,567.30 rows=1 width=20) (actual time=5,479.745..60,113.133 rows=408,497 loops=1)

  • Join Filter: (d.resource_id = r.id)
  • -> Index Only Scan using resource_disabled_versions_resource_id_version_md5_uniq on resource_disabled_versions d (cost=0.27..2.95 rows=1 width=37) (actual ti
2. 917.791 27,592.926 ↓ 408,497.0 408,497 1

Nested Loop (cost=19,852.93..95,561.67 rows=1 width=53) (actual time=5,479.689..27,592.926 rows=408,497 loops=1)

  • Index Cond: (version_md5 = v.version_md5)
  • Heap Fetches: 0
3. 5,797.459 14,735.991 ↓ 213,199.0 426,398 1

Hash Join (cost=19,852.50..95,560.38 rows=2 width=49) (actual time=4,661.917..14,735.991 rows=426,398 loops=1)

  • Hash Cond: ((o.version_md5 = v.version_md5) AND (r.resource_config_scope_id = v.resource_config_scope_id))
4. 7,013.430 7,014.085 ↓ 244.6 1,628,759 1

Nested Loop (cost=100.54..72,175.82 rows=6,658 width=45) (actual time=0.152..7,014.085 rows=1,628,759 loops=1)

  • -> Bitmap Heap Scan on build_resource_config_version_outputs o (cost=100.13..14379.18 rows=5122 width=41) (actual time=110.940..435.812 row
5. 0.655 0.655 ↓ 2.4 12 1

Index Scan using resources_pipeline_id on resources r (cost=0.41..23.82 rows=5 width=8) (actual time=0.074..0.655 rows=12 loops=1)

  • Index Cond: (pipeline_id = 303697)
  • Recheck Cond: (resource_id = r.id)
  • Heap Blocks: exact=140908
  • -> Bitmap Index Scan on build_resource_config_version_outputs_resource_id_idx (cost=0.00..98.85 rows=5122 width=0) (actual time=80.39
  • Index Cond: (resource_id = r.id)
6. 718.267 1,924.447 ↓ 1.0 334,040 1

Hash (cost=11,817.10..11,817.10 rows=333,524 width=45) (actual time=1,924.447..1,924.447 rows=334,040 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3783kB
7. 1,206.180 1,206.180 ↓ 1.0 334,040 1

Seq Scan on resource_config_versions v (cost=0.00..11,817.10 rows=333,524 width=45) (actual time=0.030..1,206.180 rows=334,040 loops=1)

  • Filter: (check_order <> 0)
  • Rows Removed by Filter: 753
8. 11,939.144 11,939.144 ↑ 1.0 1 426,398

Index Scan using builds_pkey on builds b (cost=0.43..0.63 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=426,398)

  • Index Cond: (id = o.build_id)
  • Filter: (status = 'succeeded'::build_status)
  • Rows Removed by Filter: 0