explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bmx8 : Optimization for: builds_pending current; plan #AQQF

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 26.022 540,556.020 ↓ 22,531.0 22,531 1

Subquery Scan on test_pending (cost=615,816.16..615,816.19 rows=1 width=58) (actual time=540,476.565..540,556.020 rows=22,531 loops=1)

  • Output: test_pending.abi_tag, test_pending."position", test_pending.package, test_pending.version
  • Buffers: shared hit=2199476 read=81983 written=56
2. 35.572 540,529.998 ↓ 22,531.0 22,531 1

WindowAgg (cost=615,816.16..615,816.18 rows=1 width=66) (actual time=540,476.562..540,529.998 rows=22,531 loops=1)

  • Output: t.abi_tag, row_number() OVER (?), t.package, t.version, t.released
  • Buffers: shared hit=2199476 read=81983 written=56
3. 248.194 540,494.426 ↓ 22,531.0 22,531 1

Sort (cost=615,816.16..615,816.16 rows=1 width=58) (actual time=540,476.531..540,494.426 rows=22,531 loops=1)

  • Output: t.abi_tag, t.released, t.package, t.version
  • Sort Key: t.abi_tag, t.released
  • Sort Method: quicksort Memory: 2594kB
  • Buffers: shared hit=2199476 read=81983 written=56
4. 39.593 540,246.232 ↓ 22,531.0 22,531 1

Subquery Scan on t (cost=309,423.18..615,816.15 rows=1 width=58) (actual time=26,929.418..540,246.232 rows=22,531 loops=1)

  • Output: t.abi_tag, t.released, t.package, t.version
  • Buffers: shared hit=2199476 read=81983 written=56
5. 164.043 540,206.639 ↓ 22,531.0 22,531 1

GroupAggregate (cost=309,423.18..615,816.14 rows=1 width=58) (actual time=26,929.416..540,206.639 rows=22,531 loops=1)

  • Output: v.package, v.version, v.released, min((a.abi_tag)::text)
  • Group Key: v.package, v.version
  • Buffers: shared hit=2199476 read=81983 written=56
6. 215.406 540,042.596 ↓ 45,034.0 45,034 1

Nested Loop (cost=309,423.18..615,816.12 rows=1 width=32) (actual time=26,906.571..540,042.596 rows=45,034 loops=1)

  • Output: v.package, v.version, v.released, a.abi_tag
  • Buffers: shared hit=2199476 read=81983 written=56
7. 10,048.563 534,108.065 ↓ 45,753.0 45,753 1

Merge Left Join (cost=309,422.76..615,815.67 rows=1 width=32) (actual time=26,902.740..534,108.065 rows=45,753 loops=1)

  • Output: v.package, v.version, v.released, a.abi_tag
  • Merge Cond: (((v.package)::text = (b.package)::text) AND ((v.version)::text = (b.version)::text))
  • Join Filter: (((COALESCE(f.abi_tag, b.abi_tag))::text = (a.abi_tag)::text) OR ((f.abi_tag)::text = 'none'::text))
  • Rows Removed by Join Filter: 1142542
  • Filter: (b.build_id IS NULL)
  • Rows Removed by Filter: 2996708
  • Buffers: shared hit=2017713 read=80396 written=54
8. 6,453.094 493,619.340 ↑ 1.1 2,788,078 1

Nested Loop (cost=0.56..259,292.86 rows=2,963,451 width=32) (actual time=16.738..493,619.340 rows=2,788,078 loops=1)

  • Output: v.package, v.version, v.released, a.abi_tag
  • Buffers: shared hit=2017657 read=51561 written=54
9. 484,378.168 484,378.168 ↑ 2.1 1,394,039 1

Index Scan using versions_pk on public.versions v (cost=0.56..222,248.70 rows=2,963,451 width=26) (actual time=0.053..484,378.168 rows=1,394,039 loops=1)

  • Output: v.package, v.version, v.released, v.skip
  • Filter: ((v.skip)::text = ''::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2017657 read=51560 written=54
10. 2,772.174 2,788.078 ↓ 2.0 2 1,394,039

Materialize (cost=0.00..1.03 rows=1 width=6) (actual time=0.001..0.002 rows=2 loops=1,394,039)

  • Output: a.abi_tag
  • Buffers: shared read=1
11. 15.904 15.904 ↓ 2.0 2 1

Seq Scan on public.build_abis a (cost=0.00..1.02 rows=1 width=6) (actual time=15.897..15.904 rows=2 loops=1)

  • Output: a.abi_tag
  • Filter: ((a.skip)::text = ''::text)
  • Buffers: shared read=1
12. 19,229.446 30,440.162 ↓ 2.0 4,139,249 1

Sort (cost=309,422.20..314,478.24 rows=2,022,417 width=34) (actual time=26,826.404..30,440.162 rows=4,139,249 loops=1)

  • Output: b.build_id, b.package, b.version, b.abi_tag, f.abi_tag
  • Sort Key: b.package, b.version
  • Sort Method: quicksort Memory: 218097kB
  • Buffers: shared hit=56 read=28835
13. 3,572.608 11,210.716 ↓ 1.0 2,069,625 1

Merge Left Join (cost=4.40..97,597.79 rows=2,022,417 width=34) (actual time=52.113..11,210.716 rows=2,069,625 loops=1)

  • Output: b.build_id, b.package, b.version, b.abi_tag, f.abi_tag
  • Merge Cond: (b.build_id = f.build_id)
  • Buffers: shared hit=56 read=28835
14. 6,032.812 6,032.812 ↓ 1.0 2,022,441 1

Index Only Scan using builds_pkgverabi on public.builds b (cost=0.55..53,748.11 rows=2,022,417 width=29) (actual time=29.107..6,032.812 rows=2,022,441 loops=1)

  • Output: b.build_id, b.package, b.version, b.abi_tag
  • Heap Fetches: 178
  • Buffers: shared hit=3 read=21076
15. 1,605.296 1,605.296 ↑ 1.0 1,098,180 1

Index Only Scan using files_abi on public.files f (cost=0.43..25,065.86 rows=1,098,223 width=9) (actual time=22.997..1,605.296 rows=1,098,180 loops=1)

  • Output: f.build_id, f.abi_tag
  • Heap Fetches: 485
  • Buffers: shared hit=53 read=7759
16. 5,719.125 5,719.125 ↑ 1.0 1 45,753

Index Scan using packages_pk on public.packages p (cost=0.42..0.44 rows=1 width=13) (actual time=0.124..0.125 rows=1 loops=45,753)

  • Output: p.package, p.skip
  • Index Cond: ((p.package)::text = (v.package)::text)
  • Filter: ((p.skip)::text = ''::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=181763 read=1587 written=2
Planning time : 185.761 ms
Execution time : 540,618.786 ms