OOPS-a750a111ef33fe426965ed063503aa8e is a qastaging OOPS, so a fair bit of this is just because qastaging is slow, and the query is much better once the relevant database rows warm up. However, I noticed that the plan isn't great (look at the sequential scans there):
launchpad_qastaging=> EXPLAIN (ANALYZE, BUFFERS) SELECT SourcePackageRecipe.* FROM SourcePackageRecipe, SourcePackageRecipeData LEFT JOIN Branch ON SourcePackageRecipeData.base_branch = Branch.id LEFT JOIN GitRepository ON SourcePackageRecipeData.base_git_repository = GitRepository.id WHERE SourcePackageRecipe.id = SourcePackageRecipeData.sourcepackage_recipe AND (Branch.product = 57971 OR GitRepository.project = 57971);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=6547.86..36658.69 rows=1 width=129) (actual time=57.438..57.438 rows=0 loops=1)
Buffers: shared hit=49779
-> Hash Left Join (cost=6547.58..36658.36 rows=1 width=4) (actual time=57.437..57.437 rows=0 loops=1)
Hash Cond: (sourcepackagerecipedata.base_git_repository = gitrepository.id)
Filter: ((branch.product = 57971) OR (gitrepository.project = 57971))
Rows Removed by Filter: 11990
Buffers: shared hit=49779
-> Nested Loop Left Join (cost=0.43..30079.74 rows=11988 width=12) (actual time=0.024..44.559 rows=11990 loops=1)
Buffers: shared hit=43505
-> Seq Scan on sourcepackagerecipedata (cost=0.00..230.88 rows=11988 width=12) (actual time=0.011..1.893 rows=11990 loops=1)
Buffers: shared hit=111
-> Index Scan using branch_pkey on branch (cost=0.43..2.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=11990)
Index Cond: (sourcepackagerecipedata.base_branch = id)
Buffers: shared hit=43394
-> Hash (cost=6395.40..6395.40 rows=12140 width=8) (actual time=10.125..10.126 rows=12141 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 588kB
Buffers: shared hit=6274
-> Seq Scan on gitrepository (cost=0.00..6395.40 rows=12140 width=8) (actual time=0.008..7.626 rows=12141 loops=1)
Buffers: shared hit=6274
-> Index Scan using sourcepackagerecipe_pkey on sourcepackagerecipe (cost=0.29..0.33 rows=1 width=129) (never executed)
Index Cond: (id = sourcepackagerecipedata.sourcepackage_recipe)
Planning time: 0.945 ms
Execution time: 57.498 ms
(23 rows)
If I turn this into a UNION, then I get a much more sensible plan that uses the correct indexes:
launchpad_qastaging=> EXPLAIN (ANALYZE, BUFFERS) SELECT SourcePackageRecipe.* FROM SourcePackageRecipe, SourcePackageRecipeData LEFT JOIN Branch ON SourcePackageRecipeData.base_branch = Branch.id WHERE SourcePackageRecipe.id = SourcePackageRecipeData.sourcepackage_recipe AND Branch.product = 57971 UNION SELECT SourcePackageRecipe.* FROM SourcePackageRecipe, SourcePackageRecipeData LEFT JOIN GitRepository ON SourcePackageRecipeData.base_git_repository = GitRepository.id WHERE SourcePackageRecipe.id = SourcePackageRecipeData.sourcepackage_recipe AND GitRepository.project = 57971;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=80.32..80.37 rows=2 width=98) (actual time=0.045..0.045 rows=0 loops=1)
Buffers: shared hit=6
-> Sort (cost=80.32..80.32 rows=2 width=98) (actual time=0.045..0.045 rows=0 loops=1)
Sort Key: sourcepackagerecipe.id, sourcepackagerecipe.date_created, sourcepackagerecipe.date_last_modified, sourcepackagerecipe.registrant, sourcepackagerecipe.owner, sourcepackagerecipe.name, sourcepackagerecipe.description, sourcepackagerecipe.build_daily, sourcepackagerecipe.daily_build_archive, sourcepackagerecipe.is_stale
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=6
-> Append (cost=0.99..80.31 rows=2 width=98) (actual time=0.027..0.027 rows=0 loops=1)
Buffers: shared hit=6
-> Nested Loop (cost=0.99..72.02 rows=1 width=129) (actual time=0.013..0.013 rows=0 loops=1)
Buffers: shared hit=3
-> Nested Loop (cost=0.71..71.69 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Buffers: shared hit=3
-> Index Scan using branch__product__owner__name__key on branch (cost=0.42..24.43 rows=13 width=4) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (product = 57971)
Buffers: shared hit=3
-> Index Scan using sourcepackagerecipedata__base_branch__idx on sourcepackagerecipedata (cost=0.29..3.63 rows=1 width=8) (never executed)
Index Cond: (base_branch = branch.id)
-> Index Scan using sourcepackagerecipe_pkey on sourcepackagerecipe (cost=0.29..0.33 rows=1 width=129) (never executed)
Index Cond: (id = sourcepackagerecipedata.sourcepackage_recipe)
-> Nested Loop (cost=0.97..8.27 rows=1 width=129) (actual time=0.014..0.014 rows=0 loops=1)
Buffers: shared hit=3
-> Nested Loop (cost=0.69..7.93 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Buffers: shared hit=3
-> Index Only Scan using gitrepository__project__id__idx on gitrepository (cost=0.41..4.03 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (project = 57971)
Heap Fetches: 0
Buffers: shared hit=3
-> Index Scan using sourcepackagerecipedata__base_git_repository__idx on sourcepackagerecipedata sourcepackagerecipedata_1 (cost=0.28..3.89 rows=1 width=8) (never executed)
Index Cond: (base_git_repository = gitrepository.id)
-> Index Scan using sourcepackagerecipe_pkey on sourcepackagerecipe sourcepackagerecipe_1 (cost=0.29..0.33 rows=1 width=129) (never executed)
Index Cond: (id = sourcepackagerecipedata_1.sourcepackage_recipe)
Planning time: 1.407 ms
Execution time: 0.149 ms
(33 rows)
