explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M5fX : Original statistics view

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 20,325.832 ↑ 1.0 1 1

Nested Loop (cost=322,156.29..322,156.55 rows=1 width=96) (actual time=20,325.793..20,325.832 rows=1 loops=1)

  • Output: p.packages_count, bp.packages_built, v.versions_count, bv.versions_tried, bs.builds_count, bs.builds_count_success, bl.builds_count_last_hour, bs.builds_time, fc.files_count, fs.builds_size, dl.downloads_last_month
  • Buffers: shared hit=1470171
2.          

CTE package_stats

3. 93.640 196.180 ↑ 1.0 1 1

Aggregate (cost=3,422.96..3,422.97 rows=1 width=8) (actual time=196.178..196.180 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=1063
4. 102.540 102.540 ↑ 1.0 187,402 1

Seq Scan on public.packages (cost=0.00..2,950.98 rows=188,792 width=0) (actual time=0.012..102.540 rows=187,402 loops=1)

  • Output: packages.package, packages.skip
  • Filter: (NOT packages.skip)
  • Rows Removed by Filter: 14
  • Buffers: shared hit=1063
5.          

CTE version_stats

6. 670.385 3,489.069 ↑ 1.0 1 1

Aggregate (cost=48,070.71..48,070.72 rows=1 width=8) (actual time=3,489.068..3,489.069 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=9439
7. 1,752.470 2,818.684 ↑ 1.0 1,309,617 1

Hash Join (cost=5,310.88..44,796.11 rows=1,309,838 width=0) (actual time=266.630..2,818.684 rows=1,309,617 loops=1)

  • Hash Cond: ((v_1.package)::text = (p_1.package)::text)
  • Buffers: shared hit=9439
8. 799.931 799.931 ↓ 1.0 1,309,968 1

Seq Scan on public.versions v_1 (cost=0.00..21,474.80 rows=1,309,880 width=12) (actual time=0.011..799.931 rows=1,309,968 loops=1)

  • Output: v_1.package, v_1.version, v_1.skip
  • Filter: (NOT v_1.skip)
  • Rows Removed by Filter: 36
  • Buffers: shared hit=8376
9. 142.898 266.283 ↑ 1.0 187,402 1

Hash (cost=2,950.98..2,950.98 rows=188,792 width=13) (actual time=266.282..266.283 rows=187,402 loops=1)

  • Output: p_1.package
  • Buckets: 262144 Batches: 1 Memory Usage: 10355kB
  • Buffers: shared hit=1063
10. 123.385 123.385 ↑ 1.0 187,402 1

Seq Scan on public.packages p_1 (cost=0.00..2,950.98 rows=188,792 width=13) (actual time=0.010..123.385 rows=187,402 loops=1)

  • Output: p_1.package
  • Filter: (NOT p_1.skip)
  • Rows Removed by Filter: 14
  • Buffers: shared hit=1063
11.          

CTE build_vers

12. 671.359 5,614.441 ↑ 1.0 1 1

Aggregate (cost=56,263.35..56,263.36 rows=1 width=8) (actual time=5,614.439..5,614.441 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=24340
13. 3,942.172 4,943.082 ↓ 7.1 1,307,140 1

HashAggregate (cost=52,099.43..53,950.06 rows=185,063 width=19) (actual time=3,599.031..4,943.082 rows=1,307,140 loops=1)

  • Output: builds.package, builds.version
  • Group Key: builds.package, builds.version
  • Buffers: shared hit=24340
14. 1,000.910 1,000.910 ↓ 1.0 1,859,054 1

Seq Scan on public.builds (cost=0.00..42,846.29 rows=1,850,629 width=19) (actual time=0.016..1,000.910 rows=1,859,054 loops=1)

  • Output: builds.build_id, builds.package, builds.version, builds.built_by, builds.built_at, builds.duration, builds.status, builds.abi_tag
  • Buffers: shared hit=24340
15.          

CTE build_stats

16. 1,398.419 2,374.614 ↑ 1.0 1 1

Aggregate (cost=56,726.01..56,726.02 rows=1 width=32) (actual time=2,374.612..2,374.614 rows=1 loops=1)

  • Output: count(*), count(*) FILTER (WHERE builds_1.status), COALESCE(sum(builds_1.duration), '00:00:00'::interval)
  • Buffers: shared hit=24340
17. 976.195 976.195 ↓ 1.0 1,859,054 1

Seq Scan on public.builds builds_1 (cost=0.00..42,846.29 rows=1,850,629 width=17) (actual time=0.023..976.195 rows=1,859,054 loops=1)

  • Output: builds_1.build_id, builds_1.package, builds_1.version, builds_1.built_by, builds_1.built_at, builds_1.duration, builds_1.status, builds_1.abi_tag
  • Buffers: shared hit=24340
18.          

CTE build_latest

19. 0.087 0.281 ↑ 1.0 1 1

Aggregate (cost=10.35..10.36 rows=1 width=8) (actual time=0.280..0.281 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=101
20. 0.194 0.194 ↓ 6.1 153 1

Index Only Scan using builds_timestamp on public.builds builds_2 (cost=0.43..10.29 rows=25 width=0) (actual time=0.079..0.194 rows=153 loops=1)

  • Output: builds_2.built_at
  • Index Cond: (builds_2.built_at > (now() - '01:00:00'::interval))
  • Heap Fetches: 153
  • Buffers: shared hit=101
21.          

CTE build_pkgs

22. 70.482 5,290.219 ↑ 1.0 1 1

Aggregate (cost=94,641.07..94,641.08 rows=1 width=8) (actual time=5,290.218..5,290.219 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=665731
23. 1,132.018 5,219.737 ↓ 2.7 139,268 1

HashAggregate (cost=93,495.12..94,004.43 rows=50,931 width=12) (actual time=5,092.025..5,219.737 rows=139,268 loops=1)

  • Output: b.package
  • Group Key: b.package
  • Buffers: shared hit=665731
24. 1,941.176 4,087.719 ↓ 1.7 1,036,888 1

Merge Join (cost=2.48..91,993.39 rows=600,694 width=12) (actual time=0.051..4,087.719 rows=1,036,888 loops=1)

  • Output: b.package
  • Merge Cond: (b.build_id = f.build_id)
  • Buffers: shared hit=665731
25. 1,396.409 1,396.409 ↑ 1.0 1,054,805 1

Index Scan using builds_pk on public.builds b (cost=0.43..60,895.70 rows=1,065,777 width=16) (actual time=0.021..1,396.409 rows=1,054,805 loops=1)

  • Output: b.build_id, b.package, b.version, b.built_by, b.built_at, b.duration, b.status, b.abi_tag
  • Filter: b.status
  • Rows Removed by Filter: 804249
  • Buffers: shared hit=574884
26. 750.134 750.134 ↑ 1.0 1,036,888 1

Index Only Scan using files_builds on public.files f (cost=0.42..22,324.86 rows=1,043,053 width=4) (actual time=0.022..750.134 rows=1,036,888 loops=1)

  • Output: f.build_id
  • Heap Fetches: 150215
  • Buffers: shared hit=90847
27.          

CTE file_count

28. 524.565 1,139.490 ↑ 1.0 1 1

Aggregate (cost=24,932.49..24,932.50 rows=1 width=8) (actual time=1,139.490..1,139.490 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=90847
29. 614.925 614.925 ↑ 1.0 1,036,888 1

Index Only Scan using files_builds on public.files (cost=0.42..22,324.86 rows=1,043,053 width=0) (actual time=0.022..614.925 rows=1,036,888 loops=1)

  • Output: files.build_id
  • Heap Fetches: 150215
  • Buffers: shared hit=90847
30.          

CTE file_stats

31. 517.631 1,206.828 ↑ 1.0 1 1

Aggregate (cost=25,513.95..25,513.96 rows=1 width=8) (actual time=1,206.827..1,206.828 rows=1 loops=1)

  • Output: COALESCE(sum(files_1.filesize), '0'::bigint)
  • Buffers: shared hit=144156
32. 689.197 689.197 ↑ 1.0 993,551 1

Index Only Scan using files_size on public.files files_1 (cost=0.42..23,014.97 rows=999,592 width=4) (actual time=0.026..689.197 rows=993,551 loops=1)

  • Output: files_1.platform_tag, files_1.filesize
  • Heap Fetches: 142040
  • Buffers: shared hit=144156
33.          

CTE download_stats

34. 337.076 1,014.580 ↑ 1.0 1 1

Aggregate (cost=12,575.32..12,575.33 rows=1 width=8) (actual time=1,014.578..1,014.580 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=510154
35. 677.504 677.504 ↓ 1.8 645,665 1

Index Only Scan using downloads_accessed_at on public.downloads (cost=0.44..11,674.35 rows=360,387 width=0) (actual time=0.137..677.504 rows=645,665 loops=1)

  • Output: downloads.accessed_at
  • Index Cond: (downloads.accessed_at > (now() - '1 mon'::interval))
  • Heap Fetches: 645670
  • Buffers: shared hit=510154
36. 0.009 19,311.235 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.23 rows=1 width=88) (actual time=19,311.201..19,311.235 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count, bp.packages_built, bv.versions_tried, bs.builds_count, bs.builds_count_success, bs.builds_time, bl.builds_count_last_hour, fc.files_count, fs.builds_size
  • Buffers: shared hit=960017
37. 0.007 18,104.390 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.20 rows=1 width=80) (actual time=18,104.360..18,104.390 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count, bp.packages_built, bv.versions_tried, bs.builds_count, bs.builds_count_success, bs.builds_time, bl.builds_count_last_hour, fc.files_count
  • Buffers: shared hit=815861
38. 0.007 16,964.885 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.17 rows=1 width=72) (actual time=16,964.860..16,964.885 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count, bp.packages_built, bv.versions_tried, bs.builds_count, bs.builds_count_success, bs.builds_time, bl.builds_count_last_hour
  • Buffers: shared hit=725014
39. 0.009 16,964.593 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.14 rows=1 width=64) (actual time=16,964.572..16,964.593 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count, bp.packages_built, bv.versions_tried, bs.builds_count, bs.builds_count_success, bs.builds_time
  • Buffers: shared hit=724913
40. 0.008 14,589.964 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.11 rows=1 width=32) (actual time=14,589.948..14,589.964 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count, bp.packages_built, bv.versions_tried
  • Buffers: shared hit=700573
41. 0.007 8,975.507 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.08 rows=1 width=24) (actual time=8,975.495..8,975.507 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count, bp.packages_built
  • Buffers: shared hit=676233
42. 0.009 3,685.273 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=16) (actual time=3,685.265..3,685.273 rows=1 loops=1)

  • Output: p.packages_count, v.versions_count
  • Buffers: shared hit=10502
43. 196.187 196.187 ↑ 1.0 1 1

CTE Scan on package_stats p (cost=0.00..0.02 rows=1 width=8) (actual time=196.184..196.187 rows=1 loops=1)

  • Output: p.packages_count
  • Buffers: shared hit=1063
44. 3,489.077 3,489.077 ↑ 1.0 1 1

CTE Scan on version_stats v (cost=0.00..0.02 rows=1 width=8) (actual time=3,489.074..3,489.077 rows=1 loops=1)

  • Output: v.versions_count
  • Buffers: shared hit=9439
45. 5,290.227 5,290.227 ↑ 1.0 1 1

CTE Scan on build_pkgs bp (cost=0.00..0.02 rows=1 width=8) (actual time=5,290.224..5,290.227 rows=1 loops=1)

  • Output: bp.packages_built
  • Buffers: shared hit=665731
46. 5,614.449 5,614.449 ↑ 1.0 1 1

CTE Scan on build_vers bv (cost=0.00..0.02 rows=1 width=8) (actual time=5,614.446..5,614.449 rows=1 loops=1)

  • Output: bv.versions_tried
  • Buffers: shared hit=24340
47. 2,374.620 2,374.620 ↑ 1.0 1 1

CTE Scan on build_stats bs (cost=0.00..0.02 rows=1 width=32) (actual time=2,374.618..2,374.620 rows=1 loops=1)

  • Output: bs.builds_count, bs.builds_count_success, bs.builds_time
  • Buffers: shared hit=24340
48. 0.285 0.285 ↑ 1.0 1 1

CTE Scan on build_latest bl (cost=0.00..0.02 rows=1 width=8) (actual time=0.283..0.285 rows=1 loops=1)

  • Output: bl.builds_count_last_hour
  • Buffers: shared hit=101
49. 1,139.498 1,139.498 ↑ 1.0 1 1

CTE Scan on file_count fc (cost=0.00..0.02 rows=1 width=8) (actual time=1,139.496..1,139.498 rows=1 loops=1)

  • Output: fc.files_count
  • Buffers: shared hit=90847
50. 1,206.836 1,206.836 ↑ 1.0 1 1

CTE Scan on file_stats fs (cost=0.00..0.02 rows=1 width=8) (actual time=1,206.834..1,206.836 rows=1 loops=1)

  • Output: fs.builds_size
  • Buffers: shared hit=144156
51. 1,014.587 1,014.587 ↑ 1.0 1 1

CTE Scan on download_stats dl (cost=0.00..0.02 rows=1 width=8) (actual time=1,014.585..1,014.587 rows=1 loops=1)

  • Output: dl.downloads_last_month
  • Buffers: shared hit=510154