Bug #594247 “searchTasks with structural_subscriber times out re...” : Bugs : Launchpad itself

Revision history for this message

Brian Murray (brian-murray) wrote on 2010-06-14:

#1

This has happened with both the 'ubuntu-x-swat' team and the 'ubuntu-server' team.

Graham Binns (gmb) on 2010-06-23

tags:	added: timeout
Changed in malone:
status:	New → Triaged
importance:	Undecided → High

Deryck Hodge (deryck) on 2010-06-23

tags:

added: dhrb

Robert Collins (lifeless) on 2010-07-25

description:

updated

Revision history for this message

Robert Collins (lifeless) wrote on 2010-07-25:

#2

The query looks pretty silly at first glance :)

Revision history for this message

Robert Collins (lifeless) wrote on 2010-07-25:

#3

Download full text (11.1 KiB)

it gets 109 results:

count
-------
109
(1 row)

Time: 668277.323 ms

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=121948.39..121948.40 rows=1 width=0) (actual time=2629963.002..2629963.003 rows=1 loops=1)
   -> Nested Loop (cost=4893.17..121948.39 rows=2 width=0) (actual time=62340.873..2629962.699 rows=109 loops=1)
         Join Filter: ((public.bugtask.status = 10) OR ((public.bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (public.bugtask.date_incomplete <= bug.date_last_message)) OR ((public.bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (public.bugtask.date_incomplete > bug.date_last_message))) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25))
         -> Nested Loop IN Join (cost=4893.17..121880.12 rows=3 width=16) (actual time=23697.618..2629956.685 rows=240 loops=1)
               Join Filter: (public.bugtask.id = public.bugtask.id)
               -> Bitmap Heap Scan on bugtask (cost=4893.17..41864.40 rows=9302 width=20) (actual time=137.967..335.052 rows=2326 loops=1)
                     Recheck Cond: ((status = 10) OR ((date_incomplete IS NOT NULL) AND (status = 15)) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
                     Filter: ((distroseries IS NULL) AND (productseries IS NULL) AND (importance = 40) AND (distribution = 1))
                     -> BitmapOr (cost=4893.17..4893.17 rows=265884 width=0) (actual time=121.094..121.094 rows=0 loops=1)
                           -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..2376.56 rows=150833 width=0) (actual time=47.803..47.803 rows=149423 loops=1)
                                 Index Cond: (status = 10)
                           -> BitmapAnd (cost=695.95..695.95 rows=795 width=0) (actual time=28.032..28.032 rows=0 loops=1)
                                 -> Bitmap Index Scan on bugtask__date_incomplete__idx (cost=0.00..318.86 rows=23584 width=0) (actual time=9.988..9.988 rows=23522 loops=1)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..372.19 rows=23584 width=0) (actual time=12.606..12.606 rows=23522 loops=1)
                                       Index Cond: (status = 15)
                           -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..372.19 rows=23584 width=0) (actual time=10.452..10.452 rows=23522 loops=1)
                                 Index Cond: (status = 15)
                           -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..790.64 rows=50177 width=0) (actual time=13.935..13.935 rows=49263 loops=1)
                                 Index Cond: (status = 20)
                           -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..394.34 rows=24937 width=0) (actual time=7.999..7.999 rows=25635 loops=1)
                                 Index Cond: (status = 21)
                           -> Bitmap Index Scan ...

it gets 109 results:

count
-------
   109
(1 row)

