Launchpad itself

Distribution:+bugtarget-portlet-tags-content timeouts

Bug #736002 reported by Robert Collins
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
William Grant
Launchpad itself 11.05

Bug Description

 1 SELECT BugTag.tag, COUNT(*) FROM BugTag LEFT JOIN Bug ON Bug.id = BugTag.bug LEFT JOIN BugTask ON ... ) AND BugTask.distribution = $INT AND Bug.private = FALSE GROUP BY BugTag.tag ORDER BY BugTag.tag:
   GET: 1 Robots: 0 Local: 1
      1 https://bugs.launchpad.net/ubuntu/+bugtarget-portlet-tags-content (Distribution:+bugtarget-portlet-tags-content)
       OOPS-1900J34

22 SELECT BugTag.tag, COUNT(*) FROM BugTag LEFT JOIN Bug ON Bug.id = BugTag.bug LEFT JOIN BugTask ON ... ) AND BugTask.distribution = $INT AND Bug.private = FALSE GROUP BY BugTag.tag ORDER BY BugTag.tag:
   GET: 22 Robots: 0 Local: 21
     15 https://launchpad.net/ubuntu/+bugtarget-portlet-tags-content (Distribution:+bugtarget-portlet-tags-content)
       OOPS-1921A1131, OOPS-1921A1206, OOPS-1921A194, OOPS-1921A437, OOPS-1921A788
      6 https://bugs.launchpad.net/ubuntu/+bugtarget-portlet-tags-content (Distribution:+bugtarget-portlet-tags-content)
       OOPS-1921A240, OOPS-1921A498, OOPS-1921C644, OOPS-1921G824, OOPS-1921J719
      1 https://edge.launchpad.net/ubuntu/+bugtarget-portlet-tags-content (Distribution:+bugtarget-portlet-tags-content)
       OOPS-1921EA824

this query is run twice:
SELECT BugTag.tag, COUNT(*)
FROM BugTag
LEFT JOIN Bug ON Bug.id = BugTag.bug
LEFT JOIN BugTask ON BugTask.bug = Bug.id
AND BugTask.status IN (10,
                       15,
                       20,
                       21,
                       22,
                       25)
WHERE BugTask.status IN (10,
                         15,
                         20,
                         21,
                         22,
                         25)
  AND BugTask.distribution = 1
  AND Bug.private = FALSE
GROUP BY BugTag.tag
ORDER BY BugTag.tag

It could be simplified, changed from left join to inner joins.

See original description
Tags: qa-ok timeout

Related branches

lp:~wgrant/launchpad/bug-736002
Jeroen T. Vermeulen (community): Approve (code)
Diff: 430 lines (+113/-117)
11 files modified
lib/lp/bugs/browser/bugtarget.py (+28/-53)
lib/lp/bugs/doc/bug-tags.txt (+20/-12)
lib/lp/bugs/interfaces/bugtarget.py (+3/-1)
lib/lp/bugs/model/bug.py (+43/-37)
lib/lp/registry/model/distribution.py (+3/-2)
lib/lp/registry/model/distributionsourcepackage.py (+2/-2)
lib/lp/registry/model/distroseries.py (+3/-2)
lib/lp/registry/model/product.py (+3/-2)
lib/lp/registry/model/productseries.py (+3/-2)
lib/lp/registry/model/projectgroup.py (+3/-2)
lib/lp/registry/model/sourcepackage.py (+2/-2)
lp:~wgrant/launchpad/bug-736002-seriously-this-time
Robert Collins (community): Approve
Diff: 12 lines (+1/-1)
1 file modified
lib/lp/bugs/templates/bugtarget-portlet-tags-content.pt (+1/-1)
Robert Collins (lifeless)
description: updated
Revision history for this message
Robert Collins (lifeless) wrote :
Download full text (5.0 KiB)

Original query plan (hot)
                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (cost=265108.46..265149.57 rows=16442 width=10) (actual time=3192.140..3192.923 rows=6137 loops=1)
   Sort Key: bugtag.tag
   Sort Method: quicksort Memory: 567kB
   -> HashAggregate (cost=263751.58..263957.10 rows=16442 width=10) (actual time=3181.959..3184.173 rows=6137 loops=1)
         -> Hash Join (cost=242644.43..263462.46 rows=57823 width=10) (actual time=1683.258..3054.185 rows=290833 loops=1)
               Hash Cond: (bugtag.bug = bug.id)
               -> Seq Scan on bugtag (cost=0.00..11877.25 rows=739825 width=14) (actual time=0.014..156.715 rows=739317 loops=1)
               -> Hash (cost=241957.78..241957.78 rows=54932 width=8) (actual time=1683.098..1683.098 rows=177175 loops=1)
                     -> Nested Loop (cost=2578.18..241957.78 rows=54932 width=8) (actual time=117.228..1588.516 rows=177175 loops=1)
                           -> Bitmap Heap Scan on bugtask (cost=2578.18..67612.70 rows=54932 width=4) (actual time=117.188..326.646 rows=190273 loops=1)
                                 Recheck Cond: ((status = ANY ('{10,15,20,21,22,25}'::integer[])) AND (status = ANY ('{10,15,20,21,22,25}'::integer[])))
                                 Filter: (distribution = 1)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..2564.45 rows=91735 width=0) (actual time=97.028..97.028 rows=308932 loops=1)
                                       Index Cond: ((status = ANY ('{10,15,20,21,22,25}'::integer[])) AND (status = ANY ('{10,15,20,21,22,25}'::integer[])))
                           -> Index Scan using bug_pkey on bug (cost=0.00..3.16 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=190273)
                                 Index Cond: (bug.id = bugtask.bug)
                                 Filter: (NOT bug.private)
 Total runtime: 3196.765 ms

