I was doing scaling testing on bug 724033 and found the following query:
SELECT DISTINCT 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.merged, Person.mugshot, Person.name, Person.personal_standing, Person.personal_standing_reason, Person.registrant, Person.renewal_policy, Person.subscri
ptionpolicy, Person.teamdescription, Person.teamowner, Person.verbose_bugnotifications, Person.visibility FROM StructuralSubscription LEFT JOIN BugSubscriptionFilter ON BugSubscriptionFilter.structuralsubscription = StructuralSubscription.
id JOIN Person ON Person.id = StructuralSubscription.subscriber WHERE (BugSubscriptionFilter.id IN (SELECT BugSubscriptionFilter.id FROM StructuralSubscription JOIN BugSubscriptionFilter ON BugSubscriptionFilter.structuralsubscription = St
ructuralSubscription.id LEFT JOIN BugSubscriptionFilterStatus ON BugSubscriptionFilterStatus.filter = BugSubscriptionFilter.id LEFT JOIN BugSubscriptionFilterImportance ON BugSubscriptionFilterImportance.filter = BugSubscriptionFilter.id L
EFT JOIN BugSubscriptionFilterTag ON BugSubscriptionFilterTag.filter = BugSubscriptionFilter.id WHERE StructuralSubscription.id IN (1) AND ((StructuralSubscription.product = 4 OR StructuralSubscription.project = 4) AND (BugSubscriptionFilt
erImportance.importance = 20 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 130) AND (BugSubscriptionF
ilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 131) AND (BugSubscriptio
nFilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 132) AND (BugSubscript
ionFilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 133) AND (BugSubscri
ptionFilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 134) AND (BugSubsc
riptionFilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 135) AND (BugSub
scriptionFilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 136) AND (BugS
ubscriptionFilterImportance.importance = 5 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.product = 137) AND (Bu
....
NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.distribution = 1 AND StructuralSubscription.sourcepackagename = 1 OR StructuralSubscription.distribution = 1 AND StructuralSubscription.sourcepackagename IS NULL) AND (BugSubscriptionFilterImportance.importance = 30 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 10 OR BugSubscriptionFilterStatus.status IS NULL) OR (StructuralSubscription.distribution = 3 AND StructuralSubscription.sourcepackagename = 1 OR StructuralSubscription.distribution = 3 AND StructuralSubscription.sourcepackagename IS NULL) AND (BugSubscriptionFilterImportance.importance = 20 OR BugSubscriptionFilterImportance.importance IS NULL) AND (BugSubscriptionFilterStatus.status = 20 OR BugSubscriptionFilterStatus.status IS NULL)) AND NOT BugSubscriptionFilter.include_any_tags GROUP BY BugSubscriptionFilter.id HAVING COUNT((CASE WHEN BugSubscriptionFilterTag.include THEN BugSubscriptionFilterTag.tag END)) = 0) OR StructuralSubscription.id IN (1) AND BugSubscriptionFilter.id IS NULL)
The actual query is about 40 times longer.