timeout PersonSet:CollectionResource:#people:findTeam

[Curtis Hovey]

The query times out trying to match ambiguous or very common names, for example, "ubuntu-" or "r-"

PATH_INFO: /1.0/people
QUERY_STRING: text=%22ubuntu-%22&ws.op=findTeam&ws.size=75&memo=75&ws.start=75
memo: 75
text: "ubuntu-"
ws.size: 75
ws.start: 75

These are the two slow queries, note that we get the count, then get the data :(

12. 113 1162ms SQL-main-master
SELECT *
FROM (
        (SELECT Person.description,
                Person.account,
                Person.creation_comment,
                Person.creation_rationale,
                Person.datecreated,
                Person.defaultmembershipperiod,
                Person.defaultrenewalperiod,
                Person.displayname,
                Person.hide_email_addresses,
                Person.homepage_content,
                Person.icon,
                Person.id,
                Person.logo,
                Person.mailing_list_auto_subscribe_policy,
                Person.subscriptionpolicy,
                Person.merged,
                Person.mugshot,
                Person.name,
                Person.personal_standing,
                Person.personal_standing_reason,
                Person.registrant,
                Person.renewal_policy,
                Person.teamdescription,
                Person.teamowner,
                Person.verbose_bugnotifications,
                Person.visibility
         FROM EmailAddress,
              Person,
              TeamParticipation
         WHERE ((Person.visibility = 1)
                OR ( TeamParticipation.person = 28488
                    AND Person.teamowner IS NOT NULL
                    AND Person.visibility != 1 ))
           AND TeamParticipation.team = Person.id
           AND NOT (Person.teamowner IS NULL)
           AND Person.merged IS NULL
           AND EmailAddress.person = Person.id
           AND LOWER(EmailAddress.email) LIKE u'ubuntu-%' ESCAPE u'!')
      UNION
        (SELECT Person.description,
                Person.account,
                Person.creation_comment,
                Person.creation_rationale,
                Person.datecreated,
                Person.defaultmembershipperiod,
                Person.defaultrenewalperiod,
                Person.displayname,
                Person.hide_email_addresses,
                Person.homepage_content,
                Person.icon,
                Person.id,
                Person.logo,
                Person.mailing_list_auto_subscribe_policy,
                Person.subscriptionpolicy,
                Person.merged,
                Person.mugshot,
                Person.name,
                Person.personal_standing,
                Person.personal_standing_reason,
                Person.registrant,
                Person.renewal_policy,
                Person.teamdescription,
                Person.teamowner,
                Person.verbose_bugnotifications,
                Person.visibility
         FROM Person,
              TeamParticipation
         WHERE ((Person.visibility = 1)
                OR ( TeamParticipation.person = 28488
                    AND Person.teamowner IS NOT NULL
                    AND Person.visibility != 1 ))
           AND TeamParticipation.team = Person.id
           AND NOT (Person.teamowner IS NULL)
           AND Person.merged IS NULL
           AND (Person.fti @@ ftq(u'ubuntu-')))) AS "_1881"
ORDER BY person_sort_key(displayname, name) LIMIT 76
OFFSET 75

13. 2035 1170ms SQL-main-master
SELECT COUNT(*)
FROM (
        (SELECT Person.description,
                Person.account,
                Person.creation_comment,
                Person.creation_rationale,
                Person.datecreated,
                Person.defaultmembershipperiod,
                Person.defaultrenewalperiod,
                Person.displayname,
                Person.hide_email_addresses,
                Person.homepage_content,
                Person.icon,
                Person.id,
                Person.logo,
                Person.mailing_list_auto_subscribe_policy,
                Person.subscriptionpolicy,
                Person.merged,
                Person.mugshot,
                Person.name,
                Person.personal_standing,
                Person.personal_standing_reason,
                Person.registrant,
                Person.renewal_policy,
                Person.teamdescription,
                Person.teamowner,
                Person.verbose_bugnotifications,
                Person.visibility
         FROM EmailAddress,
              Person,
              TeamParticipation
         WHERE ((Person.visibility = 1)
                OR ( TeamParticipation.person = 28488
                    AND Person.teamowner IS NOT NULL
                    AND Person.visibility != 1 ))
           AND TeamParticipation.team = Person.id
           AND NOT (Person.teamowner IS NULL)
           AND Person.merged IS NULL
           AND EmailAddress.person = Person.id
           AND LOWER(EmailAddress.email) LIKE u'ubuntu-%' ESCAPE u'!')
      UNION
        (SELECT Person.description,
                Person.account,
                Person.creation_comment,
                Person.creation_rationale,
                Person.datecreated,
                Person.defaultmembershipperiod,
                Person.defaultrenewalperiod,
                Person.displayname,
                Person.hide_email_addresses,
                Person.homepage_content,
                Person.icon,
                Person.id,
                Person.logo,
                Person.mailing_list_auto_subscribe_policy,
                Person.subscriptionpolicy,
                Person.merged,
                Person.mugshot,
                Person.name,
                Person.personal_standing,
                Person.personal_standing_reason,
                Person.registrant,
                Person.renewal_policy,
                Person.teamdescription,
                Person.teamowner,
                Person.verbose_bugnotifications,
                Person.visibility
         FROM Person,
              TeamParticipation
         WHERE ((Person.visibility = 1)
                OR ( TeamParticipation.person = 28488
                    AND Person.teamowner IS NOT NULL
                    AND Person.visibility != 1 ))
           AND TeamParticipation.team = Person.id
           AND NOT (Person.teamowner IS NULL)
           AND Person.merged IS NULL
           AND (Person.fti @@ ftq(u'ubuntu-')))) AS "_tmp"

[Launchpad QA Bot (lpqabot)]

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