simpler:

SELECT BugTag.tag, COUNT(*)
FROM BugTag
JOIN Bug ON Bug.id = BugTag.bug
JOIN BugTask ON BugTask.bug = Bug.id
AND BugTask.status IN (10, 15, 20, 21, 22, 25)
WHERE BugTask.distribution = 1
  AND Bug.private = FALSE
GROUP BY BugTag.tag
ORDER BY BugTag.tag

launchpad_qastaging=> explain analyze SELECT BugTag.tag, COUNT(*)
FROM BugTag
JOIN Bug ON Bug.id = BugTag.bug
JOIN BugTask ON BugTask.bug = Bug.id
AND BugTask.status IN (10, 15, 20, 21, 22, 25)
WHERE BugTask.distribution = 1
  AND Bug.private = FALSE
GROUP BY BugTag.tag
ORDER BY BugTag.tag;
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (cost=286217.52..286258.63 rows=16442 width=10) (actual time...

Read more...

description: updated
Revision history for this message
Robert Collins (lifeless) wrote :
Download full text (4.0 KiB)

constraining by official bug tags:

explain analyze SELECT BugTag.tag, COUNT(*)
FROM BugTag join officialbugtag on officialbugtag.tag=bugtag.tag and officialbugtag.distribution=1
JOIN Bug ON Bug.id = BugTag.bug
JOIN BugTask ON BugTask.bug = Bug.id
AND BugTask.status IN (10, 15, 20, 21, 22, 25)
WHERE BugTask.distribution = 1
  AND Bug.private = FALSE
GROUP BY BugTag.tag
ORDER BY BugTag.tag;

cold

                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (cost=0.00..25903.89 rows=3969 width=10) (actual time=491.505..1791.190 rows=35 loops=1)
   -> Nested Loop (cost=0.00..25834.43 rows=3969 width=10) (actual time=20.068..1783.838 rows=9573 loops=1)
         -> Nested Loop (cost=0.00..16262.35 rows=3969 width=18) (actual time=20.049..1648.050 rows=9786 loops=1)
               -> Nested Loop (cost=0.00..4175.33 rows=16864 width=14) (actual time=10.101..219.246 rows=28341 loops=1)
                     -> Index Scan using officialbugtag__distribution__tag__key on officialbugtag (cost=0.00..34.80 rows=35 width=10) (actual time=0.039..0.256 rows=35 loops=1)
                           Index Cond: (distribution = 1)
                     -> Index Scan using bugtag__tag__bug__key on bugtag (cost=0.00..117.74 rows=45 width=14) (actual time=1.994..5.730 rows=810 loops=35)
                           Index Cond: (bugtag.tag = officialbugtag.tag)
               -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.70 rows=1 width=4) (actual time=0.048..0.049 rows=0 loops=28341)
                     Index Cond: (bugtask.bug = bugtag.bug)
                     Filter: ((bugtask.distribution = 1) AND (bugtask.status = ANY ('{10,15,20,21,22,25}'::integer[])))
         -> Index Scan using bug_pkey on bug (cost=0.00..2.40 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=9786)
               Index Cond: (bug.id = bugtag.bug)
               Filter: (NOT bug.private)
 Total runtime: 1791.348 ms

hot:

                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (cost=0.00..25903.89 rows=3969 width=10) (actual time=10.075..306.462 rows=35 loops=1)
   -> Nested Loop (cost=0.00..25834.43 rows=3969 width=10) (actual time=0.166..303.301 rows=9573 loops=1)
         -> Nested Loop (cost=0.00..16262.35 rows=3969 width=18) (actual time=0.154..236.359 rows=9786 loops=1)
               -> Nested Loop (cost=0.00..4175.33 rows=16864 width=14) (actual time=0.042..44.562 rows=28341 loops=1)
                     -> Index Scan using officialbugtag__distribution__tag__key on officialbugtag (cost=0.00..34.80 rows=35 width=10) (a...

Read more...

William Grant (wgrant)
Changed in launchpad:
assignee: nobody → William Grant (wgrant)
status: Triaged → In Progress
Revision history for this message
Robert Collins (lifeless) wrote :

SELECT BugTag.tag, COUNT(*)
FROM BugTag join officialbugtag on officialbugtag.tag=bugtag.tag and officialbugtag.product=10294
JOIN Bug ON Bug.id = BugTag.bug
JOIN BugTask ON BugTask.bug = Bug.id
AND BugTask.status IN (10, 15, 20, 21, 22, 25)
WHERE BugTask.product=10294
  AND Bug.private = FALSE
GROUP BY BugTag.tag
ORDER BY BugTag.tag;

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

bah, e-wrong-context. sorrry.

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

http://paste.ubuntu.com/591153/ is what we came up with on friday.

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

(Note that the point of this formulation is to do everything the bug needs in one call, rather than the repeated calls currently done).

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

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

Changed in launchpad:
milestone: none → 11.05
tags: added: qa-needstesting
Changed in launchpad:
status: In Progress → Fix Committed
Revision history for this message
Robert Collins (lifeless) wrote :

Brings it (hot) down to 6 seconds; we may still have duplicate queries or something.

tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
William Grant (wgrant) wrote :

Indeed, there is a dupe query from the template.

Changed in launchpad:
status: Fix Committed → In Progress
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

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

tags: added: qa-needstesting
removed: qa-ok
Changed in launchpad:
status: In Progress → Fix Committed
William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Robert Collins (lifeless)
Changed in launchpad:
status: Fix Committed → Fix Released
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.