launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

Diff comments:

> === modified file 'lib/lp/registry/model/person.py'
> --- lib/lp/registry/model/person.py	2017-04-22 13:09:01 +0000
> +++ lib/lp/registry/model/person.py	2017-06-06 08:18:21 +0000
> @@ -1434,6 +1434,32 @@
>          from lp.registry.model.distribution import Distribution
>          store = Store.of(self)
>          WorkItem = SpecificationWorkItem
> +
> +        # Since a workitem's assignee defaults to its specification's
> +        # assignee, the PostgreSQL planner isn't always able to work out
> +        # the selectivity of the filter. Put that in a CTE to force it
> +        # to calculate the workitems up front, rather than doing a hash
> +        # join over all of Specification and SpecificationWorkItem.
> +        assigned_specificationworkitem = With(
> +            'assigned_specificationworkitem',
> +            Union(
> +                Select(
> +                    SpecificationWorkItem.id,

I've dropped the alias.

> +                    where=And(
> +                        SpecificationWorkItem.assignee_id.is_in(
> +                            self.participant_ids),
> +                        Not(SpecificationWorkItem.deleted))),
> +                Select(
> +                    SpecificationWorkItem.id,
> +                    where=And(
> +                        SpecificationWorkItem.specification_id.is_in(
> +                            Select(
> +                                Specification.id,
> +                                where=Specification._assigneeID.is_in(
> +                                    self.participant_ids))),
> +                        Not(SpecificationWorkItem.deleted))),
> +                all=True))

UNION is an unnecessary set operation, and has been known to confuse the planner into performing unnecessary hashaggs etc. Despite being longer to type, UNION ALL is the basic operation, and we don't need more than that here.

> +
>          origin = [Specification]
>          productjoin, query = get_specification_active_product_filter(self)
>          origin.extend(productjoin)


-- 
https://code.launchpad.net/~wgrant/launchpad/upcoming-optimisation/+merge/325141
Your team Launchpad code reviewers is subscribed to branch lp:launchpad.