explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XGIo : Optimization for: Optimization for: plan #UexU; plan #arsY

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.309 10.729 ↓ 1.0 710 1

Hash Semi Join (cost=1.83..8,693.11 rows=678 width=172) (actual time=0.069..10.729 rows=710 loops=1)

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

CTE healthy_storages

3. 0.011 0.019 ↑ 3.0 3 1

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

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

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

  • Buffers: shared hit=1
5. 0.324 0.457 ↓ 1.0 710 1

Hash Anti Join (cost=0.32..30.99 rows=678 width=140) (actual time=0.009..0.457 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.130 0.130 ↑ 1.0 710 1

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

  • Buffers: shared hit=13
7. 0.002 0.003 ↑ 3.0 3 1

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

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

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

9. 0.002 0.023 ↑ 3.0 3 1

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

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

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

  • Buffers: shared hit=1
11.          

SubPlan (for Hash Semi Join)

12. 0.710 9.940 ↑ 1.0 1 710

Limit (cost=12.76..12.76 rows=1 width=48) (actual time=0.013..0.014 rows=1 loops=710)

  • Buffers: shared hit=2,376
13. 1.420 9.230 ↑ 10.0 1 710

Sort (cost=12.76..12.78 rows=10 width=48) (actual time=0.013..0.013 rows=1 loops=710)

  • Sort Key: t.generation DESC, (random())
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=2,376
14. 0.710 7.810 ↑ 2.5 4 710

Subquery Scan on t (cost=12.48..12.71 rows=10 width=48) (actual time=0.010..0.011 rows=4 loops=710)

  • Buffers: shared hit=2,376
15. 1.420 7.100 ↑ 2.5 4 710

HashAggregate (cost=12.48..12.58 rows=10 width=40) (actual time=0.010..0.010 rows=4 loops=710)

  • Group Key: ((healthy_storages_2.storage)::character varying), storage_repositories.generation
  • Buffers: shared hit=2,376
16. 0.710 5.680 ↑ 2.5 4 710

Append (cost=0.28..12.43 rows=10 width=40) (actual time=0.006..0.008 rows=4 loops=710)

  • Buffers: shared hit=2,376
17. 0.710 4.260 ↑ 1.0 1 710

Limit (cost=0.28..12.08 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=710)

  • Buffers: shared hit=2,376
18. 0.382 3.550 ↑ 1.0 1 710

Nested Loop (cost=0.28..12.08 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=710)

  • Join Filter: ((healthy_storages_2.storage)::text = storage_repositories.storage)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=2,376
19. 0.000 0.000 ↑ 1.0 1 710

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

  • Filter: ((virtual_storage)::text = repositories.virtual_storage)
20. 3.168 3.168 ↑ 3.0 1 792

Index Scan using storage_repositories_pkey on storage_repositories (cost=0.28..11.84 rows=3 width=41) (actual time=0.004..0.004 rows=1 loops=792)

  • Index Cond: ((virtual_storage = repositories.virtual_storage) AND (relative_path = repositories.relative_path))
  • Buffers: shared hit=2,376
21. 0.000 0.710 ↑ 3.0 3 710

Subquery Scan on *SELECT* 2 (cost=0.00..0.29 rows=9 width=524) (actual time=0.000..0.001 rows=3 loops=710)

22. 0.710 0.710 ↑ 3.0 3 710

CTE Scan on healthy_storages healthy_storages_3 (cost=0.00..0.18 rows=9 width=520) (actual time=0.000..0.001 rows=3 loops=710)

Planning time : 0.312 ms
Execution time : 10.844 ms