Bug #33700 “could queue filters match source as well as binaries...” : Bugs : Launchpad itself

Revision history for this message

Celso Providelo (cprov) wrote on 2006-04-10:

#1

Ok, will add support for matching also binarypackagenames on binary queue entries

Changed in launchpad-upload-and-queue:
assignee:	nobody → cprov
status:	Unconfirmed → Confirmed

Revision history for this message

Colin Watson (cjwatson) wrote on 2006-04-10:

#2

Binary package names are already matched; it's source package names that I would like to be matched in addition.

Curtis Hovey (sinzui) on 2010-06-01

Changed in soyuz:
assignee:	Celso Providelo (cprov) → nobody

Revision history for this message

Colin Watson (cjwatson) wrote on 2012-06-06:

#3

This has managed to survive through the refactoring of getQueueItems to getPackageUploads. These days I think it needs to be fixed in PackageUploadSet.getAll, presumably by joining the DB equivalent of PackageUploadBuild.source_package_release.

Colin Watson (cjwatson) on 2012-08-11

Changed in launchpad:
assignee:	nobody → Colin Watson (cjwatson)
status:	Triaged → In Progress

Revision history for this message

Colin Watson (cjwatson) wrote on 2012-08-13:

#4

Download full text (28.6 KiB)

On the merge proposal, Robert asked for a performance analysis. It seems more sensible to record that here rather than on the MP.

