launchpad-dev team mailing list archive

["Francis J. Lacoste" <francis.lacoste@xxxxxxxxxxxxx>]

Hi,

From my understanding COUNT(*) operation are as expensive as retrieving
the whole set anyway.

If we'd want to display counts, we'd probably need to go to a similar
pattern than we did for the bugs portlet -> maintain summary table.
Which is a lot of work, for probably something that isn't that important.



On 11-11-09 02:12 AM, Ian Booth wrote:
> 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)
>>
>>
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~launchpad-dev
> Post to     : launchpad-dev@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~launchpad-dev
> More help   : https://help.launchpad.net/ListHelp


-- 
Francis J. Lacoste
francis.lacoste@xxxxxxxxxxxxx

Attachment: signature.asc
Description: OpenPGP digital signature