explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aKUO

Settings
# exclusive inclusive rows x rows loops node
1. 0.155 17,040.938 ↑ 6.2 38 1

Sort (cost=80,752.51..80,753.10 rows=235 width=78) (actual time=17,040.922..17,040.938 rows=38 loops=1)

  • Sort Key: (round(((((count(DISTINCT o.id) - count(DISTINCT w.id)))::numeric / NULLIF(((count(DISTINCT wshift.id))::numeric * 1.0), '0'::numeric)) * '10'::numeric), 1)) DESC
  • Sort Method: quicksort Memory: 27kB
2. 1,520.020 17,040.783 ↑ 6.2 38 1

GroupAggregate (cost=79,603.55..80,743.25 rows=235 width=78) (actual time=13,982.464..17,040.783 rows=38 loops=1)

  • Group Key: s.name
3. 3,659.221 15,520.763 ↓ 33.4 2,112,138 1

Sort (cost=79,603.55..79,761.83 rows=63,313 width=38) (actual time=13,967.818..15,520.763 rows=2,112,138 loops=1)

  • Sort Key: s.name
  • Sort Method: external merge Disk: 71080kB
4. 2,780.613 11,861.542 ↓ 33.4 2,112,138 1

Gather (cost=29,436.37..74,554.27 rows=63,313 width=38) (actual time=69.285..11,861.542 rows=2,112,138 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 3,180.760 9,080.929 ↓ 26.7 704,046 3

Nested Loop Left Join (cost=28,436.37..67,222.97 rows=26,380 width=38) (actual time=64.154..9,080.929 rows=704,046 loops=3)

6. 4,311.005 5,900.166 ↓ 26.7 704,046 3

Nested Loop Left Join (cost=28,435.95..51,821.03 rows=26,380 width=38) (actual time=64.140..5,900.166 rows=704,046 loops=3)

7. 1,515.962 1,589.157 ↓ 26.7 704,046 3

Nested Loop Left Join (cost=28,435.53..36,221.23 rows=26,380 width=30) (actual time=64.101..1,589.157 rows=704,046 loops=3)

8. 0.426 64.754 ↓ 4.4 93 3

Merge Join (cost=28,435.10..28,438.46 rows=21 width=30) (actual time=64.055..64.754 rows=93 loops=3)

  • Merge Cond: (ls.subset_id = s.id)
9. 0.274 63.736 ↓ 4.4 93 3

Sort (cost=28,394.35..28,394.40 rows=21 width=16) (actual time=63.595..63.736 rows=93 loops=3)

  • Sort Key: ls.subset_id
  • Sort Method: quicksort Memory: 29kB
10. 1.004 63.462 ↓ 4.4 93 3

Nested Loop (cost=148.40..28,393.89 rows=21 width=16) (actual time=54.047..63.462 rows=93 loops=3)

11. 1.435 62.447 ↑ 44.5 74 3

Nested Loop (cost=148.12..26,771.62 rows=3,293 width=24) (actual time=54.003..62.447 rows=74 loops=3)

12. 46.799 60.995 ↑ 44.5 74 3

Parallel Bitmap Heap Scan on order_delivery o (cost=147.69..14,628.41 rows=3,293 width=16) (actual time=53.926..60.995 rows=74 loops=3)

  • Recheck Cond: ((order_state)::text = 'AVAILABLE'::text)
  • Heap Blocks: exact=7651
13. 14.196 14.196 ↓ 9.7 76,465 1

Bitmap Index Scan on idx_order_delivery_order_state (cost=0.00..145.71 rows=7,904 width=0) (actual time=14.195..14.196 rows=76,465 loops=1)

  • Index Cond: ((order_state)::text = 'AVAILABLE'::text)
14. 0.017 0.017 ↑ 1.0 1 221

Index Only Scan using pk_locality on locality oo (cost=0.43..3.69 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=221)

  • Index Cond: (id = o.origin_id)
  • Heap Fetches: 76
15. 0.011 0.011 ↑ 1.0 1 221

Index Only Scan using locality_subset_pkey on locality_subset ls (cost=0.29..0.39 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=221)

  • Index Cond: (locality_id = oo.id)
  • Heap Fetches: 103
16. 0.366 0.592 ↓ 1.0 238 3

Sort (cost=40.75..41.34 rows=235 width=22) (actual time=0.453..0.592 rows=238 loops=3)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 43kB
17. 0.226 0.226 ↓ 1.0 243 3

Seq Scan on subset s (cost=0.00..31.50 rows=235 width=22) (actual time=0.021..0.226 rows=243 loops=3)

18. 8.441 8.441 ↓ 4.8 7,543 280

Index Scan using idx_worker_subset_subset_id on worker_subset ws (cost=0.42..212.31 rows=1,583 width=16) (actual time=0.013..8.441 rows=7,543 loops=280)

  • Index Cond: (subset_id = s.id)
19. 0.004 0.004 ↓ 0.0 0 2,112,138

Index Scan using pk_worker on worker w (cost=0.42..0.59 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=2,112,138)

  • Index Cond: (id = ws.worker_id)
  • Filter: (((worker_block_state)::text = 'UNBLOCKED'::text) AND ((worker_shift_state)::text = 'ON_SHIFT'::text) AND ((worker_connectivity_state)::text = 'ONLINE'::text) AND (
  • Rows Removed by Filter: 1
20. 0.003 0.003 ↓ 0.0 0 2,112,138

Index Scan using pk_worker on worker wshift (cost=0.42..0.58 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,112,138)

  • Index Cond: (id = ws.worker_id)
  • Filter: ((worker_shift_state)::text = 'ON_SHIFT'::text)
  • Rows Removed by Filter: 1