When operating on the large queues (e.g. DONE) even the old query is a total disaster on dogfood (note that this is a query from the web UI which doesn't allow restricting by pocket):

EXPLAIN ANALYZE SELECT DISTINCT PackageUpload.archive, PackageUpload.changesfile, PackageUpload.date_created, PackageUpload.distroseries, PackageUpload.id, PackageUpload.package_copy_job, PackageUpload.pocket, PackageUpload.signing_key, PackageUpload.status FROM PackageUpload LEFT JOIN PackageCopyJob ON PackageCopyJob.id = PackageUpload.package_copy_job LEFT JOIN PackageUploadSource ON PackageUploadSource.packageupload = PackageUpload.id LEFT JOIN SourcePackageRelease ON SourcePackageRelease.id = PackageUploadSource.sourcepackagerelease LEFT JOIN SourcePackageName ON SourcePackageName.id = SourcePackageRelease.sourcepackagename LEFT JOIN PackageUploadBuild ON PackageUploadBuild.packageupload = PackageUpload.id LEFT JOIN BinaryPackageRelease ON BinaryPackageRelease.build = PackageUploadBuild.build LEFT JOIN BinaryPackageName ON BinaryPackageName.id = BinaryPackageRelease.binarypackagename LEFT JOIN PackageUploadCustom ON PackageUploadCustom.packageupload = PackageUpload.id LEFT JOIN LibraryFileAlias ON LibraryFileAlias.id = PackageUploadCustom.libraryfilealias WHERE PackageUpload.distroseries = 107 AND PackageUpload.status IN (3) AND PackageUpload.archive IN (1, 534) AND (PackageCopyJob.package_name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR SourcePackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR BinaryPackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR LibraryFileAlias.filename LIKE '%fglrx-installer-updates%' ESCAPE '!') ORDER BY PackageUpload.id DESC LIMIT 31 OFFSET 0;
Limit (cost=4300017.37..4300018.15 rows=31 width=40) (actual time=1616568.351..1616568.404 rows=11 loops=1)
   -> Unique (cost=4300017.37..4300021.47 rows=164 width=40) (actual time=1616568.347..1616568.383 rows=11 loops=1)
         -> Sort (cost=4300017.37..4300017.78 rows=164 width=40) (actual time=1616568.342..1616568.349 rows=11 loops=1)
               Sort Key: packageupload.id, packageupload.archive, packageupload.changesfile, packageupload.date_created, packageupload.package_copy_job, packageupload.pocket, packageupload.signing_key
               Sort Method: quicksort Memory: 17kB
               -> Hash Right Join (cost=3978824.68..4300011.34 rows=164 width=40) (actual time=1614733.292..1616568.144 rows=11 loops=1)
                     Hash Cond: (packageuploadcustom.packageupload = packageupload.id)
                     Filter: ((packagecopyjob.package_name ~~ '%fglrx-installer-updates%'::text) OR (sourcepackagename.name ~~ '%fglrx-installer-updates%'::text) OR (binarypackagename.name ~~ '%fglrx-installer-updates%'::text) OR (libraryfilealias.filename ~~ '%fglrx-installer-updates%'::text))
                     -> Hash Left Join (cost=1475901.71..1779659.34 rows=407243 width=47) (actual time=1003103.339..1145793.114 rows=407113 loops=1)
                           Hash Cond: (packageuploadcustom.libraryfilealias = lib...

On the merge proposal, Robert asked for a performance analysis.  It seems more sensible to record that here rather than on the MP.

When operating on the large queues (e.g. DONE) even the old query is a total disaster on dogfood (note that this is a query from the web UI which doesn't allow restricting by pocket):

EXPLAIN ANALYZE SELECT DISTINCT PackageUpload.archive, PackageUpload.changesfile, PackageUpload.date_created, PackageUpload.distroseries, PackageUpload.id, PackageUpload.package_copy_job, PackageUpload.pocket, PackageUpload.signing_key, PackageUpload.status FROM PackageUpload LEFT JOIN PackageCopyJob ON PackageCopyJob.id = PackageUpload.package_copy_job LEFT JOIN PackageUploadSource ON PackageUploadSource.packageupload = PackageUpload.id LEFT JOIN SourcePackageRelease ON SourcePackageRelease.id = PackageUploadSource.sourcepackagerelease LEFT JOIN SourcePackageName ON SourcePackageName.id = SourcePackageRelease.sourcepackagename LEFT JOIN PackageUploadBuild ON PackageUploadBuild.packageupload = PackageUpload.id LEFT JOIN BinaryPackageRelease ON BinaryPackageRelease.build = PackageUploadBuild.build LEFT JOIN BinaryPackageName ON BinaryPackageName.id = BinaryPackageRelease.binarypackagename LEFT JOIN PackageUploadCustom ON PackageUploadCustom.packageupload = PackageUpload.id LEFT JOIN LibraryFileAlias ON LibraryFileAlias.id = PackageUploadCustom.libraryfilealias WHERE PackageUpload.distroseries = 107 AND PackageUpload.status IN (3) AND PackageUpload.archive IN (1, 534) AND (PackageCopyJob.package_name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR SourcePackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR BinaryPackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR LibraryFileAlias.filename LIKE '%fglrx-installer-updates%' ESCAPE '!') ORDER BY PackageUpload.id DESC LIMIT 31 OFFSET 0;
 Limit  (cost=4300017.37..4300018.15 rows=31 width=40) (actual time=1616568.351..1616568.404 rows=11 loops=1)
   ->  Unique  (cost=4300017.37..4300021.47 rows=164 width=40) (actual time=1616568.347..1616568.383 rows=11 loops=1)
         ->  Sort  (cost=4300017.37..4300017.78 rows=164 width=40) (actual time=1616568.342..1616568.349 rows=11 loops=1)
               Sort Key: packageupload.id, packageupload.archive, packageupload.changesfile, packageupload.date_created, packageupload.package_copy_job, packageupload.pocket, packageupload.signing_key
               Sort Method: quicksort  Memory: 17kB
               ->  Hash Right Join  (cost=3978824.68..4300011.34 rows=164 width=40) (actual time=1614733.292..1616568.144 rows=11 loops=1)
                     Hash Cond: (packageuploadcustom.packageupload = packageupload.id)
                     Filter: ((packagecopyjob.package_name ~~ '%fglrx-installer-updates%'::text) OR (sourcepackagename.name ~~ '%fglrx-installer-updates%'::text) OR (binarypackagename.name ~~ '%fglrx-installer-updates%'::text) OR (libraryfilealias.filename ~~ '%fglrx-installer-updates%'::text))
                     ->  Hash Left Join  (cost=1475901.71..1779659.34 rows=407243 width=47) (actual time=1003103.339..1145793.114 rows=407113 loops=1)
                           Hash Cond: (packageuploadcustom.libraryfilealias = libraryfilealias.id)
                           ->  Seq Scan on packageuploadcustom  (cost=0.00..6666.43 rows=407243 width=8) (actual time=0.040..407.841 rows=407113 loops=1)
                           ->  Hash  (cost=814543.87..814543.87 rows=31065587 width=47) (actual time=1003099.933..1003099.933 rows=31065171 loops=1)
                                 Buckets: 8192  Batches: 512  Memory Usage: 4031kB
                                 ->  Seq Scan on libraryfilealias  (cost=0.00..814543.87 rows=31065587 width=47) (actual time=10.032..900347.222 rows=31065171 loops=1)
                     ->  Hash  (cost=2491197.28..2491197.28 rows=431896 width=95) (actual time=468263.093..468263.093 rows=241747 loops=1)
                           Buckets: 8192  Batches: 16  Memory Usage: 1122kB
                           ->  Hash Left Join  (cost=492673.94..2491197.28 rows=431896 width=95) (actual time=134516.407..467605.617 rows=241747 loops=1)
                                 Hash Cond: (binarypackagerelease.binarypackagename = binarypackagename.id)
                                 ->  Hash Right Join  (cost=484651.81..2464542.83 rows=431896 width=72) (actual time=134063.579..465503.679 rows=241747 loops=1)
                                       Hash Cond: (binarypackagerelease.build = packageuploadbuild.build)
                                       ->  Seq Scan on binarypackagerelease  (cost=0.00..1384850.81 rows=9771281 width=8) (actual time=0.037..347519.234 rows=9771288 loops=1)
                                       ->  Hash  (cost=481782.01..481782.01 rows=118464 width=72) (actual time=95370.180..95370.180 rows=87609 loops=1)
                                             Buckets: 8192  Batches: 4  Memory Usage: 2740kB
                                             ->  Hash Left Join  (cost=381695.26..481782.01 rows=118464 width=72) (actual time=86963.005..95017.279 rows=87609 loops=1)
                                                   Hash Cond: (sourcepackagerelease.sourcepackagename = sourcepackagename.id)
                                                   ->  Hash Left Join  (cost=380234.41..477951.88 rows=118464 width=62) (actual time=86870.536..94536.305 rows=87609 loops=1)
                                                         Hash Cond: (packageupload.package_copy_job = packagecopyjob.id)
                                                         ->  Hash Left Join  (cost=379664.89..476345.79 rows=118464 width=48) (actual time=86843.810..94203.101 rows=87609 loops=1)
                                                               Hash Cond: (packageuploadsource.sourcepackagerelease = sourcepackagerelease.id)
                                                               ->  Hash Right Join  (cost=81808.43..168728.13 rows=118464 width=48) (actual time=7707.168..12663.977 rows=87609 loops=1)
                                                                     Hash Cond: (packageuploadbuild.packageupload = packageupload.id)
                                                                     ->  Seq Scan on packageuploadbuild  (cost=0.00..43337.28 rows=2728628 width=8) (actual time=0.045..2818.731 rows=2728644 loops=1)
                                                                     ->  Hash  (cost=79343.63..79343.63 rows=118464 width=44) (actual time=3602.759..3602.759 rows=87272 loops=1)
                                                                           Buckets: 8192  Batches: 2  Memory Usage: 2474kB
                                                                           ->  Hash Right Join  (cost=39173.36..79343.63 rows=118464 width=44) (actual time=1951.615..3470.148 rows=87272 loops=1)
                                                                                 Hash Cond: (packageuploadsource.packageupload = packageupload.id)
                                                                                 ->  Seq Scan on packageuploadsource  (cost=0.00..19790.44 rows=1246044 width=8) (actual time=0.022..1185.546 rows=1246067 loops=1)
                                                                                 ->  Hash  (cost=36766.56..36766.56 rows=118464 width=40) (actual time=308.566..308.566 rows=87272 loops=1)
                                                                                       Buckets: 16384  Batches: 2  Memory Usage: 2419kB
                                                                                       ->  Bitmap Heap Scan on packageupload  (cost=3059.44..36766.56 rows=118464 width=40) (actual time=28.160..190.916 rows=87272 loops=1)
                                                                                             Recheck Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 107) AND (status = 3))
                                                                                             ->  Bitmap Index Scan on packageupload__archive__distroseries__status__idx  (cost=0.00..3029.82 rows=118464 width=0) (actual time=26.207..26.207 rows=87273 loops=1)
                                                                                                   Index Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 107) AND (status = 3))
                                                               ->  Hash  (cost=274941.87..274941.87 rows=1396687 width=8) (actual time=79136.374..79136.374 rows=1396692 loops=1)
                                                                     Buckets: 32768  Batches: 8  Memory Usage: 4782kB
                                                                     ->  Seq Scan on sourcepackagerelease  (cost=0.00..274941.87 rows=1396687 width=8) (actual time=0.034..76811.515 rows=1396692 loops=1)
                                                         ->  Hash  (cost=430.90..430.90 rows=11090 width=18) (actual time=26.668..26.668 rows=11084 loops=1)
                                                               Buckets: 2048  Batches: 1  Memory Usage: 412kB
                                                               ->  Seq Scan on packagecopyjob  (cost=0.00..430.90 rows=11090 width=18) (actual time=0.017..14.737 rows=11084 loops=1)
                                                   ->  Hash  (cost=817.60..817.60 rows=51460 width=18) (actual time=92.387..92.387 rows=51462 loops=1)
                                                         Buckets: 8192  Batches: 1  Memory Usage: 1927kB
                                                         ->  Seq Scan on sourcepackagename  (cost=0.00..817.60 rows=51460 width=18) (actual time=0.018..41.357 rows=51462 loops=1)
                                 ->  Hash  (cost=3808.17..3808.17 rows=217917 width=31) (actual time=451.961..451.961 rows=217919 loops=1)
                                       Buckets: 16384  Batches: 4  Memory Usage: 2747kB
                                       ->  Seq Scan on binarypackagename  (cost=0.00..3808.17 rows=217917 width=31) (actual time=0.042..181.750 rows=217919 loops=1)
 Total runtime: 1616574.237 ms
