explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UexU

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.330 2,657.457 ↓ 1.0 710 1

Hash Semi Join (cost=1.83..57,756.58 rows=678 width=172) (actual time=4.250..2,657.457 rows=710 loops=1)

  • Hash Cond: (repositories.virtual_storage = (healthy_storages_1.virtual_storage)::text)
  • Buffers: shared hit=29,124
2.          

CTE healthy_storages

3. 0.024 0.040 ↑ 3.0 3 1

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

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

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

  • Buffers: shared hit=1
5. 2.480 3.192 ↓ 1.0 710 1

Hash Anti Join (cost=0.32..30.99 rows=678 width=140) (actual time=0.035..3.192 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.706 0.706 ↑ 1.0 710 1

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

  • Buffers: shared hit=13
7. 0.004 0.006 ↑ 3.0 3 1

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

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

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

9. 0.010 0.055 ↑ 3.0 3 1

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

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

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

  • Buffers: shared hit=1
11.          

SubPlan (for Hash Semi Join)

12. 3.550 2,646.880 ↑ 1.0 1 710

Limit (cost=85.12..85.12 rows=1 width=48) (actual time=3.727..3.728 rows=1 loops=710)

  • Buffers: shared hit=29,110
13. 17.040 2,643.330 ↑ 1.0 1 710

Sort (cost=85.12..85.12 rows=1 width=48) (actual time=3.723..3.723 rows=1 loops=710)

  • Sort Key: storage_repositories.generation DESC NULLS LAST, (random())
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=29,110
14. 1,775.710 2,626.290 ↓ 3.0 3 710

Hash Right Join (cost=0.22..85.11 rows=1 width=48) (actual time=1.142..3.699 rows=3 loops=710)

  • Hash Cond: ((storage_repositories.virtual_storage = (healthy_storages_2.virtual_storage)::text) AND (storage_repositories.storage = (healthy_storages_2.storage)::text))
  • Filter: ((storage_repositories.relative_path IS NULL) OR (storage_repositories.relative_path = repositories.relative_path))
  • Rows Removed by Filter: 2,049
  • Buffers: shared hit=29,110
15. 842.770 842.770 ↑ 1.0 2,052 710

Seq Scan on storage_repositories (cost=0.00..66.65 rows=2,052 width=125) (actual time=0.008..1.187 rows=2,052 loops=710)

  • Filter: (virtual_storage = repositories.virtual_storage)
  • Buffers: shared hit=29,110
16. 4.970 7.810 ↓ 3.0 3 710

Hash (cost=0.20..0.20 rows=1 width=1,032) (actual time=0.011..0.011 rows=3 loops=710)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 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.002..0.004 rows=3 loops=710)

  • Filter: ((virtual_storage)::text = repositories.virtual_storage)
Planning time : 0.651 ms
Execution time : 2,658.271 ms