Builder:+history timeouts

Running that with builder 3 on staging - 51 rows in 22482ms

From staging:

 Limit (cost=148649.73..148651.84 rows=51 width=57) (actual time=23673.661..23675.791 rows=51 loops=1)
   -> Unique (cost=148649.73..153285.81 rows=112426 width=57) (actual time=23673.659..23675.778 rows=51 loops=1)
         -> Sort (cost=148649.73..149036.07 rows=154536 width=57) (actual time=23673.656..23674.758 rows=1606 loops=1)
               Sort Key: buildfarmjob.date_finished, buildfarmjob.id, buildfarmjob.date_created, buildfarmjob.date_first_dispatched, buildfarmjob.date_started, buildfarmjob.job_type, buildfarmjob.log, buildfarmjob.processor, buildfarmjob.status, buildfarmjob.virtualized
               Sort Method: external merge Disk: 171056kB
               -> Hash Left Join (cost=74869.48..143494.09 rows=154536 width=57) (actual time=609.450..3272.502 rows=2275497 loops=1)
                     Hash Cond: (packagebuild.archive = archive.id)
                     Filter: ((NOT COALESCE(archive.private, false)) OR (teamparticipation.person = 2))
                     -> Merge Right Join (cost=28166.54..89063.64 rows=56213 width=61) (actual time=339.722..2067.928 rows=53331 loops=1)
                           Merge Cond: (packagebuild.build_farm_job = buildfarmjob.id)
                           -> Index Scan using packagebuild__build_farm_job__idx on packagebuild (cost=0.00..55396.04 rows=1863177 width=8) (actual time=0.034..1292.351 rows=1863124 loops=1)
                           -> Sort (cost=28166.54..28307.08 rows=56213 width=57) (actual time=237.429..282.928 rows=53331 loops=1)
                                 Sort Key: buildfarmjob.id
                                 Sort Method: quicksort Memory: 9036kB
                                 -> Bitmap Heap Scan on buildfarmjob (cost=931.06..23731.73 rows=56213 width=57) (actual time=30.516..125.626 rows=53331 loops=1)
                                       Recheck Cond: (builder = 3)
                                       -> Bitmap Index Scan on buildfarmjob__builder_and_status__idx (cost=0.00..917.01 rows=56213 width=0) (actual time=22.007..22.007 rows=53331 loops=1)
                                             Index Cond: (builder = 3)
                     -> Hash (cost=45335.51..45335.51 rows=109394 width=9) (actual time=269.627..269.627 rows=49138 loops=1)
                           -> Nested Loop Left Join (cost=0.00..45335.51 rows=109394 width=9) (actual time=0.044..238.518 rows=49138 loops=1)
                                 -> Seq Scan on archive (cost=0.00..1490.99 rows=19899 width=9) (actual time=0.010..9.088 rows=19899 loops=1)
                                 -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..2.13 rows=6 width=8) (actual time=0.007..0.009 rows=2 loops=19899)
                                       Index Cond: (teamparticipation.team = archive.owner)
 Total runtime: 23744.002 ms
(24 rows)

This is a candidate rearrangement:

SELECT DISTINCT BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BuildFarmJob LEFT JOIN PackageBuild ON PackageBuild.build_farm_job = BuildFarmJob.id LEFT JOIN (Archive LEFT JOIN TeamParticipation ON (TeamParticipation.person = 2 AND TeamParticipation.team = Archive.owner)) ON (PackageBuild.archive = Archive.id AND (Archive.private = FALSE OR TeamParticipation.person = 2)) WHERE BuildFarmJob.builder = 3 ORDER BY BuildFarmJob.date_finished DESC, BuildFarmJob.id LIMIT 51 OFFSET 0;

SELECT DISTINCT BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BuildFarmJob LEFT JOIN PackageBuild ON PackageBuild.build_farm_job = BuildFarmJob.id
LEFT JOIN (Archive LEFT JOIN TeamParticipation ON (TeamParticipation.person = 2 AND TeamParticipation.team = Archive.owner and (archive.private=FALSE or TeamParticipation.person is not NULL))) ON PackageBuild.archive = Archive.id WHERE BuildFarmJob.builder = 3 ORDER BY BuildFarmJob.date_finished DESC, BuildFarmJob.id LIMIT 51 OFFSET 0;

is slightly faster still, but still not brilliant. (1400ms vs 1800ms on staging)

SELECT DISTINCT BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BuildFarmJob LEFT JOIN (PackageBuild
INNER JOIN (Archive INNER JOIN TeamParticipation ON (TeamParticipation.person = 2 AND TeamParticipation.team = Archive.owner and (archive.private=FALSE or TeamParticipation.person is not NULL))) ON PackageBuild.archive = Archive.id) ON PackageBuild.build_farm_job = BuildFarmJob.id WHERE BuildFarmJob.builder = 3 ORDER BY BuildFarmJob.date_finished DESC, BuildFarmJob.id LIMIT 51 OFFSET 0;

930ms - uses two inner joins rather than left joins.

On Monday 06 September 2010 01:56:28 Robert Collins wrote:
> -> Seq Scan on
> archive (cost=0.00..1490.99 rows=19899 width=9) (actual time=0.010..9.088
> rows=19899 loops=1)

Argh!

JTV came up with this which is massively quicker.

SELECT DISTINCT BuildFarmJob.*
FROM BuildFarmJob
LEFT JOIN PackageBuild ON PackageBuild.build_farm_job = BuildFarmJob.id
WHERE
    BuildFarmJob.builder = %s AND (
        PackageBuild.id IS NULL OR
        PackageBuild.archive IN (
            SELECT id
            FROM Archive
            WHERE Archive.private = FALSE

            UNION

            SELECT id
            FROM Archive
            WHERE
                Archive.private = TRUE AND
                Archive.owner IN (
                    SELECT DISTINCT team
                    FROM TeamParticipation
                    WHERE person = %s
                )
        )
    )
ORDER BY date_finished DESC, id

On dogfood, the old query took ~45 seconds, this new one is 5-6 seconds. On staging it's about 2 seconds.

I'm just curious, did you try the one I came up with that was < 1 second?

(its in comment 5)

On Thursday 10 February 2011 19:43:07 you wrote:
> I'm just curious, did you try the one I came up with that was < 1
> second?

Yeah it's fast but it also has a bug. :)

Also, the query is somewhat dynamic depending on who the user is.

On Fri, Feb 11, 2011 at 11:14 AM, Julian Edwards
<email address hidden> wrote:
> Also, the query is somewhat dynamic depending on who the user is.

Oh, whats the bug?

On Thursday 10 February 2011 23:14:32 you wrote:
> On Fri, Feb 11, 2011 at 11:14 AM, Julian Edwards
>
> <email address hidden> wrote:
> > Also, the query is somewhat dynamic depending on who the user is.
>
> Oh, whats the bug?

I'd have to dig again, it was something to do with exposing private builds to
the wrong people IIRC.

I'd love to know, I thought i had done correctness preserving transforms, I'd hate to introduce such a bug at a later time.

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