searchTasks with structural_subscriber times out regularly

Bug #594247 reported by Brian Murray on 2010-06-14
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
High
Abel Deuring

Bug Description

It is possible to search for bug tasks using structural_subscriber as an argument for example:

xswat = launchpad.people['ubuntu-x-swat']
tasks = ubuntu.searchTasks(structural_subscriber=xswat)

and this works fine. However, if an additional parameter is added like importance it results in a OOPS.

tasks = ubuntu.searchTasks(structural_subscriber=xswat,importance='High')

https://lp-oops.canonical.com/oops.py/?oopsid=OOPS-1626EC2715

SQL time: 17078 ms
Non-sql time: 121 ms
Total time: 17199 ms
Statement Count: 21

SELECT COUNT(*) FROM Bug, BugTask WHERE Bug.id = BugTask.bug AND 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 BugTask.id IN ( SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.product = StructuralSubscription.product AND StructuralSubscription.subscriber = 458651 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 458651 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distroseries = StructuralSubscription.distroseries AND StructuralSubscription.subscriber = 458651 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.milestone = StructuralSubscription.milestone AND StructuralSubscription.subscriber = 458651 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.productseries = StructuralSubscription.productseries AND StructuralSubscription.subscriber = 458651 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription, Product WHERE BugTask.product = Product.id AND Product.project = StructuralSubscription.project AND StructuralSubscription.subscriber = 458651 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND StructuralSubscription.sourcepackagename 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)) AND (1=1)

Related branches

Brian Murray (brian-murray) wrote :

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
description: updated
Robert Collins (lifeless) wrote :

The query looks pretty silly at first glance :)

Robert Collins (lifeless) wrote :
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 ...

Robert Collins (lifeless) wrote :

This may well also be another overly precise case.

tags: added: overlyprecise
Stuart Bishop (stub) wrote :

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).

Robert Collins (lifeless) wrote :

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)
tags: added: pg83

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
Launchpad QA Bot (lpqabot) wrote :

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
Robert Collins (lifeless) wrote :

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

tags: added: qa-untestable
Launchpad QA Bot (lpqabot) wrote :

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
Abel Deuring (adeuring) wrote :

still in progress...

Changed in malone:
status: Fix Committed → In Progress
Abel Deuring (adeuring) on 2010-11-22
Changed in malone:
status: In Progress → Fix Committed
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
Changed in malone:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers