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)
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 rcePackageCache .id) lease ild.id = BinaryPackageRe lease.build ild.distro_ arch_series s.distroseries lease lease.id = BinaryPackageBu ild.source_ package_ release rcePackageCache rcePackageCache .sourcepackagen ame lease.sourcepac kagename distribution = 1 releasestatus != 6 rcePackageCache .archive IN (1, 534) lease.binarypac kagename = (
QUERY PLAN ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -- 84..14663. 85 rows=1 width=4) (actual time=628. 353..628. 354 rows=1 loops=1) me_name_ key on binarypackagename (cost=0.00..3.89 rows=1 width=4) (actual time=0.082..0.084 rows=1 loops=1) 85..14659. 79 rows=65 width=4) (actual time=412. 262..627. 161 rows=1388 loops=1) urcepackagecach e.sourcepackage name = sourcepackagere lease.sourcepac kagename) rcepackagecache (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[ ])) rcepackagecache __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[ ])) 45..897. 45 rows=46 width=4) (actual time=242. 941..242. 941 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: (binarypackageb uild.distro_ arch_series = distroarchserie s.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 binarypackagere lease_binarypac kagename_ key on binarypackagere lease (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 binarypackagebu ild_pkey on binarypackagebuild (cost=0.00..3.98 rows=1 width=12) (actual time=0.026..0.027 rows=1 loops=2346)
Index Cond: (binarypackageb uild.id = binarypackagere lease.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: (distroarchseri es.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 sourcepackagere lease_pkey on sourcepackagere lease (cost=0.00..0.43 rows=1 width=8) (actual time=0.034..0.035 rows=1 loops=1936)
Index Cond: (sourcepackager elease. id = binarypackagebu ild.source_ package_ release)
launchpad_prod_3-# SELECT COUNT(DISTINCT DistributionSou
launchpad_prod_3-# FROM
launchpad_prod_3-# BinaryPackageRe
launchpad_prod_3-# JOIN BinaryPackageBuild
launchpad_prod_3-# ON BinaryPackageBu
launchpad_prod_3-# JOIN DistroArchSeries
launchpad_prod_3-# ON DistroArchSeries.id = BinaryPackageBu
launchpad_prod_3-# JOIN DistroSeries ON DistroSeries.id = DistroArchSerie
launchpad_prod_3-# JOIN SourcePackageRe
launchpad_prod_3-# ON SourcePackageRe
launchpad_prod_3-# JOIN DistributionSou
launchpad_prod_3-# ON DistributionSou
launchpad_prod_3-# = SourcePackageRe
launchpad_prod_3-# WHERE
launchpad_prod_3-# DistroSeries.
launchpad_prod_3-# AND DistroSeries.
launchpad_prod_3-# AND DistributionSou
launchpad_prod_3-# AND BinaryPackageRe
launchpad_prod_3(# SELECT id FROM BinaryPackageName WHERE name='mplayer');
-------
Aggregate (cost=14663.
InitPlan
-> Index Scan using binarypackagena
Index Cond: (name = 'mplayer'::text)
-> Hash Join (cost=1437.
Hash Cond: (distributionso
-> Bitmap Heap Scan on distributionsou
-> Bitmap Index Scan on distributionsou
-> Hash (cost=897.
-> Nested Loop (cost=7.20..897.45 rows=46 width=4) (actual time=0.997..239.023 rows=1936 loops=1)
Total runtime: 628.683 ms
(29 rows)