explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 6.878 30,469.116 ↓ 5.5 22,531 1

Subquery Scan on builds_pending (cost=993,717.40..993,839.35 rows=4,065 width=876) (actual time=30,432.572..30,469.116 rows=22,531 loops=1)

  • Output: builds_pending.abi_tag, builds_pending."position", builds_pending.package, builds_pending.version
  • Buffers: shared hit=3217409
2. 21.434 30,462.238 ↓ 5.5 22,531 1

WindowAgg (cost=993,717.40..993,798.70 rows=4,065 width=876) (actual time=30,432.569..30,462.238 rows=22,531 loops=1)

  • Output: t.abi_tag, row_number() OVER (?), t.package, t.version, t.released
  • Buffers: shared hit=3217409
3. 82.889 30,440.804 ↓ 5.5 22,531 1

Sort (cost=993,717.40..993,727.57 rows=4,065 width=876) (actual time=30,432.554..30,440.804 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=3217409
4. 6.627 30,357.915 ↓ 5.5 22,531 1

Subquery Scan on t (cost=993,392.43..993,473.73 rows=4,065 width=876) (actual time=30,333.355..30,357.915 rows=22,531 loops=1)

  • Output: t.abi_tag, t.released, t.package, t.version
  • Buffers: shared hit=3217406
5. 100.586 30,351.288 ↓ 5.5 22,531 1

HashAggregate (cost=993,392.43..993,433.08 rows=4,065 width=1,062) (actual time=30,333.353..30,351.288 rows=22,531 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=3217406
6. 1,089.602 30,250.702 ↓ 11.1 45,034 1

Merge Join (cost=931,522.36..993,351.78 rows=4,065 width=1,062) (actual time=26,715.646..30,250.702 rows=45,034 loops=1)

  • Output: q.package, q.version, q.abi_tag, v.released
  • Merge Cond: (((v.package)::text = (q.package)::text) AND ((v.version)::text = (q.version)::text))
  • Buffers: shared hit=3217406
7. 2,406.629 2,406.629 ↑ 1.0 1,393,855 1

Index Scan using versions_pk on public.versions v (cost=0.43..40,880.43 rows=1,394,040 width=26) (actual time=0.017..2,406.629 rows=1,393,855 loops=1)

  • Output: v.package, v.version, v.released, v.skip
  • Buffers: shared hit=1381534
8. 14.462 26,754.471 ↑ 31.0 45,034 1

Materialize (cost=931,521.93..938,491.21 rows=1,393,857 width=1,054) (actual time=26,715.169..26,754.471 rows=45,034 loops=1)

  • Output: q.package, q.version, q.abi_tag
  • Buffers: shared hit=1835872
9. 275.483 26,740.009 ↑ 31.0 45,034 1

Sort (cost=931,521.93..935,006.57 rows=1,393,857 width=1,054) (actual time=26,715.159..26,740.009 rows=45,034 loops=1)

  • Output: q.package, q.version, q.abi_tag
  • Sort Key: q.package, q.version
  • Sort Method: quicksort Memory: 5048kB
  • Buffers: shared hit=1835872
10. 19.627 26,464.526 ↑ 31.0 45,034 1

Subquery Scan on q (cost=8,068.73..412,564.23 rows=1,393,857 width=1,054) (actual time=25,889.411..26,464.526 rows=45,034 loops=1)

  • Output: q.package, q.version, q.abi_tag
  • Buffers: shared hit=1835872
11. 8,583.175 26,444.899 ↑ 31.0 45,034 1

HashSetOp Except All (cost=8,068.73..398,625.66 rows=1,393,857 width=226) (actual time=25,889.409..26,444.899 rows=45,034 loops=1)

  • Output: "*SELECT* 1".package, "*SELECT* 1".version, "*SELECT* 1".abi_tag, (0)
  • Buffers: shared hit=1835872
12. 1,307.873 17,861.724 ↓ 1.6 5,857,930 1

Append (cost=8,068.73..371,814.12 rows=3,574,872 width=226) (actual time=572.012..17,861.724 rows=5,857,930 loops=1)

  • Buffers: shared hit=1835872
13. 1,007.750 5,076.958 ↓ 2.0 2,784,706 1

Subquery Scan on *SELECT* 1 (cost=8,068.73..70,054.01 rows=1,393,857 width=236) (actual time=572.011..5,076.958 rows=2,784,706 loops=1)

  • Output: "*SELECT* 1".package, "*SELECT* 1".version, "*SELECT* 1".abi_tag, 0
  • Buffers: shared hit=13694
14. 2,762.823 4,069.208 ↓ 2.0 2,784,706 1

Hash Join (cost=8,068.73..56,115.44 rows=1,393,857 width=236) (actual time=572.008..4,069.208 rows=2,784,706 loops=1)

  • Output: v_1.package, v_1.version, b.abi_tag
  • Hash Cond: ((v_1.package)::text = (p.package)::text)
  • Buffers: shared hit=13694
15. 735.761 735.761 ↑ 1.0 1,394,039 1

Seq Scan on public.versions v_1 (cost=0.00..28,880.50 rows=1,394,040 width=18) (actual time=0.022..735.761 rows=1,394,039 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=11455
16. 257.554 570.624 ↓ 2.0 397,066 1

Hash (cost=5,587.16..5,587.16 rows=198,525 width=231) (actual time=570.624..570.624 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=2239
17. 132.967 313.070 ↓ 2.0 397,066 1

Nested Loop (cost=0.00..5,587.16 rows=198,525 width=231) (actual time=0.046..313.070 rows=397,066 loops=1)

  • Output: p.package, b.abi_tag
  • Buffers: shared hit=2239
18. 0.023 0.023 ↓ 2.0 2 1

Seq Scan on public.build_abis b (cost=0.00..1.02 rows=1 width=218) (actual time=0.019..0.023 rows=2 loops=1)

  • Output: b.abi_tag, b.skip
  • Filter: ((b.skip)::text = ''::text)
  • Buffers: shared hit=1
19. 180.080 180.080 ↓ 1.0 198,533 2

Seq Scan on public.packages p (cost=0.00..3,600.89 rows=198,525 width=13) (actual time=0.012..90.040 rows=198,533 loops=2)

  • Output: p.package, p.skip
  • Filter: ((p.skip)::text = ''::text)
  • Rows Removed by Filter: 18
  • Buffers: shared hit=2238
20. 1,101.165 11,476.893 ↓ 1.4 3,073,224 1

Result (cost=14.82..301,760.11 rows=2,181,015 width=220) (actual time=0.082..11,476.893 rows=3,073,224 loops=1)

  • Output: b_1.package, b_1.version, v_1_1.abi_tag, 1
  • Buffers: shared hit=1822178
21. 669.080 10,375.728 ↓ 1.4 3,073,224 1

Append (cost=14.82..301,760.11 rows=2,181,015 width=220) (actual time=0.080..10,375.728 rows=3,073,224 loops=1)

  • Buffers: shared hit=1822178
22. 1,494.250 5,495.409 ↓ 1.0 2,007,198 1

Nested Loop (cost=14.82..151,885.88 rows=2,002,568 width=237) (actual time=0.079..5,495.409 rows=2,007,198 loops=1)

  • Output: b_1.package, b_1.version, v_1_1.abi_tag
  • Buffers: shared hit=911086
23. 1,441.667 4,001.159 ↓ 1.0 1,003,599 1

Merge Join (cost=14.82..126,852.76 rows=1,001,284 width=19) (actual time=0.067..4,001.159 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=911085
24. 1,275.541 1,275.541 ↑ 1.0 2,022,391 1

Index Scan using builds_pk on public.builds b_1 (cost=0.43..60,934.12 rows=2,022,441 width=23) (actual time=0.026..1,275.541 rows=2,022,391 loops=1)

  • Output: b_1.build_id, b_1.package, b_1.version, b_1.built_by, b_1.built_at, b_1.duration, b_1.status, b_1.abi_tag
  • Buffers: shared hit=632498
25. 1,283.951 1,283.951 ↓ 1.0 1,003,599 1

Index Scan using files_builds on public.files f (cost=0.43..48,348.72 rows=1,001,284 width=4) (actual time=0.033..1,283.951 rows=1,003,599 loops=1)

  • Output: f.filename, f.build_id, f.filesize, f.filehash, f.package_version_tag, f.py_version_tag, f.abi_tag, f.platform_tag, f.package_tag
  • Filter: ((f.abi_tag)::text = 'none'::text)
  • Rows Removed by Filter: 94581
  • Buffers: shared hit=278587
26. 0.000 0.000 ↑ 1.0 2 1,003,599

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

  • Output: v_1_1.abi_tag
  • Buffers: shared hit=1
27. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on public.build_abis v_1_1 (cost=0.00..1.02 rows=2 width=218) (actual time=0.004..0.004 rows=2 loops=1)

  • Output: v_1_1.abi_tag
  • Buffers: shared hit=1
28. 2,178.361 4,211.239 ↓ 6.0 1,066,026 1

Merge Left Join (cost=14.15..128,064.08 rows=178,447 width=30) (actual time=0.705..4,211.239 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=911092
29. 1,336.230 1,336.230 ↑ 1.0 2,022,441 1

Index Scan using builds_pk on public.builds b_2 (cost=0.43..60,934.12 rows=2,022,441 width=29) (actual time=0.017..1,336.230 rows=2,022,441 loops=1)

  • Output: b_2.build_id, b_2.package, b_2.version, b_2.built_by, b_2.built_at, b_2.duration, b_2.status, b_2.abi_tag
  • Buffers: shared hit=632505
30. 696.648 696.648 ↑ 1.0 1,098,180 1

Index Scan using files_builds on public.files f_1 (cost=0.43..45,603.27 rows=1,098,180 width=9) (actual time=0.016..696.648 rows=1,098,180 loops=1)

  • Output: f_1.filename, f_1.build_id, f_1.filesize, f_1.filehash, f_1.package_version_tag, f_1.py_version_tag, f_1.abi_tag, f_1.platform_tag, f_1.package_tag
  • Buffers: shared hit=278587
Planning time : 5.053 ms
Execution time : 30,473.153 ms