Launchpad itself

Archive:EntryResource:getPublishedSources

Bug #727560 reported by Robert Collins
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
Robert Collins
Launchpad itself 11.03

Bug Description

 25 SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePac ... SourcePackageRelease.version DESC, SourcePackagePublishingHistory.id DESC LIMIT $INT OFFSET $INT:
   GET: 24 POST: 1 Robots: 0 Local: 3
     21 https://api.launchpad.net/1.0/ubuntu/+archive/primary (Archive:EntryResource:getPublishedSources)
OOPS-1886A2323, OOPS-1886A2325, OOPS-1886A2331, OOPS-1886B1613, OOPS-1886B1616

8. 97 15002ms SQL-launchpad-main-master
SELECT SourcePackagePublishingHistory.ancestor,
       SourcePackagePublishingHistory.archive,
       SourcePackagePublishingHistory.component,
       SourcePackagePublishingHistory.datecreated,
       SourcePackagePublishingHistory.datemadepending,
       SourcePackagePublishingHistory.datepublished,
       SourcePackagePublishingHistory.dateremoved,
       SourcePackagePublishingHistory.datesuperseded,
       SourcePackagePublishingHistory.distroseries,
       SourcePackagePublishingHistory.id,
       SourcePackagePublishingHistory.pocket,
       SourcePackagePublishingHistory.removal_comment,
       SourcePackagePublishingHistory.removed_by,
       SourcePackagePublishingHistory.scheduleddeletiondate,
       SourcePackagePublishingHistory.section,
       SourcePackagePublishingHistory.sourcepackagerelease,
       SourcePackagePublishingHistory.status,
       SourcePackagePublishingHistory.supersededby,
       "_prejoin1".user_defined_fields,
       "_prejoin1".architecturehintlist,
       "_prejoin1".build_conflicts,
       "_prejoin1".build_conflicts_indep,
       "_prejoin1".builddepends,
       "_prejoin1".builddependsindep,
       "_prejoin1".changelog,
       "_prejoin1".changelog_entry,
       "_prejoin1".component,
       "_prejoin1".copyright,
       "_prejoin1".creator,
       "_prejoin1".dateuploaded,
       "_prejoin1".dsc,
       "_prejoin1".dsc_binaries,
       "_prejoin1".dsc_format,
       "_prejoin1".dsc_maintainer_rfc822,
       "_prejoin1".dsc_standards_version,
       "_prejoin1".dscsigningkey,
       "_prejoin1".format,
       "_prejoin1".homepage,
       "_prejoin1".id,
       "_prejoin1".maintainer,
       "_prejoin1".section,
       "_prejoin1".sourcepackage_recipe_build,
       "_prejoin1".sourcepackagename,
       "_prejoin1".upload_archive,
       "_prejoin1".upload_distroseries,
       "_prejoin1".urgency,
       "_prejoin1".VERSION, "_prejoin2".account,
                            "_prejoin2".creation_comment,
                            "_prejoin2".creation_rationale,
                            "_prejoin2".datecreated,
                            "_prejoin2".defaultmembershipperiod,
                            "_prejoin2".defaultrenewalperiod,
                            "_prejoin2".displayname,
                            "_prejoin2".hide_email_addresses,
                            "_prejoin2".homepage_content,
                            "_prejoin2".icon,
                            "_prejoin2".id,
                            "_prejoin2".logo,
                            "_prejoin2".mailing_list_auto_subscribe_policy,
                            "_prejoin2".merged,
                            "_prejoin2".mugshot,
                            "_prejoin2".name,
                            "_prejoin2".personal_standing,
                            "_prejoin2".personal_standing_reason,
                            "_prejoin2".registrant,
                            "_prejoin2".renewal_policy,
                            "_prejoin2".subscriptionpolicy,
                            "_prejoin2".teamdescription,
                            "_prejoin2".teamowner,
                            "_prejoin2".verbose_bugnotifications,
                            "_prejoin2".visibility,
                            "_prejoin3".active,
                            "_prejoin3".algorithm,
                            "_prejoin3".can_encrypt,
                            "_prejoin3".fingerprint,
                            "_prejoin3".id,
                            "_prejoin3".keyid,
                            "_prejoin3".keysize,
                            "_prejoin3".OWNER, "_prejoin4".binarycount,
                                               "_prejoin4".changeslist,
                                               "_prejoin4".date_created,
                                               "_prejoin4".datereleased,
                                               "_prejoin4".defer_translation_imports,
                                               "_prejoin4".description,
                                               "_prejoin4".displayname,
                                               "_prejoin4".distribution,
                                               "_prejoin4".driver,
                                               "_prejoin4".hide_all_translations,
                                               "_prejoin4".id,
                                               "_prejoin4".language_pack_base,
                                               "_prejoin4".language_pack_delta,
                                               "_prejoin4".language_pack_full_export_requested,
                                               "_prejoin4".language_pack_proposed,
                                               "_prejoin4".messagecount,
                                               "_prejoin4".name,
                                               "_prejoin4".nominatedarchindep,
                                               "_prejoin4".OWNER, "_prejoin4".parent_series,
                                                                  "_prejoin4".sourcecount,
                                                                  "_prejoin4".releasestatus,
                                                                  "_prejoin4".summary,
                                                                  "_prejoin4".title,
                                                                  "_prejoin4".VERSION, "_prejoin5".id,
                                                                                       "_prejoin5".name