(56 rows)

I'm not great at reading EXPLAIN output, but it looks as though an awful lot of this is down to all those LIKE operators.  But it's kind of moot; there doesn't seem much point in worrying about making a ~30-minute query worse.

Add the pocket restriction produced by the API query mentioned in the MP and the old query becomes much more sensible.  There are 117 uploads to oneiric-updates in the DONE status in dogfood's database.

EXPLAIN ANALYZE SELECT DISTINCT PackageUpload.archive, PackageUpload.changesfile, PackageUpload.date_created, PackageUpload.distroseries, PackageUpload.id, PackageUpload.package_copy_job, PackageUpload.pocket, PackageUpload.signing_key, PackageUpload.status FROM PackageUpload LEFT JOIN PackageCopyJob ON PackageCopyJob.id = PackageUpload.package_copy_job LEFT JOIN PackageUploadSource ON PackageUploadSource.packageupload = PackageUpload.id LEFT JOIN SourcePackageRelease ON SourcePackageRelease.id = PackageUploadSource.sourcepackagerelease LEFT JOIN SourcePackageName ON SourcePackageName.id = SourcePackageRelease.sourcepackagename LEFT JOIN PackageUploadBuild ON PackageUploadBuild.packageupload = PackageUpload.id LEFT JOIN BinaryPackageRelease ON BinaryPackageRelease.build = PackageUploadBuild.build LEFT JOIN BinaryPackageName ON BinaryPackageName.id = BinaryPackageRelease.binarypackagename LEFT JOIN PackageUploadCustom ON PackageUploadCustom.packageupload = PackageUpload.id LEFT JOIN LibraryFileAlias ON LibraryFileAlias.id = PackageUploadCustom.libraryfilealias WHERE PackageUpload.distroseries = 107 AND PackageUpload.status IN (3) AND PackageUpload.archive IN (1, 534) AND PackageUpload.pocket IN (20) AND (PackageCopyJob.package_name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR SourcePackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR BinaryPackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR LibraryFileAlias.filename LIKE '%fglrx-installer-updates%' ESCAPE '!') ORDER BY PackageUpload.id DESC LIMIT 31 OFFSET 0;
 Limit  (cost=133022.20..133022.23 rows=1 width=40) (actual time=612.471..612.475 rows=1 loops=1)
   ->  Unique  (cost=133022.20..133022.23 rows=1 width=40) (actual time=612.466..612.468 rows=1 loops=1)
         ->  Sort  (cost=133022.20..133022.21 rows=1 width=40) (actual time=612.464..612.464 rows=1 loops=1)
               Sort Key: packageupload.id, packageupload.archive, packageupload.changesfile, packageupload.date_created, packageupload.package_copy_job, packageupload.signing_key
               Sort Method: quicksort  Memory: 17kB
               ->  Nested Loop Left Join  (cost=113465.98..133022.19 rows=1 width=40) (actual time=610.364..612.368 rows=1 loops=1)
                     Filter: ((packagecopyjob.package_name ~~ '%fglrx-installer-updates%'::text) OR (sourcepackagename.name ~~ '%fglrx-installer-updates%'::text) OR (binarypackagename.name ~~ '%fglrx-installer-updates%'::text) OR (libraryfilealias.filename ~~ '%fglrx-installer-updates%'::text))
                     ->  Nested Loop Left Join  (cost=113465.98..124448.95 rows=849 width=99) (actual time=291.280..611.810 rows=127 loops=1)
                           ->  Hash Left Join  (cost=113465.98..118116.80 rows=849 width=95) (actual time=291.200..610.233 rows=127 loops=1)
                                 Hash Cond: (sourcepackagerelease.sourcepackagename = sourcepackagename.id)
                                 ->  Hash Right Join  (cost=112005.13..116638.97 rows=849 width=85) (actual time=185.722..504.445 rows=127 loops=1)
                                       Hash Cond: (binarypackagename.id = binarypackagerelease.binarypackagename)
                                       ->  Seq Scan on binarypackagename  (cost=0.00..3808.17 rows=217917 width=31) (actual time=0.041..185.980 rows=217919 loops=1)
                                       ->  Hash  (cost=111994.51..111994.51 rows=849 width=62) (actual time=151.313..151.313 rows=127 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                             ->  Hash Right Join  (cost=111480.43..111994.51 rows=849 width=62) (actual time=134.652..151.011 rows=127 loops=1)
                                                   Hash Cond: (packagecopyjob.id = packageupload.package_copy_job)
                                                   ->  Seq Scan on packagecopyjob  (cost=0.00..430.90 rows=11090 width=18) (actual time=0.023..12.235 rows=11084 loops=1)
                                                   ->  Hash  (cost=111469.82..111469.82 rows=849 width=48) (actual time=130.076..130.076 rows=127 loops=1)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                         ->  Nested Loop Left Join  (cost=3029.88..111469.82 rows=849 width=48) (actual time=113.421..129.821 rows=127 loops=1)
                                                               ->  Nested Loop Left Join  (cost=3029.88..42651.10 rows=233 width=48) (actual time=113.414..129.143 rows=117 loops=1)
                                                                     ->  Nested Loop Left Join  (cost=3029.88..40668.10 rows=233 width=44) (actual time=113.375..127.482 rows=117 loops=1)
                                                                           ->  Nested Loop Left Join  (cost=3029.88..38939.91 rows=233 width=44) (actual time=113.300..126.826 rows=117 loops=1)
                                                                                 ->  Bitmap Heap Scan on packageupload  (cost=3029.88..37033.16 rows=233 width=40) (actual time=113.220..125.111 rows=117 loops=1)
                                                                                       Recheck Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 107) AND (status = 3))
                                                                                       Filter: (pocket = 20)
                                                                                       ->  Bitmap Index Scan on packageupload__archive__distroseries__status__idx  (cost=0.00..3029.82 rows=118464 width=0) (actual time=27.891..27.891 rows=87273 loops=1)
                                                                                             Index Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 107) AND (status = 3))
                                                                                 ->  Index Scan using packageuploadsource__packageupload__key on packageuploadsource  (cost=0.00..8.17 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=117)
                                                                                       Index Cond: (packageupload = packageupload.id)
                                                                           ->  Index Scan using sourcepackagerelease_pkey on sourcepackagerelease  (cost=0.00..7.40 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=117)
                                                                                 Index Cond: (id = packageuploadsource.sourcepackagerelease)
                                                                     ->  Index Scan using distroreleasequeuebuild__distroreleasequeue__build__unique on packageuploadbuild  (cost=0.00..8.50 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=117)
                                                                           Index Cond: (packageupload = packageupload.id)
                                                               ->  Index Scan using binarypackagerelease_build_idx on binarypackagerelease  (cost=0.00..294.05 rows=105 width=8) (actual time=0.002..0.003 rows=0 loops=117)
                                                                     Index Cond: (build = packageuploadbuild.build)
                                 ->  Hash  (cost=817.60..817.60 rows=51460 width=18) (actual time=105.405..105.405 rows=51462 loops=1)
                                       Buckets: 8192  Batches: 1  Memory Usage: 1927kB
                                       ->  Seq Scan on sourcepackagename  (cost=0.00..817.60 rows=51460 width=18) (actual time=0.018..44.858 rows=51462 loops=1)
                           ->  Index Scan using packageuploadcustom__packageupload__idx on packageuploadcustom  (cost=0.00..7.45 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=127)
                                 Index Cond: (packageupload = packageupload.id)
                     ->  Index Scan using libraryfilealias_pkey on libraryfilealias  (cost=0.00..10.08 rows=1 width=47) (actual time=0.001..0.001 rows=0 loops=127)
                           Index Cond: (id = packageuploadcustom.libraryfilealias)
 Total runtime: 613.976 ms
