Person:+branches timeouts

so the timeout seems to be in

select branch ... ORDER BY Branch.lifecycle_status DESC, Branch.target_suffix ASC, Branch.owner_name ASC, Branch.name ASC LIMIT 48
OFFSET 313000

On Wed, Aug 17, 2011 at 11:03 PM, Martin Pool <email address hidden> wrote:
> so the timeout seems to be in
>
> select branch ... ORDER BY Branch.lifecycle_status DESC, Branch.target_suffix ASC, Branch.owner_name ASC, Branch.name ASC LIMIT 48
> OFFSET 313000

heh. So this is addressable soon with Abels up coming most excellent
work integrating the memo support in batchnav.

-Rob

Most of the time is indeed spent computing counts; Unfortunately I think Abel's work on StormRangeFactory cannot be applied to this because of the limitations it has (namely, that resultsets must be entire objects: simply adding range_factory = StormRangeFactory(results) fails to create a proper explain query: "EXPLAIN SELECT Branch.date_last_modified DESC, Branch.target [...]"). Maybe the query could be refactored to make it work but the way the query is built is rather complex.

I'll see if I can find a way to improve the queries themselves (for instance by caching the result of the subquery https://pastebin.canonical.com/54940/ which is repeated over and over).

I made a first attempt: http://paste.ubuntu.com/719759/. But testing the generated query on real data showed that this does not lead to any serious gain.

The heart of the problem seems to be here:
   -> Seq Scan on branch (cost=3507.31..57784.85 rows=223894 width=0) (actual time=0.029..1019.004 rows=336922 loops=1)
         Filter: (((NOT transitively_private) OR (hashed SubPlan 1)) AND (owner = 2866082) AND (lifecycle_status = ANY ('{10,30,50}'::integer[])))
(lines 4,5 of https://pastebin.canonical.com/54886/ [orig query https://pastebin.canonical.com/54885/])

jtv suggests splitting the problem into two to give a chance to the planner to do more clever choices.

Note that stormrangefactory can be improved to do non-objects, it just
wasn't needed for the first type abel was working on improving.

The diff I proposed in an earlier comment did improve the counting of the private branches (and since the subquery is repeated over and over this might buy us some perf) but the main problem is counting the public branches which can be many (336922 in this case).

The only solution left is the one Rob suggests: try to use stormrangefactory to avoid the counts altogether.

Thanks for fixing this: at the moment the udd roles have so many branches you can never get a full listing, and being able to see them will be helpful.

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

r14236 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/14236) is part of this bug's fix.

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

r14258 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/14258) is part of this bug's fix.

The new (simplified) menu has just been enabled in production.

https://code.launchpad.net/~ubuntu-branches is still heavy to generate but no timeout so far…

looks stable to me too. fixreleased?

Fix released indeed…

The new menu made a branch listing on another page oops. The FF has been removed for now… back to in progress…

The fix for the other bug has been released, the new menu is now enabled again.