explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LYc4

Settings
# exclusive inclusive rows x rows loops node
1. 135.172 10,346.489 ↓ 0.0 0 1

Insert on maintainer_repo_metapackages_events2 (cost=98,285.08..1,679,649.29 rows=14,956 width=58) (actual time=10,346.489..10,346.489 rows=0 loops=1)

2.          

CTE diff

3. 18.830 148.875 ↓ 2.1 8,083 1

Merge Full Join (cost=3,555.72..98,285.08 rows=3,760 width=237) (actual time=22.578..148.875 rows=8,083 loops=1)

  • Merge Cond: ((incoming_packages.effname = packages.effname) AND (incoming_packages.repo = packages.repo) AND ((unnest(incoming_packages.maintainers)) = (unnest(packages.maintainers))))
4. 41.364 65.352 ↓ 2.1 8,083 1

GroupAggregate (cost=3,487.11..4,596.31 rows=3,760 width=192) (actual time=22.501..65.352 rows=8,083 loops=1)

  • Group Key: incoming_packages.effname, incoming_packages.repo, (unnest(incoming_packages.maintainers))
5. 12.883 23.988 ↑ 4.2 8,982 1

Sort (cost=3,487.11..3,581.11 rows=37,600 width=130) (actual time=22.469..23.988 rows=8,982 loops=1)

  • Sort Key: incoming_packages.effname, incoming_packages.repo, (unnest(incoming_packages.maintainers))
  • Sort Method: quicksort Memory: 1428kB
6. 9.032 11.105 ↑ 4.2 8,982 1

ProjectSet (cost=0.00..629.80 rows=37,600 width=130) (actual time=0.017..11.105 rows=8,982 loops=1)

7. 2.073 2.073 ↓ 2.5 9,236 1

Seq Scan on incoming_packages (cost=0.00..413.60 rows=3,760 width=130) (actual time=0.007..2.073 rows=9,236 loops=1)

8. 0.006 0.029 ↓ 0.0 0 1

Materialize (cost=68.61..98.61 rows=500 width=192) (actual time=0.029..0.029 rows=0 loops=1)

9. 0.001 0.023 ↓ 0.0 0 1

GroupAggregate (cost=68.61..92.36 rows=500 width=192) (actual time=0.023..0.023 rows=0 loops=1)

  • Group Key: packages.effname, packages.repo, (unnest(packages.maintainers))
10. 0.002 0.022 ↓ 0.0 0 1

Sort (cost=68.61..69.86 rows=500 width=130) (actual time=0.022..0.022 rows=0 loops=1)

  • Sort Key: packages.effname, packages.repo, (unnest(packages.maintainers))
  • Sort Method: quicksort Memory: 25kB
11. 0.001 0.020 ↓ 0.0 0 1

ProjectSet (cost=31.50..46.19 rows=500 width=130) (actual time=0.020..0.020 rows=0 loops=1)

12. 0.001 0.019 ↓ 0.0 0 1

Hash Join (cost=31.50..43.32 rows=50 width=130) (actual time=0.019..0.019 rows=0 loops=1)

  • Hash Cond: (packages.effname = changed_projects.effname)
13. 0.018 0.018 ↓ 0.0 0 1

Seq Scan on packages (cost=0.00..11.00 rows=100 width=130) (actual time=0.018..0.018 rows=0 loops=1)

14. 0.000 0.000 ↓ 0.0 0

Hash (cost=29.00..29.00 rows=200 width=32) (never executed)

15. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=27.00..29.00 rows=200 width=32) (never executed)

  • Group Key: changed_projects.effname
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on changed_projects (cost=0.00..23.60 rows=1,360 width=32) (never executed)

17.          

SubPlan (for Merge Full Join)

18. 24.249 24.249 ↑ 1.0 1 8,083

Index Scan using maintainers_maintainer_idx on maintainers (cost=0.27..8.29 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8,083)

  • Index Cond: (maintainer = COALESCE((unnest(incoming_packages.maintainers)), (unnest(packages.maintainers))))
19. 24.249 24.249 ↑ 1.0 1 8,083

Index Scan using repositories_name_idx on repositories (cost=0.28..8.29 rows=1 width=2) (actual time=0.003..0.003 rows=1 loops=8,083)

  • Index Cond: (name = COALESCE(incoming_packages.repo, packages.repo))
