explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.699 9.828 ↓ 1.0 710 1

Nested Loop (cost=1.19..8,511.15 rows=706 width=172) (actual time=0.197..9.828 rows=710 loops=1)

  • Join Filter: (repositories.virtual_storage = (healthy_storages_1.virtual_storage)::text)
  • Buffers: shared hit=2,144
2.          

CTE healthy_storages

3. 0.006 0.055 ↑ 1.0 1 1

Unique (cost=1.12..1.13 rows=1 width=1,032) (actual time=0.049..0.055 rows=1 loops=1)

  • Buffers: shared hit=1
4. 0.020 0.049 ↓ 3.0 3 1

Sort (cost=1.12..1.13 rows=1 width=1,032) (actual time=0.047..0.049 rows=3 loops=1)

  • Sort Key: node_status.shard_name
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
5. 0.029 0.029 ↓ 3.0 3 1

Seq Scan on node_status (cost=0.00..1.11 rows=1 width=1,032) (actual time=0.024..0.029 rows=3 loops=1)

  • Filter: ((node_name)::text = 'file-praefect-01'::text)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
6. 0.008 0.068 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=516) (actual time=0.067..0.068 rows=1 loops=1)

  • Group Key: (healthy_storages_1.virtual_storage)::text
  • Buffers: shared hit=1
7. 0.060 0.060 ↑ 1.0 1 1

CTE Scan on healthy_storages healthy_storages_1 (cost=0.00..0.02 rows=1 width=516) (actual time=0.054..0.060 rows=1 loops=1)

  • Buffers: shared hit=1
8. 0.402 0.541 ↓ 1.0 710 1

Hash Anti Join (cost=0.04..30.93 rows=706 width=140) (actual time=0.047..0.541 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
9. 0.133 0.133 ↑ 1.0 710 1

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

  • Buffers: shared hit=13
10. 0.004 0.006 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=1,032) (actual time=0.006..0.006 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.002 0.002 ↑ 1.0 1 1

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

12.          

SubPlan (for Nested Loop)

13. 0.000 8.520 ↑ 1.0 1 710

Limit (cost=11.99..12.00 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=710)

  • Buffers: shared hit=2,130
14. 0.710 8.520 ↑ 2.0 1 710

Unique (cost=11.99..12.00 rows=2 width=32) (actual time=0.012..0.012 rows=1 loops=710)

  • Buffers: shared hit=2,130
15. 1.420 7.810 ↑ 2.0 1 710

Sort (cost=11.99..12.00 rows=2 width=32) (actual time=0.011..0.011 rows=1 loops=710)

  • Sort Key: "*SELECT* 1".storage
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,130
16. 0.000 6.390 ↑ 1.0 2 710

Append (cost=11.91..11.98 rows=2 width=32) (actual time=0.007..0.009 rows=2 loops=710)

  • Buffers: shared hit=2,130
17. 0.000 4.970 ↑ 1.0 1 710

Subquery Scan on *SELECT* 1 (cost=11.91..11.93 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=710)

  • Buffers: shared hit=2,130
18. 0.710 4.970 ↑ 1.0 1 710

Sort (cost=11.91..11.92 rows=1 width=40) (actual time=0.007..0.007 rows=1 loops=710)

  • Sort Key: storage_repositories.generation DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,130
19. 1.420 4.260 ↑ 1.0 1 710

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

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

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

  • Filter: ((virtual_storage)::text = repositories.virtual_storage)
21. 2.840 2.840 ↑ 3.0 1 710

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=710)

  • Index Cond: ((virtual_storage = repositories.virtual_storage) AND (relative_path = repositories.relative_path))
  • Buffers: shared hit=2,130
22. 0.710 1.420 ↑ 1.0 1 710

Subquery Scan on *SELECT* 2 (cost=0.03..0.05 rows=1 width=516) (actual time=0.001..0.002 rows=1 loops=710)

23. 0.710 0.710 ↑ 1.0 1 710

Sort (cost=0.03..0.04 rows=1 width=524) (actual time=0.001..0.001 rows=1 loops=710)

  • Sort Key: (random())
  • Sort Method: quicksort Memory: 25kB
24. 0.000 0.000 ↑ 1.0 1 710

CTE Scan on healthy_storages healthy_storages_3 (cost=0.00..0.02 rows=1 width=524) (actual time=0.000..0.000 rows=1 loops=710)

Planning time : 0.954 ms
Execution time : 10.020 ms