Distribution:+ppas timeouts

Bug #790535 reported by Robert Collins on 2011-05-31
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Steve Kowalik

Bug Description

  4 SELECT COUNT(*) FROM Archive, ValidPersonOrTeamCache WHERE Archive.purpose = $INT AND Archive.dis ... ValidPersonOrTeamCache.id AND Archive.private = FALSE AND Archive.enabled = TRUE AND ($INT=$INT):
   GET: 4 Robots: 0 Local: 4
      4 https://launchpad.net/ubuntu/+ppas (Distribution:+ppas)
       OOPS-1976AT50, OOPS-1976CF21, OOPS-1976EC31, OOPS-1976O20

Slow query:
23. 91 8927ms SQL-launchpad-main-master
FROM Archive,
WHERE Archive.purpose = 2
  AND Archive.distribution = 1
  AND Archive.OWNER = ValidPersonOrTeamCache.id
  AND Archive.private = FALSE
  AND Archive.enabled = TRUE
  AND (1=1)

Related branches

Robert Collins (lifeless) wrote :

Cold cache:

                                                                               QUERY PLAN
 Aggregate (cost=100580.29..100580.30 rows=1 width=0) (actual time=100274.103..100274.104 rows=1 loops=1)
   -> Nested Loop Left Join (cost=0.00..100529.87 rows=20167 width=0) (actual time=47.910..100260.601 rows=20171 loops=1)
         Filter: (((person.teamowner IS NOT NULL) AND (person.merged IS NULL)) OR ((person.teamowner IS NULL) AND (account.status = 20) AND (emailaddress.status = 4)))
         -> Nested Loop Left Join (cost=0.00..86267.63 rows=20764 width=16) (actual time=19.015..43137.671 rows=29889 loops=1)
               -> Nested Loop (cost=0.00..65571.41 rows=20492 width=12) (actual time=0.096..7466.364 rows=20515 loops=1)
                     -> Seq Scan on archive (cost=0.00..1897.40 rows=20492 width=4) (actual time=0.033..440.219 rows=20515 loops=1)
                           Filter: ((NOT private) AND enabled AND (purpose = 2) AND (distribution = 1))
                     -> Index Scan using person_pkey on person (cost=0.00..3.09 rows=1 width=12) (actual time=0.339..0.340 rows=1 loops=20515)
                           Index Cond: (person.id = archive.owner)
               -> Index Scan using emailaddress__person__status__idx on emailaddress (cost=0.00..0.95 rows=5 width=12) (actual time=1.416..1.736 rows=1 loops=20515)
                     Index Cond: (person.id = emailaddress.person)
         -> Index Scan using account_pkey on account (cost=0.00..0.67 rows=1 width=8) (actual time=1.906..1.908 rows=1 loops=29889)
               Index Cond: (emailaddress.account = account.id)
 Total runtime: 100274.418 ms

description: updated
Robert Collins (lifeless) wrote :

Without the valid person check:

                                                    QUERY PLAN
 Aggregate (cost=1948.64..1948.65 rows=1 width=0) (actual time=31.173..31.174 rows=1 loops=1)
   -> Seq Scan on archive (cost=0.00..1897.40 rows=20492 width=0) (actual time=0.019..25.743 rows=20515 loops=1)
         Filter: ((NOT private) AND enabled AND (purpose = 2) AND (distribution = 1))
 Total runtime: 31.255 ms
(4 rows)

William Grant (wgrant) on 2012-10-22
tags: added: ppa
Launchpad QA Bot (lpqabot) wrote :
Changed in launchpad:
assignee: nobody → Steve Kowalik (stevenk)
tags: added: qa-needstesting
Changed in launchpad:
status: Triaged → Fix Committed
Steve Kowalik (stevenk) on 2013-01-11
tags: added: qa-ok
removed: qa-needstesting
Steve Kowalik (stevenk) on 2013-01-11
Changed in launchpad:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers