Person:+commentedbugs timeouts

[Robert Collins]

178 SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_close ... T ) ) AND Bug.private = FALSE ORDER BY BugTask.importance DESC, BugTask.id LIMIT $INT OFFSET $INT:
   GET: 178 Robots: 169 Local: 0
      4 https://bugs.launchpad.net/%7Ejcollins/+commentedbugs (Person:+commentedbugs)
       OOPS-1762D306, OOPS-1762F338, OOPS-1762M581, OOPS-1762N365
      4 https://bugs.launchpad.net/%7Emaco.m/+commentedbugs (Person:+commentedbugs)
       OOPS-1762A134, OOPS-1762D130, OOPS-1762F112, OOPS-1762H176
      4 https://bugs.launchpad.net/%7Embp/+commentedbugs (Person:+commentedbugs)
       OOPS-1762B1197, OOPS-1762H101, OOPS-1762K735, OOPS-1762L1903

OOPS-1762D306:
Branch: launchpad-rev-11756
Revno: 11756
SQL time: 16232 ms
Non-sql time: 214 ms
Total time: 16446 ms
Statement Count: 11
1 15000.0 1 SQL-launchpad-main-slave SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache, Product.answers_usage, Product.blueprints_usage, Product.owner, Product.translations_usage, Product.active, Product.autoupdate, Product.bug_reported_acknowledgement, Product.bug_reporting_guidelines, Product.bug_supervisor, Product.bugtracker, Product.date_next_suggest_packaging, Product.datecreated, Product.description, Product.development_focus, Product.displayname, Product.downloadurl, Product.driver, Product.enable_bug_expiration, Product.freshmeatproject, Product.homepage_content, Product.homepageurl, Product.icon, Product.id, Product.lastdoap, Product.license_approved, Product.license_info, Product.reviewed, Product.logo, Product.max_bug_heat, Product.mugshot, Product.name, Product.official_answers, Product.official_blueprints, Product.official_malone, Product.official_rosetta, Product.private_bugs, Product.programminglang, Product.project, Product.registrant, Product.remote_product, Product.reviewer_whiteboard, Product.screenshotsurl, Product.security_contact, Product.sourceforgeproject, Product.summary, Product.title, Product.translation_focus, Product.translationgroup, Product.translationpermission, Product.wikiurl, SourcePackageName.id, SourcePackageName.name, Bug.date_last_message, Bug.date_last_updated, Bug.date_made_private, Bug.datecreated, Bug.description, Bug.duplicateof, Bug.heat, Bug.heat_last_updated, Bug.id, Bug.latest_patch_uploaded, Bug.message_count, Bug.name, Bug.number_of_duplicates, Bug.owner, Bug.private, Bug.security_related, Bug.title, Bug.users_affected_count, Bug.users_unaffected_count, Bug.who_made_private FROM BugTask LEFT JOIN Bug ON BugTask.bug = Bug.id LEFT JOIN Product ON BugTask.product = Product.id LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE (1=1) AND Bug.id = BugTask.bug AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, BugMessage, Message WHERE Message.owner = 293289 AND Message.id = BugMessage.message AND BugTask.bug = BugMessage.bug AND Message.id NOT IN ( SELECT BugMessage.message FROM BugMessage WHERE BugMessage.bug = BugTask.bug ORDER BY BugMessage.id LIMIT 1 ) ) AND Bug.private = FALSE ORDER BY BugTask.importance DESC, BugTask.id LIMIT 76 OFFSET 0

[Stuart Bishop (stub)]

I don't think it is an 8.4 issue - I suspect this would perform just as badly under other releases with the right data. Things that stand out are an outer join where a normal join would do, and the IN clauses referencing tables in the outer scope causing them to be reevaluated many times and not scaling.

SELECT BugTask.*, Product.*, SourcePackageName.*, Bug.*
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id
LEFT JOIN Product ON BugTask.product = Product.id
LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE
    (1=1)
    AND Bug.id = BugTask.bug
    AND (
        (BugTask.status = 10)
        OR (BugTask.status = 15)
        OR (BugTask.status = 20)
        OR (BugTask.status = 21)
        OR (BugTask.status = 22)
        OR (BugTask.status = 25))
    AND Bug.duplicateof is NULL
    AND BugTask.id IN (
        SELECT BugTask.id FROM BugTask, BugMessage, Message
        WHERE
            Message.owner = 293289
            AND Message.id = BugMessage.message
            AND BugTask.bug = BugMessage.bug
            AND Message.id NOT IN (
                SELECT BugMessage.message FROM BugMessage
                WHERE BugMessage.bug = BugTask.bug
                ORDER BY BugMessage.id LIMIT 1 ) )
    AND Bug.private = FALSE
ORDER BY BugTask.importance DESC, BugTask.id LIMIT 76 OFFSET 0

[Stuart Bishop (stub)]

Actually, it might be triggered by 8.4. The following query performs well enough:

SELECT BugTask.*, Product.*, SourcePackageName.*, Bug.*
FROM BugTask
JOIN Bug ON BugTask.bug = Bug.id
LEFT JOIN Product ON BugTask.product = Product.id
LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE
    BugTask.status IN (10, 15, 20, 21, 22, 25)
    AND Bug.duplicateof is NULL
    AND Bug.private IS FALSE
    AND BugTask.id IN (
        SELECT DISTINCT BugTask.id
        FROM BugTask, BugMessage, Message
        WHERE
            Message.owner = 293289
            AND Message.id = BugMessage.message
            AND BugTask.bug = BugMessage.bug
            AND Message.id NOT IN (
                SELECT BugMessage.message FROM BugMessage
                WHERE BugMessage.bug = BugTask.bug
                ORDER BY BugMessage.id LIMIT 1 ) )
ORDER BY BugTask.importance DESC, BugTask.id LIMIT 76 OFFSET 0;

The differences here:

  - Use a normal join for Bug rather than a left outer join.
  - Add DISTINCT to the first IN subselect. Ideally the query planner would do this for us in cases where it helps and not hinders.

[Robert Collins (lifeless)]

You're talking about this bit specifically right?
SELECT BugMessage.message FROM BugMessage
               WHERE BugMessage.bug = BugTask.bug

being a correlated subquery with the next outer query ?
That looks like an awkward way to say 'filed by this user' anyhow.
'owner' is that person and it will be heaps faster.

[Robert Collins (lifeless)]

Or perhaps its trying to say 'commented by didn't file' in a clever way?

[Stuart Bishop (stub)]

On Tue, Nov 9, 2010 at 11:10 AM, Robert Collins
<email address hidden> wrote:
> Or perhaps its trying to say 'commented by didn't file' in a clever way?

I think so, yes. This part can be made much faster when
BugMessage.index is being populated.

--
Stuart Bishop <email address hidden>
http://www.stuartbishop.net/

[Robert Collins (lifeless)]

Just adding the DISTINCT cuts this to 226ms on staging.

[Robert Collins (lifeless)]

Has landed in devel; but buildbot just had a rosetta test failure, so that needs fixing before this will propogate to qastaging and be qable.

[Robert Collins (lifeless)]

 https://bugs.launchpad.net/%7Ejcollins/+commentedbugs works on qastaging.

[Launchpad QA Bot (lpqabot)]

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