Time: 668277.323 ms

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=121948.39..121948.40 rows=1 width=0) (actual time=2629963.002..2629963.003 rows=1 loops=1)
   ->  Nested Loop  (cost=4893.17..121948.39 rows=2 width=0) (actual time=62340.873..2629962.699 rows=109 loops=1)
         Join Filter: ((public.bugtask.status = 10) OR ((public.bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (public.bugtask.date_incomplete <= bug.date_last_message)) OR ((public.bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (public.bugtask.date_incomplete > bug.date_last_message))) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25))
         ->  Nested Loop IN Join  (cost=4893.17..121880.12 rows=3 width=16) (actual time=23697.618..2629956.685 rows=240 loops=1)
               Join Filter: (public.bugtask.id = public.bugtask.id)
               ->  Bitmap Heap Scan on bugtask  (cost=4893.17..41864.40 rows=9302 width=20) (actual time=137.967..335.052 rows=2326 loops=1)
                     Recheck Cond: ((status = 10) OR ((date_incomplete IS NOT NULL) AND (status = 15)) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
                     Filter: ((distroseries IS NULL) AND (productseries IS NULL) AND (importance = 40) AND (distribution = 1))
                     ->  BitmapOr  (cost=4893.17..4893.17 rows=265884 width=0) (actual time=121.094..121.094 rows=0 loops=1)
                           ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..2376.56 rows=150833 width=0) (actual time=47.803..47.803 rows=149423 loops=1)
                                 Index Cond: (status = 10)
                           ->  BitmapAnd  (cost=695.95..695.95 rows=795 width=0) (actual time=28.032..28.032 rows=0 loops=1)
                                 ->  Bitmap Index Scan on bugtask__date_incomplete__idx  (cost=0.00..318.86 rows=23584 width=0) (actual time=9.988..9.988 rows=23522 loops=1)
                                 ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..372.19 rows=23584 width=0) (actual time=12.606..12.606 rows=23522 loops=1)
                                       Index Cond: (status = 15)
                           ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..372.19 rows=23584 width=0) (actual time=10.452..10.452 rows=23522 loops=1)
                                 Index Cond: (status = 15)
                           ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..790.64 rows=50177 width=0) (actual time=13.935..13.935 rows=49263 loops=1)
                                 Index Cond: (status = 20)
                           ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..394.34 rows=24937 width=0) (actual time=7.999..7.999 rows=25635 loops=1)
                                 Index Cond: (status = 21)
                           ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..66.40 rows=4012 width=0) (actual time=1.542..1.542 rows=4483 loops=1)
                                 Index Cond: (status = 22)
                           ->  Bitmap Index Scan on bugtask__status__idx  (cost=0.00..182.91 rows=11547 width=0) (actual time=3.682..3.682 rows=11332 loops=1)
                                 Index Cond: (status = 25)
               ->  Append  (cost=0.00..462438.12 rows=18054934 width=4) (actual time=0.452..1119.537 rows=17813 loops=2326)
                     ->  Nested Loop  (cost=0.00..45118.08 rows=10441 width=4) (actual time=0.450..0.517 rows=7 loops=2326)
                           ->  Index Scan using structuralsubscription__subscriber__idx on structuralsubscription  (cost=0.00..302.37 rows=169 width=4) (actual time=0.010..0.158 rows=169 loops=2326)
                                 Index Cond: (subscriber = 458651)
                           ->  Index Scan using bugtask__product__bug__key on bugtask  (cost=0.00..263.57 rows=129 width=8) (actual time=0.001..0.001 rows=0 loops=393094)
                                 Index Cond: (public.bugtask.product = public.structuralsubscription.product)
                     ->  Nested Loop  (cost=4.56..2009.35 rows=6799 width=4) (actual time=0.059..58.330 rows=17806 loops=2326)
                           ->  Bitmap Heap Scan on structuralsubscription  (cost=4.56..176.14 rows=169 width=8) (actual time=0.036..0.246 rows=163 loops=2326)
                                 Recheck Cond: (subscriber = 458651)
                                 ->  Bitmap Index Scan on structuralsubscription__subscriber__idx  (cost=0.00..4.52 rows=169 width=0) (actual time=0.030..0.030 rows=169 loops=2326)
                                       Index Cond: (subscriber = 458651)
                           ->  Index Scan using bugtask__distribution__sourcepackagename__idx on bugtask  (cost=0.00..10.79 rows=4 width=12) (actual time=0.010..0.226 rows=110 loops=378158)
                                 Index Cond: ((public.bugtask.distribution = public.structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = public.structuralsubscription.sourcepackagename))
                     ->  Hash Join  (cost=46814.38..62053.29 rows=1123 width=4) (actual time=1159.873..1159.873 rows=0 loops=2086)
                           Hash Cond: (public.structuralsubscription.distroseries = public.bugtask.distroseries)
                           ->  Bitmap Heap Scan on structuralsubscription  (cost=4.56..176.14 rows=169 width=4) (actual time=0.054..0.267 rows=169 loops=2086)
                                 Recheck Cond: (subscriber = 458651)
                                 ->  Bitmap Index Scan on structuralsubscription__subscriber__idx  (cost=0.00..4.52 rows=169 width=0) (actual time=0.044..0.044 rows=169 loops=2086)
                                       Index Cond: (subscriber = 458651)
                           ->  Hash  (cost=35328.14..35328.14 rows=699814 width=8) (actual time=1159.039..1159.039 rows=14744 loops=2086)
                                 ->  Seq Scan on bugtask  (cost=0.00..35328.14 rows=699814 width=8) (actual time=0.010..755.495 rows=699823 loops=2086)
                     ->  Nested Loop  (cost=4.56..10721.41 rows=141 width=4) (actual time=0.554..0.554 rows=0 loops=2086)
                           ->  Bitmap Heap Scan on structuralsubscription  (cost=4.56..176.14 rows=169 width=4) (actual time=0.066..0.227 rows=169 loops=2086)
                                 Recheck Cond: (subscriber = 458651)
                                 ->  Bitmap Index Scan on structuralsubscription__subscriber__idx  (cost=0.00..4.52 rows=169 width=0) (actual time=0.055..0.055 rows=169 loops=2086)
                                       Index Cond: (subscriber = 458651)
                           ->  Index Scan using bugtask__milestone__idx on bugtask  (cost=0.00..61.84 rows=45 width=8) (actual time=0.001..0.001 rows=0 loops=352534)
                                 Index Cond: (public.bugtask.milestone = public.structuralsubscription.milestone)
                     ->  Nested Loop  (cost=0.00..16671.55 rows=55 width=4) (actual time=0.482..0.482 rows=0 loops=2086)
                           ->  Index Scan using structuralsubscription__subscriber__idx on structuralsubscription  (cost=0.00..302.37 rows=169 width=4) (actual time=0.011..0.165 rows=169 loops=2086)
                                 Index Cond: (subscriber = 458651)