20. 16.166 16.166 ↑ 1.0 1 8,083

Index Scan using metapackages_effname_idx on metapackages (cost=0.27..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8,083)

  • Index Cond: (effname = COALESCE(incoming_packages.effname, packages.effname))
21. 25.528 10,211.317 ↓ 1.1 16,051 1

Result (cost=0.00..1,581,364.21 rows=14,956 width=58) (actual time=157.443..10,211.317 rows=16,051 loops=1)

22. 3.352 10,185.789 ↓ 1.1 16,051 1

Append (cost=0.00..1,581,177.26 rows=14,956 width=54) (actual time=157.385..10,185.789 rows=16,051 loops=1)

23. 0.001 157.377 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.00..98.70 rows=1,880 width=54) (actual time=157.377..157.377 rows=0 loops=1)

24. 157.376 157.376 ↓ 0.0 0 1

CTE Scan on diff (cost=0.00..79.90 rows=1,880 width=54) (actual time=157.376..157.376 rows=0 loops=1)

  • Filter: is_removed
  • Rows Removed by Filter: 8083
25. 1.530 4.639 ↓ 4.3 8,083 1

Subquery Scan on *SELECT* 2 (cost=0.00..98.70 rows=1,880 width=54) (actual time=0.006..4.639 rows=8,083 loops=1)

26. 3.109 3.109 ↓ 4.3 8,083 1

CTE Scan on diff diff_1 (cost=0.00..79.90 rows=1,880 width=54) (actual time=0.004..3.109 rows=8,083 loops=1)

  • Filter: is_added
27. 0.868 8.779 ↓ 1.3 4,814 1

Subquery Scan on *SELECT* 3 (cost=0.00..140.58 rows=3,732 width=54) (actual time=0.013..8.779 rows=4,814 loops=1)

28. 7.911 7.911 ↓ 1.3 4,814 1

CTE Scan on diff diff_2 (cost=0.00..103.26 rows=3,732 width=54) (actual time=0.012..7.911 rows=4,814 loops=1)

  • Filter: ((new_versions_uptodate IS NOT NULL) AND (is_added OR (old_versions_uptodate[1] IS DISTINCT FROM new_versions_uptodate[1])))
  • Rows Removed by Filter: 3269
29. 2.429 10,010.204 ↑ 1.2 3,019 1

Subquery Scan on *SELECT* 4 (cost=0.00..1,580,623.92 rows=3,732 width=54) (actual time=3.277..10,010.204 rows=3,019 loops=1)

30. 45.075 10,007.775 ↑ 1.2 3,019 1

CTE Scan on diff diff_3 (cost=0.00..1,580,586.60 rows=3,732 width=54) (actual time=3.276..10,007.775 rows=3,019 loops=1)

  • Filter: ((new_versions_outdated IS NOT NULL) AND (is_added OR (old_versions_outdated[1] IS DISTINCT FROM new_versions_outdated[1])))
  • Rows Removed by Filter: 5064
31.          

SubPlan (for CTE Scan)

32. 120.760 9,962.700 ↑ 1.0 1 3,019

Aggregate (cost=423.48..423.49 rows=1 width=32) (actual time=3.300..3.300 rows=1 loops=3,019)

33. 9,841.940 9,841.940 ↓ 3.5 66 3,019

Seq Scan on incoming_packages incoming_packages_1 (cost=0.00..423.00 rows=19 width=38) (actual time=1.681..3.260 rows=66 loops=3,019)

  • Filter: (effname = diff_3.effname)
  • Rows Removed by Filter: 9170
34. 0.026 1.438 ↑ 27.6 135 1

Subquery Scan on *SELECT* 5 (cost=0.00..140.58 rows=3,732 width=54) (actual time=0.010..1.438 rows=135 loops=1)

35. 1.412 1.412 ↑ 27.6 135 1

CTE Scan on diff diff_4 (cost=0.00..103.26 rows=3,732 width=54) (actual time=0.009..1.412 rows=135 loops=1)

  • Filter: ((new_versions_ignored IS NOT NULL) AND (is_added OR (old_versions_ignored[1] IS DISTINCT FROM new_versions_ignored[1])))
  • Rows Removed by Filter: 7948
Planning time : 1.105 ms
Execution time : 10,346.894 ms