explain.depesz.com

PostgreSQL's explain analyze made readable

Result: arsY : Optimization for: plan #UexU

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.212 65.297 ↓ 1.0 710 1

Hash Semi Join (cost=1.83..8,243.94 rows=678 width=172) (actual time=0.540..65.297 rows=710 loops=1)

  • Hash Cond: (repositories.virtual_storage = (healthy_storages_1.virtual_storage)::text)
  • Buffers: shared hit=8,022 read=39
2.          

CTE healthy_storages

3. 0.055 0.081 ↑ 3.0 3 1

HashAggregate (cost=1.14..1.23 rows=9 width=1,032) (actual time=0.075..0.081 rows=3 loops=1)

  • Group Key: node_status.shard_name, node_status.node_name
  • Buffers: shared hit=1
4. 0.026 0.026 ↑ 1.0 9 1

Seq Scan on node_status (cost=0.00..1.09 rows=9 width=1,032) (actual time=0.019..0.026 rows=9 loops=1)

  • Buffers: shared hit=1
5. 1.602 2.346 ↓ 1.0 710 1

Hash Anti Join (cost=0.32..30.99 rows=678 width=140) (actual time=0.052..2.346 rows=710 loops=1)

  • Hash Cond: ((repositories.virtual_storage = (healthy_storages.virtual_storage)::text) AND (repositories."primary" = (healthy_storages.storage)::text))
  • Buffers: shared hit=13
6. 0.714 0.714 ↑ 1.0 710 1

Seq Scan on repositories (cost=0.00..20.10 rows=710 width=140) (actual time=0.025..0.714 rows=710 loops=1)

  • Buffers: shared hit=13
7. 0.026 0.030 ↑ 3.0 3 1

Hash (cost=0.18..0.18 rows=9 width=1,032) (actual time=0.010..0.030 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.004 0.004 ↑ 3.0 3 1

CTE Scan on healthy_storages (cost=0.00..0.18 rows=9 width=1,032) (actual time=0.001..0.004 rows=3 loops=1)

9. 0.009 0.099 ↑ 3.0 3 1

Hash (cost=0.18..0.18 rows=9 width=516) (actual time=0.098..0.099 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
10. 0.090 0.090 ↑ 3.0 3 1

CTE Scan on healthy_storages healthy_storages_1 (cost=0.00..0.18 rows=9 width=516) (actual time=0.081..0.090 rows=3 loops=1)

  • Buffers: shared hit=1
11.          

SubPlan (for Hash Semi Join)

12. 1.420 59.640 ↑ 1.0 1 710

Limit (cost=12.09..12.10 rows=1 width=48) (actual time=0.083..0.084 rows=1 loops=710)

  • Buffers: shared hit=8,008 read=39
13. 7.810 58.220 ↑ 1.0 1 710

Sort (cost=12.09..12.10 rows=1 width=48) (actual time=0.082..0.082 rows=1 loops=710)

  • Sort Key: storage_repositories.generation DESC NULLS LAST, (random())
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8,008 read=39
14. 9.230 50.410 ↓ 3.0 3 710

Nested Loop (cost=0.28..12.08 rows=1 width=48) (actual time=0.029..0.071 rows=3 loops=710)

  • Join Filter: ((healthy_storages_2.storage)::text = storage_repositories.storage)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=8,008 read=39
15. 2.840 2.840 ↓ 3.0 3 710

CTE Scan on healthy_storages healthy_storages_2 (cost=0.00..0.20 rows=1 width=1,032) (actual time=0.001..0.004 rows=3 loops=710)

  • Filter: ((virtual_storage)::text = repositories.virtual_storage)
16. 38.340 38.340 ↑ 1.5 2 2,130

Index Scan using storage_repositories_pkey on storage_repositories (cost=0.28..11.84 rows=3 width=41) (actual time=0.016..0.018 rows=2 loops=2,130)

  • Index Cond: ((virtual_storage = repositories.virtual_storage) AND (relative_path = repositories.relative_path))
  • Buffers: shared hit=8,008 read=39
Planning time : 0.903 ms
Execution time : 65.716 ms