->  Index Scan using bugtask__productseries__bug__key on bugtask  (cost=0.00..96.38 rows=38 width=8) (actual time=0.001..0.001 rows=0 loops=352534)
                                 Index Cond: (public.bugtask.productseries = public.structuralsubscription.productseries)
                     ->  Nested Loop  (cost=2756.92..3446.91 rows=149 width=4) (actual time=0.299..0.299 rows=0 loops=2086)
                           ->  Hash Join  (cost=2756.92..3325.17 rows=16 width=4) (actual time=0.297..0.297 rows=0 loops=2086)
                                 Hash Cond: (public.structuralsubscription.project = product.project)
                                 ->  Bitmap Heap Scan on structuralsubscription  (cost=4.56..176.14 rows=169 width=4) (actual time=0.038..0.175 rows=169 loops=2086)
                                       Recheck Cond: (subscriber = 458651)
                                       ->  Bitmap Index Scan on structuralsubscription__subscriber__idx  (cost=0.00..4.52 rows=169 width=0) (actual time=0.031..0.031 rows=169 loops=2086)
                                             Index Cond: (subscriber = 458651)
                                 ->  Hash  (cost=2439.38..2439.38 rows=25038 width=8) (actual time=37.811..37.811 rows=2390 loops=1)
                                       ->  Seq Scan on product  (cost=0.00..2439.38 rows=25038 width=8) (actual time=0.008..22.634 rows=25042 loops=1)
                           ->  Index Scan using bugtask__product__bug__key on bugtask  (cost=0.00..6.00 rows=129 width=8) (never executed)
                                 Index Cond: (public.bugtask.product = product.id)
                     ->  Nested Loop  (cost=270.66..141868.20 rows=18036226 width=4) (actual time=0.087..0.087 rows=0 loops=2086)
                           ->  Index Scan using structuralsubscription__subscriber__idx on structuralsubscription  (cost=0.00..302.37 rows=76 width=4) (actual time=0.065..0.074 rows=1 loops=2086)
                                 Index Cond: (subscriber = 458651)
                                 Filter: (sourcepackagename IS NULL)
                           ->  Bitmap Heap Scan on bugtask  (cost=270.66..1552.35 rows=24829 width=8) (actual time=0.003..0.003 rows=0 loops=2086)
                                 Recheck Cond: (public.bugtask.distribution = public.structuralsubscription.distribution)
                                 ->  Bitmap Index Scan on bugtask__distribution__sourcepackagename__idx  (cost=0.00..264.45 rows=24829 width=0) (actual time=0.001..0.001 rows=0 loops=2086)
                                       Index Cond: (public.bugtask.distribution = public.structuralsubscription.distribution)
         ->  Index Scan using bug_pkey on bug  (cost=0.00..22.72 rows=1 width=12) (actual time=0.017..0.018 rows=0 loops=240)
               Index Cond: (bug.id = public.bugtask.bug)
               Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (subplan)))
               SubPlan
                 ->  Hash Join  (cost=9.09..18.37 rows=1 width=4) (never executed)
                       Hash Cond: (bugsubscription.person = teamparticipation.team)
                       ->  Index Scan using bugsubscription_bug_idx on bugsubscription  (cost=0.00..9.24 rows=7 width=8) (never executed)
                             Index Cond: (bug = $0)
                       ->  Hash  (cost=9.06..9.06 rows=2 width=4) (never executed)
                             ->  Index Scan using teamparticipation_person_idx on teamparticipation  (cost=0.00..9.06 rows=2 width=4) (never executed)
                                   Index Cond: (person = 972620)
 Total runtime: 2629964.147 ms
(89 rows)

Revision history for this message

Robert Collins (lifeless) wrote on 2010-07-25:

#4

This may well also be another overly precise case.

tags:

added: overlyprecise

Revision history for this message

Stuart Bishop (stub) wrote on 2010-07-29:

#5

The following query should be the same and performs in <1s:

SELECT COUNT(DISTINCT BugTask.id)
FROM Bug
JOIN BugTask ON Bug.id = BugTask.bug
LEFT OUTER JOIN Product ON BugTask.product = Product.id
JOIN StructuralSubscription ON (
    BugTask.product = StructuralSubscription.product
    OR (
        BugTask.distribution = StructuralSubscription.distribution
        AND (
            BugTask.sourcepackagename IS NULL
            OR BugTask.sourcepackagename
                = StructuralSubscription.sourcepackagename
            )
        )
    OR BugTask.distroseries = StructuralSubscription.distroseries
    OR BugTask.milestone = StructuralSubscription.milestone
    OR BugTask.productseries = StructuralSubscription.productseries
    OR Product.project = StructuralSubscription.project
    )
WHERE
    BugTask.importance = 40
    AND BugTask.distribution = 1
    AND (
        (BugTask.status = 10)
        OR (BugTask.status = 15) AND (
            Bug.date_last_message IS NOT NULL
            AND BugTask.date_incomplete <= Bug.date_last_message
            )
        OR (BugTask.status = 15) AND (
            Bug.date_last_message IS NULL
            OR BugTask.date_incomplete > Bug.date_last_message)
        OR (BugTask.status = 20) OR (BugTask.status = 21)
        OR (BugTask.status = 22) OR (BugTask.status = 25)
        )
    AND Bug.duplicateof is NULL
    AND BugTask.distroseries is NULL
    AND BugTask.productseries is NULL
    AND StructuralSubscription.subscriber = 458651
    AND (Bug.private = FALSE OR EXISTS (
        SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation
        WHERE
            TeamParticipation.person = 972620
            AND BugSubscription.person = TeamParticipation.team
            AND BugSubscription.bug = Bug.id
        ))

It is still horrible though - the data model makes joining BugTask with StructuralSubscription problematic. I suspect we need to define a BugTarget (or just Target) and link StructuralSubscription and BugTask to that instead of (distribution OR distroseries OR (distribution AND sourcepackagename) OR Project OR Product OR Milestone OR productseries).

The following query should be the same and performs in <1s:

