launchpad-dev team mailing list archive

[Raphaël Badin <raphael.badin@xxxxxxxxxxxxx>]

Le 09/11/2011 08:12, Ian Booth a écrit :
> 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

Well, this is a more complex query than the one you suggest here but if
you take a look at the query/explain analyse below, you will see that
the expensive part is the Seq scan that owner=XXX and NOT
transitively_private generates.  Having looked at this with jtv and
lifeless it seems that there is now way to have this count query
performing well.

The query: https://pastebin.canonical.com/54885/
The EXPLAIN ANALYSE: https://pastebin.canonical.com/54886/