FROM SourcePackageName,
     SourcePackageRelease,
     SourcePackagePublishingHistory
LEFT JOIN SourcePackageRelease AS "_prejoin1" ON SourcePackagePublishingHistory.sourcepackagerelease = "_prejoin1".id
LEFT JOIN Person AS "_prejoin2" ON "_prejoin1".creator = "_prejoin2".id
LEFT JOIN GPGKey AS "_prejoin3" ON "_prejoin1".dscsigningkey = "_prejoin3".id
LEFT JOIN DistroSeries AS "_prejoin4" ON SourcePackagePublishingHistory.distroseries = "_prejoin4".id
LEFT JOIN Section AS "_prejoin5" ON SourcePackagePublishingHistory.section = "_prejoin5".id
WHERE SourcePackagePublishingHistory.archive = 1
  AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id
  AND SourcePackageRelease.sourcepackagename = SourcePackageName.id
  AND (1=1)
ORDER BY SourcePackageName.name,
         SourcePackageRelease.VERSION DESC, SourcePackagePublishingHistory.id DESC LIMIT 76
OFFSET 0

See original description
Tags: qa-ok timeout

Related branches

lp:~lifeless/launchpad/bug-727560
Tim Penhey (community): Approve (code)
Ian Booth (community): Approve (*code)
Diff: 1300 lines (+271/-300)
30 files modified
lib/canonical/launchpad/doc/publishing-security.txt (+6/-10)
lib/canonical/launchpad/testing/fakepackager.py (+2/-4)
lib/lp/archivepublisher/tests/archive-signing.txt (+1/-1)
lib/lp/archiveuploader/nascentupload.py (+3/-1)
lib/lp/archiveuploader/nascentuploadfile.py (+5/-3)
lib/lp/archiveuploader/tests/nascentupload-ddebs.txt (+2/-2)
lib/lp/archiveuploader/tests/test_ppauploadprocessor.py (+8/-11)
lib/lp/archiveuploader/tests/test_uploadprocessor.py (+5/-5)
lib/lp/registry/model/distroseriesdifference.py (+5/-6)
lib/lp/soyuz/browser/archive.py (+9/-15)
lib/lp/soyuz/browser/tests/publishing-views.txt (+1/-1)
lib/lp/soyuz/browser/tests/test_archive_packages.py (+2/-2)
lib/lp/soyuz/doc/archive.txt (+21/-156)
lib/lp/soyuz/doc/distribution.txt (+1/-1)
lib/lp/soyuz/doc/publishing.txt (+3/-3)
lib/lp/soyuz/doc/sourcepackagerelease-build-lookup.txt (+3/-3)
lib/lp/soyuz/interfaces/publishing.py (+4/-0)
lib/lp/soyuz/interfaces/sourcepackagerelease.py (+1/-0)
lib/lp/soyuz/model/archive.py (+76/-49)
lib/lp/soyuz/model/packagecloner.py (+0/-4)
lib/lp/soyuz/scripts/add_missing_builds.py (+2/-1)
lib/lp/soyuz/scripts/ftpmasterbase.py (+3/-3)
lib/lp/soyuz/scripts/packagecopier.py (+3/-3)
lib/lp/soyuz/scripts/tests/test_copypackage.py (+6/-6)
lib/lp/soyuz/stories/ppa/xx-delete-packages.txt (+1/-1)
lib/lp/soyuz/stories/ppa/xx-ppa-packages.txt (+2/-2)
lib/lp/soyuz/stories/soyuz/xx-package-diff.txt (+1/-1)
lib/lp/soyuz/tests/test_archive.py (+92/-2)
lib/lp/soyuz/tests/test_processaccepted.py (+2/-2)
lib/lp/soyuz/tests/test_syncpackagejob.py (+1/-2)
Robert Collins (lifeless)
description: updated
description: updated
Revision history for this message
Robert Collins (lifeless) wrote :
Download full text (6.6 KiB)

 Limit (cost=955708.70..955708.89 rows=76 width=3079)
   -> Sort (cost=955708.70..956933.77 rows=490029 width=3079)
         Sort Key: sourcepackagename.name, sourcepackagerelease.version, sourcepackagepublishinghistory.id
         -> Hash Left Join (cost=316138.47..937950.23 rows=490029 width=3079)
               Hash Cond: (sourcepackagepublishinghistory.distroseries = _prejoin4.id)
               -> Hash Left Join (cost=316135.71..931209.56 rows=490029 width=2784)
                     Hash Cond: (_prejoin1.dscsigningkey = _prejoin3.id)
                     -> Nested Loop Left Join (cost=314428.10..921059.70 rows=490029 width=2716)
                           -> Hash Left Join (cost=314428.10..674859.04 rows=490029 width=1961)
                                 Hash Cond: (sourcepackagepublishinghistory.section = _prejoin5.id)
                                 -> Hash Join (cost=314425.86..668118.91 rows=490029 width=1951)
                                       Hash Cond: (sourcepackagerelease.sourcepackagename = sourcepackagename.id)
                                       -> Merge Right Join (cost=313264.66..654706.97 rows=490029 width=1941)
                                             Merge Cond: (_prejoin1.id = sourcepackagepublishinghistory.sourcepackagerelease)
                                             -> Index Scan using sourcepackagerelease_pkey on sourcepackagerelease _prejoin1 (cost=0.00..327201.72 rows=822218 width=1801)
                                             -> Materialize (cost=313231.82..319357.18 rows=490029 width=140)
                                                   -> Sort (cost=313231.82..314456.89 rows=490029 width=140)
                                                         Sort Key: sourcepackagepublishinghistory.sourcepackagerelease
                                                         -> Hash Join (cost=180069.99..236767.94 rows=490029 width=140)
                                                               Hash Cond: (sourcepackagepublishinghistory.sourcepackagerelease = sourcepackagerelease.id)
                                                               -> Bitmap Heap Scan on sourcepackagepublishinghistory (cost=7833.09..31881.45 rows=490029 width=118)
                                                                     Recheck Cond: (archive = 1)
                                                                     -> Bitmap Index Scan on securesourcepackagepublishinghistory__archive__status__idx (cost=0.00..7710.58 rows=490029 width=0)
                                                                           Index Cond: (archive = 1)
                                                               -> Hash (cost=156338.18..156338.18 rows=822218 width=26)
                                                                     -> Seq Scan on sourcepackagerelease (cost=0.00..156338.18 rows=822218 width=26)
                                       -> Hash (cost=653.87..653.87 rows=40587 width=18)
                                             -> Seq Scan on sourcepackagename (cost=0.00..653.87 rows=40587 width=18)
                                 -> Hash (cost=1.55..1.55 rows=5...

Read more...

Revision history for this message
Robert Collins (lifeless) wrote :

Needs eager loading of
LEFT JOIN SourcePackageRelease AS "_prejoin1" ON SourcePackagePublishingHistory.sourcepackagerelease = "_prejoin1".id
LEFT JOIN Person AS "_prejoin2" ON "_prejoin1".creator = "_prejoin2".id
LEFT JOIN GPGKey AS "_prejoin3" ON "_prejoin1".dscsigningkey = "_prejoin3".id
LEFT JOIN DistroSeries AS "_prejoin4" ON SourcePackagePublishingHistory.distroseries = "_prejoin4".id
LEFT JOIN Section AS "_prejoin5" ON SourcePackagePublishingHistory.section = "_prejoin5".id

Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

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

Changed in launchpad:
assignee: nobody → Robert Collins (lifeless)
milestone: none → 11.03
tags: added: qa-needstesting
Changed in launchpad:
status: Triaged → Fix Committed
Revision history for this message
Robert Collins (lifeless) wrote :

reworked version completes in 10 seconds on staging. We will need more work done to it, but this is enough of an improvement.

tags: added: qa-ok
removed: qa-needstesting
Curtis Hovey (sinzui)
Changed in launchpad:
status: Fix Committed → Fix Released
Revision history for this message
Robert Collins (lifeless) wrote :

still times out cold, but works on a repeat, so closing for now.

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.