explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AQQF : builds_pending current

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 26.092 580,836.456 ↓ 5.4 22,527 1

Subquery Scan on builds_pending (cost=1,202,471.67..1,202,596.38 rows=4,157 width=876) (actual time=580,758.549..580,836.456 rows=22,527 loops=1)

  • Output: builds_pending.abi_tag, builds_pending."position", builds_pending.package, builds_pending.version
  • Buffers: shared hit=2125822 read=73745 written=89
2. 34.789 580,810.364 ↓ 5.4 22,527 1

WindowAgg (cost=1,202,471.67..1,202,554.81 rows=4,157 width=884) (actual time=580,758.546..580,810.364 rows=22,527 loops=1)

  • Output: t.abi_tag, row_number() OVER (?), t.package, t.version, t.released
  • Buffers: shared hit=2125822 read=73745 written=89
3. 76.416 580,775.575 ↓ 5.4 22,527 1

Sort (cost=1,202,471.67..1,202,482.06 rows=4,157 width=876) (actual time=580,758.508..580,775.575 rows=22,527 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=2125822 read=73745 written=89
4. 27.497 580,699.159 ↓ 5.4 22,527 1

Subquery Scan on t (cost=1,202,086.71..1,202,221.81 rows=4,157 width=876) (actual time=580,572.197..580,699.159 rows=22,527 loops=1)

  • Output: t.abi_tag, t.released, t.package, t.version
  • Buffers: shared hit=2125819 read=73745 written=89
5. 69.199 580,671.662 ↓ 5.4 22,527 1

GroupAggregate (cost=1,202,086.71..1,202,180.24 rows=4,157 width=876) (actual time=580,572.194..580,671.662 rows=22,527 loops=1)

  • Output: q.package, q.version, v.released, min((q.abi_tag)::text)
  • Group Key: q.package, q.version, v.released
  • Buffers: shared hit=2125819 read=73745 written=89
6. 133.275 580,602.463 ↓ 10.8 45,026 1

Sort (cost=1,202,086.71..1,202,097.10 rows=4,157 width=1,062) (actual time=580,572.164..580,602.463 rows=45,026 loops=1)

  • Output: q.package, q.version, v.released, q.abi_tag
  • Sort Key: q.package, q.version, v.released
  • Sort Method: quicksort Memory: 5185kB
  • Buffers: shared hit=2125819 read=73745 written=89
7. 1,778.819 580,469.188 ↓ 10.8 45,026 1

Merge Join (cost=989,210.70..1,201,836.84 rows=4,157 width=1,062) (actual time=51,309.981..580,469.188 rows=45,026 loops=1)

  • Output: q.package, q.version, v.released, q.abi_tag
  • Merge Cond: (((v.package)::text = (q.package)::text) AND ((v.version)::text = (q.version)::text))
  • Buffers: shared hit=2125819 read=73745 written=89
8. 527,258.274 527,258.274 ↑ 1.0 1,393,851 1

Index Scan using versions_pk on public.versions v (cost=0.55..191,487.20 rows=1,406,590 width=26) (actual time=0.065..527,258.274 rows=1,393,851 loops=1)

  • Output: v.package, v.version, v.released, v.skip
  • Buffers: shared hit=2033300 read=7662 written=89
9. 64.317 51,432.095 ↑ 31.2 45,026 1

Materialize (cost=989,210.15..996,242.64 rows=1,406,498 width=1,054) (actual time=51,309.038..51,432.095 rows=45,026 loops=1)

  • Output: q.package, q.version, q.abi_tag
  • Buffers: shared hit=92519 read=66083
10. 211.214 51,367.778 ↑ 31.2 45,026 1

Sort (cost=989,210.15..992,726.39 rows=1,406,498 width=1,054) (actual time=51,309.023..51,367.778 rows=45,026 loops=1)

  • Output: q.package, q.version, q.abi_tag
  • Sort Key: q.package, q.version
  • Sort Method: quicksort Memory: 5047kB
  • Buffers: shared hit=92519 read=66083
11. 50.500 51,156.564 ↑ 31.2 45,026 1

Subquery Scan on q (cost=8,085.54..465,455.50 rows=1,406,498 width=1,054) (actual time=50,670.288..51,156.564 rows=45,026 loops=1)

  • Output: q.package, q.version, q.abi_tag
  • Buffers: shared hit=92519 read=66083
12. 10,190.505 51,106.064 ↑ 31.2 45,026 1

HashSetOp Except All (cost=8,085.54..451,390.52 rows=1,406,498 width=1,058) (actual time=50,670.285..51,106.064 rows=45,026 loops=1)

  • Output: "*SELECT* 1".package, "*SELECT* 1".version, "*SELECT* 1".abi_tag, (0)
  • Buffers: shared hit=92519 read=66083
13. 5,606.502 40,915.559 ↓ 1.6 5,857,922 1

Append (cost=8,085.54..424,483.95 rows=3,587,542 width=1,058) (actual time=1,207.444..40,915.559 rows=5,857,922 loops=1)

  • Buffers: shared hit=92519 read=66083
14. 2,902.545 16,170.763 ↓ 2.0 2,784,698 1

Subquery Scan on *SELECT* 1 (cost=8,085.54..160,397.59 rows=1,406,498 width=28) (actual time=1,207.441..16,170.763 rows=2,784,698 loops=1)

  • Output: "*SELECT* 1".package, "*SELECT* 1".version, "*SELECT* 1".abi_tag, 0
  • Buffers: shared hit=58561 read=45007
15. 4,026.606 13,268.218 ↓ 2.0 2,784,698 1

Hash Join (cost=8,085.54..146,332.61 rows=1,406,498 width=24) (actual time=1,207.438..13,268.218 rows=2,784,698 loops=1)

  • Output: v_1.package, v_1.version, b.abi_tag
  • Hash Cond: ((v_1.package)::text = (p.package)::text)
  • Buffers: shared hit=58561 read=45007
16. 8,035.806 8,035.806 ↑ 1.0 1,394,035 1

Seq Scan on public.versions v_1 (cost=0.00..118,907.38 rows=1,406,590 width=18) (actual time=0.046..8,035.806 rows=1,394,035 loops=1)

  • Output: v_1.package, v_1.version, v_1.released, v_1.skip
  • Filter: ((v_1.skip)::text = ''::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=56319 read=45006
17. 402.903 1,205.806 ↓ 2.0 397,066 1

Hash (cost=5,598.63..5,598.63 rows=198,953 width=19) (actual time=1,205.806..1,205.806 rows=397,066 loops=1)

  • Output: p.package, b.abi_tag
  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 24031kB
  • Buffers: shared hit=2242 read=1
18. 469.235 802.903 ↓ 2.0 397,066 1

Nested Loop (cost=0.00..5,598.63 rows=198,953 width=19) (actual time=0.429..802.903 rows=397,066 loops=1)

  • Output: p.package, b.abi_tag
  • Buffers: shared hit=2242 read=1
19. 0.408 0.408 ↓ 2.0 2 1

Seq Scan on public.build_abis b (cost=0.00..1.02 rows=1 width=6) (actual time=0.402..0.408 rows=2 loops=1)

  • Output: b.abi_tag, b.skip
  • Filter: ((b.skip)::text = ''::text)
  • Buffers: shared read=1
20. 333.260 333.260 ↑ 1.0 198,533 2

Seq Scan on public.packages p (cost=0.00..3,608.08 rows=198,953 width=13) (actual time=0.014..166.630 rows=198,533 loops=2)

  • Output: p.package, p.skip
  • Filter: ((p.skip)::text = ''::text)
  • Rows Removed by Filter: 18
  • Buffers: shared hit=2242
21. 3,058.796 19,138.294 ↓ 1.4 3,073,224 1

Result (cost=4.46..264,086.36 rows=2,181,044 width=1,058) (actual time=0.136..19,138.294 rows=3,073,224 loops=1)

  • Output: b_1.package, b_1.version, v_2.abi_tag, 1
  • Buffers: shared hit=33958 read=21076
22. 2,859.958 16,079.498 ↓ 1.4 3,073,224 1

Append (cost=4.46..242,275.92 rows=2,181,044 width=1,054) (actual time=0.134..16,079.498 rows=3,073,224 loops=1)

  • Buffers: shared hit=33958 read=21076
23. 2,494.166 8,660.891 ↓ 1.0 2,007,198 1

Nested Loop (cost=4.46..120,122.13 rows=2,002,060 width=25) (actual time=0.132..8,660.891 rows=2,007,198 loops=1)

  • Output: b_1.package, b_1.version, v_2.abi_tag
  • Buffers: shared hit=26143
24. 2,226.553 4,159.527 ↓ 1.0 1,003,599 1

Merge Join (cost=4.46..95,095.36 rows=1,001,030 width=19) (actual time=0.112..4,159.527 rows=1,003,599 loops=1)

  • Output: b_1.package, b_1.version
  • Merge Cond: (b_1.build_id = f.build_id)
  • Buffers: shared hit=26142
25. 1,254.039 1,254.039 ↑ 1.0 2,022,391 1

Index Only Scan using builds_pkgverid on public.builds b_1 (cost=0.43..50,688.88 rows=2,022,417 width=23) (actual time=0.063..1,254.039 rows=2,022,391 loops=1)

  • Output: b_1.build_id, b_1.package, b_1.version
  • Heap Fetches: 145
  • Buffers: shared hit=18346
26. 678.935 678.935 ↓ 1.0 1,003,599 1

Index Only Scan using files_abi on public.files f (cost=0.43..26,839.32 rows=1,001,030 width=4) (actual time=0.041..678.935 rows=1,003,599 loops=1)

  • Output: f.build_id, f.abi_tag
  • Index Cond: (f.abi_tag = 'none'::text)
  • Heap Fetches: 314
  • Buffers: shared hit=7796
27. 2,007.185 2,007.198 ↑ 1.0 2 1,003,599

Materialize (cost=0.00..1.03 rows=2 width=6) (actual time=0.000..0.002 rows=2 loops=1,003,599)

  • Output: v_2.abi_tag
  • Buffers: shared hit=1
28. 0.013 0.013 ↑ 1.0 2 1

Seq Scan on public.build_abis v_2 (cost=0.00..1.02 rows=2 width=6) (actual time=0.011..0.013 rows=2 loops=1)

  • Output: v_2.abi_tag
  • Buffers: shared hit=1
29. 2,475.003 4,558.649 ↓ 6.0 1,066,026 1

Merge Left Join (cost=4.40..100,343.35 rows=178,984 width=237) (actual time=0.933..4,558.649 rows=1,066,026 loops=1)

  • Output: b_2.package, b_2.version, COALESCE(f_1.abi_tag, b_2.abi_tag)
  • Merge Cond: (b_2.build_id = f_1.build_id)
  • Filter: ((f_1.build_id IS NULL) OR ((f_1.abi_tag)::text <> 'none'::text))
  • Rows Removed by Filter: 1003599
  • Buffers: shared hit=7815 read=21076
30. 1,464.538 1,464.538 ↓ 1.0 2,022,441 1

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

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

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

  • Output: f_1.build_id, f_1.abi_tag
  • Heap Fetches: 485
  • Buffers: shared hit=7812
Planning time : 194.136 ms
Execution time : 580,914.943 ms