(45 rows)

EXPLAIN ANALYZE SELECT DISTINCT PackageUpload.archive, PackageUpload.changesfile, PackageUpload.date_created, PackageUpload.distroseries, PackageUpload.id, PackageUpload.package_copy_job, PackageUpload.pocket, PackageUpload.signing_key, PackageUpload.status FROM PackageUpload LEFT JOIN PackageCopyJob ON PackageCopyJob.id = PackageUpload.package_copy_job LEFT JOIN PackageUploadSource ON PackageUploadSource.packageupload = PackageUpload.id LEFT JOIN PackageUploadBuild ON PackageUploadBuild.packageupload = PackageUpload.id LEFT JOIN BinaryPackageBuild ON BinaryPackageBuild.id = PackageUploadBuild.build LEFT JOIN SourcePackageRelease ON SourcePackageRelease.id = PackageUploadSource.sourcepackagerelease OR SourcePackageRelease.id = BinaryPackageBuild.source_package_release LEFT JOIN SourcePackageName ON SourcePackageName.id = SourcePackageRelease.sourcepackagename LEFT JOIN BinaryPackageRelease ON BinaryPackageRelease.build = PackageUploadBuild.build LEFT JOIN BinaryPackageName ON BinaryPackageName.id = BinaryPackageRelease.binarypackagename LEFT JOIN PackageUploadCustom ON PackageUploadCustom.packageupload = PackageUpload.id LEFT JOIN LibraryFileAlias ON LibraryFileAlias.id = PackageUploadCustom.libraryfilealias WHERE PackageUpload.distroseries = 107 AND PackageUpload.status IN (3) AND PackageUpload.pocket IN (20) AND PackageUpload.archive IN (1, 534) AND (PackageCopyJob.package_name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR SourcePackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR BinaryPackageName.name LIKE '%fglrx-installer-updates%' ESCAPE '!' OR LibraryFileAlias.filename LIKE '%fglrx-installer-updates%' ESCAPE '!') ORDER BY PackageUpload.id DESC LIMIT 31 OFFSET 0;
 Limit  (cost=168327.07..168327.10 rows=1 width=40) (actual time=749.709..749.731 rows=3 loops=1)
   ->  Unique  (cost=168327.07..168327.10 rows=1 width=40) (actual time=749.705..749.722 rows=3 loops=1)
         ->  Sort  (cost=168327.07..168327.07 rows=1 width=40) (actual time=749.702..749.705 rows=7 loops=1)
               Sort Key: packageupload.id, packageupload.archive, packageupload.changesfile, packageupload.date_created, packageupload.package_copy_job, packageupload.signing_key
               Sort Method: quicksort  Memory: 17kB
               ->  Nested Loop Left Join  (cost=12521.35..168327.06 rows=1 width=40) (actual time=671.281..749.572 rows=7 loops=1)
                     Filter: ((packagecopyjob.package_name ~~ '%fglrx-installer-updates%'::text) OR (sourcepackagename.name ~~ '%fglrx-installer-updates%'::text) OR (binarypackagename.name ~~ '%fglrx-installer-updates%'::text) OR (libraryfilealias.filename ~~ '%fglrx-installer-updates%'::text))
                     ->  Nested Loop Left Join  (cost=12521.35..151158.16 rows=1699 width=99) (actual time=665.247..748.955 rows=127 loops=1)
                           ->  Hash Left Join  (cost=12521.35..138593.55 rows=1699 width=95) (actual time=665.176..747.344 rows=127 loops=1)
                                 Hash Cond: (binarypackagerelease.binarypackagename = binarypackagename.id)
                                 ->  Hash Left Join  (cost=4499.22..129013.81 rows=1699 width=72) (actual time=206.272..226.782 rows=127 loops=1)
                                       Hash Cond: (sourcepackagerelease.sourcepackagename = sourcepackagename.id)
                                       ->  Nested Loop Left Join  (cost=3038.37..127518.98 rows=1699 width=62) (actual time=109.941..130.137 rows=127 loops=1)
                                             Join Filter: ((sourcepackagerelease.id = packageuploadsource.sourcepackagerelease) OR (sourcepackagerelease.id = binarypackagebuild.source_package_release))
                                             ->  Nested Loop Left Join  (cost=3029.88..113493.79 rows=849 width=66) (actual time=109.821..128.270 rows=127 loops=1)
                                                   ->  Nested Loop Left Join  (cost=3029.88..44594.61 rows=233 width=66) (actual time=109.813..127.640 rows=117 loops=1)
                                                         ->  Nested Loop Left Join  (cost=3029.88..42612.40 rows=233 width=62) (actual time=109.806..127.018 rows=117 loops=1)
                                                               ->  Nested Loop Left Join  (cost=3029.88..40629.40 rows=233 width=58) (actual time=109.766..125.342 rows=117 loops=1)
                                                                     ->  Nested Loop Left Join  (cost=3029.88..38722.65 rows=233 width=54) (actual time=109.694..123.677 rows=117 loops=1)
                                                                           ->  Bitmap Heap Scan on packageupload  (cost=3029.88..37033.16 rows=233 width=40) (actual time=109.673..121.869 rows=117 loops=1)
                                                                                 Recheck Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 107) AND (status = 3))
                                                                                 Filter: (pocket = 20)
                                                                                 ->  Bitmap Index Scan on packageupload__archive__distroseries__status__idx  (cost=0.00..3029.82 rows=118464 width=0) (actual time=25.881..25.881 rows=87273 loops=1)
                                                                                       Index Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 107) AND (status = 3))
                                                                           ->  Index Scan using packagecopyjob_pkey on packagecopyjob  (cost=0.00..7.24 rows=1 width=18) (actual time=0.010..0.011 rows=1 loops=117)
                                                                                 Index Cond: (id = packageupload.package_copy_job)
                                                                     ->  Index Scan using packageuploadsource__packageupload__key on packageuploadsource  (cost=0.00..8.17 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=117)
                                                                           Index Cond: (packageupload = packageupload.id)
                                                               ->  Index Scan using distroreleasequeuebuild__distroreleasequeue__build__unique on packageuploadbuild  (cost=0.00..8.50 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=117)
                                                                     Index Cond: (packageupload = packageupload.id)
                                                         ->  Index Scan using binarypackagebuild_pkey on binarypackagebuild  (cost=0.00..8.49 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=117)
                                                               Index Cond: (id = packageuploadbuild.build)
                                                   ->  Index Scan using binarypackagerelease_build_idx on binarypackagerelease  (cost=0.00..294.39 rows=105 width=8) (actual time=0.002..0.003 rows=0 loops=117)
                                                         Index Cond: (build = packageuploadbuild.build)
                                             ->  Bitmap Heap Scan on sourcepackagerelease  (cost=8.49..16.49 rows=2 width=8) (actual time=0.010..0.010 rows=0 loops=127)
                                                   Recheck Cond: ((id = packageuploadsource.sourcepackagerelease) OR (id = binarypackagebuild.source_package_release))
                                                   ->  BitmapOr  (cost=8.49..8.49 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=127)
                                                         ->  Bitmap Index Scan on sourcepackagerelease_pkey  (cost=0.00..4.24 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=127)
                                                               Index Cond: (id = packageuploadsource.sourcepackagerelease)
                                                         ->  Bitmap Index Scan on sourcepackagerelease_pkey  (cost=0.00..4.24 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=127)
                                                               Index Cond: (id = binarypackagebuild.source_package_release)
                                       ->  Hash  (cost=817.60..817.60 rows=51460 width=18) (actual time=96.240..96.240 rows=51462 loops=1)
                                             Buckets: 8192  Batches: 1  Memory Usage: 1927kB
                                             ->  Seq Scan on sourcepackagename  (cost=0.00..817.60 rows=51460 width=18) (actual time=0.019..43.473 rows=51462 loops=1)
                                 ->  Hash  (cost=3808.17..3808.17 rows=217917 width=31) (actual time=458.707..458.707 rows=217919 loops=1)
                                       Buckets: 16384  Batches: 4  Memory Usage: 2747kB
                                       ->  Seq Scan on binarypackagename  (cost=0.00..3808.17 rows=217917 width=31) (actual time=0.042..194.033 rows=217919 loops=1)
                           ->  Index Scan using packageuploadcustom__packageupload__idx on packageuploadcustom  (cost=0.00..7.38 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=127)
                                 Index Cond: (packageupload = packageupload.id)
                     ->  Index Scan using libraryfilealias_pkey on libraryfilealias  (cost=0.00..10.08 rows=1 width=47) (actual time=0.001..0.001 rows=0 loops=127)
                           Index Cond: (id = packageuploadcustom.libraryfilealias)
 Total runtime: 751.920 ms