SELECT COUNT(DISTINCT BugTask.id)
FROM Bug
JOIN BugTask ON Bug.id = BugTask.bug
LEFT OUTER JOIN Product ON BugTask.product = Product.id
JOIN StructuralSubscription ON (
    BugTask.product = StructuralSubscription.product
    OR (
        BugTask.distribution = StructuralSubscription.distribution
        AND (
            BugTask.sourcepackagename IS NULL
            OR BugTask.sourcepackagename
                = StructuralSubscription.sourcepackagename
            )
        )
    OR BugTask.distroseries = StructuralSubscription.distroseries
    OR BugTask.milestone = StructuralSubscription.milestone
    OR BugTask.productseries = StructuralSubscription.productseries
    OR Product.project = StructuralSubscription.project
    )
WHERE
    BugTask.importance = 40
    AND BugTask.distribution = 1
    AND (
        (BugTask.status = 10)
        OR (BugTask.status = 15) AND (
            Bug.date_last_message IS NOT NULL
            AND BugTask.date_incomplete <= Bug.date_last_message
            )
        OR (BugTask.status = 15) AND (
            Bug.date_last_message IS NULL
            OR BugTask.date_incomplete > Bug.date_last_message)
        OR (BugTask.status = 20) OR (BugTask.status = 21)
        OR (BugTask.status = 22) OR (BugTask.status = 25)
        )
    AND Bug.duplicateof is NULL
    AND BugTask.distroseries is NULL
    AND BugTask.productseries is NULL
    AND StructuralSubscription.subscriber = 458651
    AND (Bug.private = FALSE OR EXISTS (
        SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation
        WHERE
            TeamParticipation.person = 972620
            AND BugSubscription.person = TeamParticipation.team
            AND BugSubscription.bug = Bug.id
        ))

It is still horrible though - the data model makes joining BugTask with StructuralSubscription problematic. I suspect we need to define a BugTarget (or just Target) and link StructuralSubscription and BugTask to that instead of (distribution OR distroseries OR (distribution AND sourcepackagename) OR Project OR Product OR Milestone OR productseries).

Revision history for this message

Robert Collins (lifeless) wrote on 2010-09-18:

#6

I agree. Still, we should do this fix, its trivial.

Deryck Hodge (deryck) on 2010-09-24

Changed in malone:
status:	Triaged → In Progress
assignee:	nobody → Abel Deuring (adeuring)

Robert Collins (lifeless) on 2010-10-14

tags:

added: pg83

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2010-10-25: Bug fixed by a commit

#7

Fixed in stable r11782 (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11782) by a commit, but not testable.

Changed in malone:
milestone:	none → 10.11
tags:	added: qa-untestable
Changed in malone:
status:	In Progress → Fix Committed

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2010-10-25:

#8

Fixed in stable r11788 (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11788) by a commit, but not testable.

Abel Deuring (adeuring) on 2010-10-26

Changed in malone:
status:	Fix Committed → In Progress
tags:	removed: qa-untestable

Revision history for this message

Robert Collins (lifeless) wrote on 2010-10-28:

#9

Please don't untag landed incremental work like this *until* it has been deployed.

tags:

added: qa-untestable

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2010-11-04:

#10

Fixed in stable r11843 (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11843) by a commit, but not testable.

Changed in malone:
status:	In Progress → Fix Committed

Revision history for this message

Abel Deuring (adeuring) wrote on 2010-11-04:

#12

still in progress...

Changed in malone:
status:	Fix Committed → In Progress

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2010-11-05: Incremental fix

#13

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

Abel Deuring (adeuring) on 2010-11-22

Changed in malone:
status:	In Progress → Fix Committed

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2010-11-22: Bug fixed by a commit

#14

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

Changed in malone:
milestone:	10.11 → 10.12
tags:	added: qa-needstesting removed: qa-untestable

Abel Deuring (adeuring) on 2010-11-22

tags:

added: qa-ok
removed: qa-needstesting

Robert Collins (lifeless) on 2010-11-23

Changed in malone:
status:	Fix Committed → Fix Released

Launchpad itself

searchTasks with structural_subscriber times out regularly

Bug Description

Related branches

Other bug subscribers

Remote bug watches