launchpad-reviewers team mailing list archive

[Deryck Hodge <deryck.hodge@xxxxxxxxxxxxx>]

There is no observable difference in the two queries.


SELECT * from Specification WHERE
    Specification.owner = 2
    OR Specification.assignee = 2
    OR Specification.drafter = 2
    OR Specification.approver = 2
    OR Specification.id IN
    (
        SELECT SpecificationSubscription.specification
            FROM SpecificationSubscription
            WHERE SpecificationSubscription.person = 2
    )
    AND (
        NOT (Specification.implementation_status IN (0, 5, 90, 95))
        OR Specification.implementation_status = 60
        AND Specification.definition_status = 10
    ) AND (
        Specification.product IS NULL
        OR NOT Specification.product IN (
            SELECT Product.id FROM Product WHERE Product.active = true)
    ) AND (
        NOT (Specification.implementation_status = 0
        OR Specification.definition_status IN (90, 95)
        OR Specification.implementation_status = 60
        AND Specification.definition_status = 10)
    );

Total runtime: 155.522 ms

SELECT * from Specification WHERE
    Specification.owner = 2
    OR Specification.assignee = 2
    OR Specification.drafter = 2
    OR Specification.approver = 2
    OR Specification.id IN
    (
        SELECT SpecificationSubscription.specification
            FROM SpecificationSubscription
            WHERE SpecificationSubscription.person = 2
    )
    AND (
        Specification.information_type IN (NULL, 1)
        OR Specification.id IN (
            SELECT Specification.id FROM Specification
                JOIN AccessPolicy ON (
                    Specification.product = AccessPolicy.product
                    OR Specification.distribution = AccessPolicy.distribution
                )
                AND Specification.information_type = AccessPolicy.type
                    JOIN AccessPolicyGrantFlat ON
                    AccessPolicy.id = AccessPolicyGrantFlat.policy
                    LEFT JOIN AccessArtifact
                        ON AccessPolicyGrantFlat.artifact = AccessArtifact.id
                    JOIN TeamParticipation
                        ON TeamParticipation.team = AccessPolicyGrantFlat.grantee
                    AND TeamParticipation.person = 2
                    WHERE AccessPolicyGrantFlat.artifact IS NULL
                    OR AccessArtifact.specification = Specification.id
        )
    )
    AND (
        NOT (Specification.implementation_status IN (0, 5, 90, 95))
        OR Specification.implementation_status = 60
        AND Specification.definition_status = 10
    ) AND (
        Specification.product IS NULL
        OR NOT Specification.product IN (
            SELECT Product.id FROM Product WHERE Product.active = true)
    ) AND (
        NOT (Specification.implementation_status = 0
        OR Specification.definition_status IN (90, 95)
        OR Specification.implementation_status = 60
        AND Specification.definition_status = 10)
    );

Total runtime: 155.263 ms

-- 
https://code.launchpad.net/~abentley/launchpad/person-assigned-specs-in-progress/+merge/130888
Your team Launchpad code reviewers is subscribed to branch lp:launchpad.