(52 rows)

So, yes, that is a bit worse, although not (at this scale) critically so.  I'd welcome somebody who's actually familiar with PostgreSQL performance analysis having a look at this and seeing how much of this is intrinsic and how much could be improved; and also whether this increase in runtime is actually a problem here.  Obviously there are various possible scales involved; my feeling is that a queue involving 100+ uploads would be atypically large for us these days unless we're going and trawling through historical data for some reason (in which case there are usually better ways to do it anyway), but I might be missing something.

Colin Watson (cjwatson) on 2012-12-12

Changed in launchpad:
assignee:	Colin Watson (cjwatson) → Steve Kowalik (stevenk)

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-12-13:

#5

r12228 in db-stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/12228) is part of this bug's fix.

tags:

added: qa-needstesting

Steve Kowalik (stevenk) on 2012-12-13

tags:

added: qa-ok
removed: qa-needstesting

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-12-14:

#6

Fixed in stable r16371 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16371>.

tags:	added: qa-needstesting removed: qa-ok
Changed in launchpad:
status:	In Progress → Fix Committed

Steve Kowalik (stevenk) on 2012-12-14

Changed in launchpad:
status:	Fix Committed → In Progress

Steve Kowalik (stevenk) on 2012-12-14

tags:

added: qa-ok
removed: qa-needstesting

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-12-17:

#7

r16375 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16375) is part of this bug's fix.

tags:

added: qa-needstesting
removed: qa-ok

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-12-17:

#8

r12235 in db-stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/12235) is part of this bug's fix.

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-12-17:

#9

r16376 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16376) is part of this bug's fix.

Steve Kowalik (stevenk) on 2012-12-17

tags:

added: qa-ok
removed: qa-needstesting

Steve Kowalik (stevenk) on 2012-12-19

Changed in launchpad:
status:	In Progress → Fix Released

Launchpad itself

could queue filters match source as well as binaries?

Bug Description

Related branches

Other bug subscribers

Remote bug watches