launchpad-dev team mailing list archive

[Ian Booth <ian.booth@xxxxxxxxxxxxx>]

Hi

Let me rephrase the question - for a simple count of owned branches, I 
can't see a need to do all the stuff shown in the queries on the timeout 
oops. I mean, selecting the entire column set from the query table(s) 
just to do a count(*) on the rows returned seems pretty dumb, and I'd be 
surprised if there weren't performance issues like those we are seeing 
on large result sets.

So, I still wonder whether
a)
  select count(*)  from branch
  where branch.owner in (
  select team from teamparticipation where person = %d)
is really that expensive, and
b) if we can do that query efficiently, can we not use that to display 
the count of at least owned branches?

If the above holds true, then perhaps even counting registered or 
subscribed branches would be possible using the same idea?

I realise that a big part of the issue is using BranchCollection which 
is really designed to load the visibile branches rather than just count 
them. But perhaps we can bypass that class for counting simple things 
like owned branches?

On 08/11/11 22:22, Raphaël Badin wrote:
> > Hi Raphaël
> >
> > Speaking off the top of my head, not looking at the query or code etc:
> >
> > I'm wondering if "owned branches" for example is really that difficult
> > to compute? select count(*) from branch where owner = ?
>
> For ~ubuntu-branches, this query takes ~1 sec.
>
> Unfortunately, the expensive part is not the joins or anything complex
> but simply the Seq Scan generated by requesting all the public branches
> for ~ubuntu-branches.  This fetches ~300K rows (i.e. a significant
> proportion of the whole table) and thus the Seq Scan is the right thing
> to do; but it's heavy.
>
> Please have a look at this MP if you want details:
> https://code.launchpad.net/~rvb/launchpad/branches-timeout-bug-827935/+merge/80875
>
> (if you want more details:
> https://code.launchpad.net/~rvb/launchpad/branches-timeout-bug-827935-always-display/+merge/81022
> https://code.launchpad.net/~rvb/launchpad/branches-timeout-bug-827935-3/+merge/81262
> https://code.launchpad.net/~rvb/launchpad/branches-timeout-bug-827935-4/+merge/81290)