Comment 5 for bug 618372

There is an unnecessary join with SourcePackageName. However the big win is surprising - if I move the BinaryPackageName check to a subquery, things go much, much faster. PG 8.3 doesn't seem smart enough to know the BinaryPackageName check can only return a single row - maybe PG 8.4 will.

launchpad_prod_3=# explain analyze
launchpad_prod_3-# SELECT COUNT(DISTINCT DistributionSourcePackageCache.id)
launchpad_prod_3-# FROM
launchpad_prod_3-# BinaryPackageRelease
launchpad_prod_3-# JOIN BinaryPackageBuild
launchpad_prod_3-# ON BinaryPackageBuild.id = BinaryPackageRelease.build
launchpad_prod_3-# JOIN DistroArchSeries
launchpad_prod_3-# ON DistroArchSeries.id = BinaryPackageBuild.distro_arch_series
launchpad_prod_3-# JOIN DistroSeries ON DistroSeries.id = DistroArchSeries.distroseries
launchpad_prod_3-# JOIN SourcePackageRelease
launchpad_prod_3-# ON SourcePackageRelease.id = BinaryPackageBuild.source_package_release
launchpad_prod_3-# JOIN DistributionSourcePackageCache
launchpad_prod_3-# ON DistributionSourcePackageCache.sourcepackagename
launchpad_prod_3-# = SourcePackageRelease.sourcepackagename
launchpad_prod_3-# WHERE
launchpad_prod_3-# DistroSeries.distribution = 1
launchpad_prod_3-# AND DistroSeries.releasestatus != 6
launchpad_prod_3-# AND DistributionSourcePackageCache.archive IN (1, 534)
launchpad_prod_3-# AND BinaryPackageRelease.binarypackagename = (
launchpad_prod_3(# SELECT id FROM BinaryPackageName WHERE name='mplayer');
                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=14663.84..14663.85 rows=1 width=4) (actual time=628.353..628.354 rows=1 loops=1)
   InitPlan
     -> Index Scan using binarypackagename_name_key on binarypackagename (cost=0.00..3.89 rows=1 width=4) (actual time=0.082..0.084 rows=1 loops=1)
           Index Cond: (name = 'mplayer'::text)
   -> Hash Join (cost=1437.85..14659.79 rows=65 width=4) (actual time=412.262..627.161 rows=1388 loops=1)
         Hash Cond: (distributionsourcepackagecache.sourcepackagename = sourcepackagerelease.sourcepackagename)
         -> Bitmap Heap Scan on distributionsourcepackagecache (cost=539.83..13678.72 rows=21977 width=8) (actual time=56.406..361.512 rows=21978 loops=1)
               Recheck Cond: (archive = ANY ('{1,534}'::integer[]))
               -> Bitmap Index Scan on distributionsourcepackagecache__archive__idx (cost=0.00..534.34 rows=21977 width=0) (actual time=50.053..50.053 rows=22577 loops=1)
                     Index Cond: (archive = ANY ('{1,534}'::integer[]))
         -> Hash (cost=897.45..897.45 rows=46 width=4) (actual time=242.941..242.941 rows=1936 loops=1)
               -> Nested Loop (cost=7.20..897.45 rows=46 width=4) (actual time=0.997..239.023 rows=1936 loops=1)
                     -> Hash Join (cost=7.20..876.95 rows=46 width=4) (actual time=0.921..161.208 rows=1936 loops=1)
                           Hash Cond: (binarypackagebuild.distro_arch_series = distroarchseries.id)
                           -> Nested Loop (cost=0.00..868.70 rows=155 width=8) (actual time=0.375..155.262 rows=2346 loops=1)
                                 -> Index Scan using binarypackagerelease_binarypackagename_key on binarypackagerelease (cost=0.00..249.60 rows=155 width=4) (actual time=0.252..79.585 rows=2346 loops=1)
                                       Index Cond: (binarypackagename = $0)
                                 -> Index Scan using binarypackagebuild_pkey on binarypackagebuild (cost=0.00..3.98 rows=1 width=12) (actual time=0.026..0.027 rows=1 loops=2346)
                                       Index Cond: (binarypackagebuild.id = binarypackagerelease.build)
                           -> Hash (cost=6.90..6.90 rows=24 width=4) (actual time=0.358..0.358 rows=40 loops=1)
                                 -> Hash Join (cost=3.56..6.90 rows=24 width=4) (actual time=0.129..0.307 rows=40 loops=1)
                                       Hash Cond: (distroarchseries.distroseries = distroseries.id)
                                       -> Seq Scan on distroarchseries (cost=0.00..2.80 rows=80 width=8) (actual time=0.019..0.082 rows=80 loops=1)
                                       -> Hash (cost=3.45..3.45 rows=9 width=4) (actual time=0.080..0.080 rows=6 loops=1)
                                             -> Seq Scan on distroseries (cost=0.00..3.45 rows=9 width=4) (actual time=0.035..0.068 rows=6 loops=1)
                                                   Filter: ((releasestatus <> 6) AND (distribution = 1))
                     -> Index Scan using sourcepackagerelease_pkey on sourcepackagerelease (cost=0.00..0.43 rows=1 width=8) (actual time=0.034..0.035 rows=1 loops=1936)
                           Index Cond: (sourcepackagerelease.id = binarypackagebuild.source_package_release)
 Total runtime: 628.683 ms
(29 rows)