BranchMergeProposal:+index timeout

                                                                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (cost=58877.11..58886.70 rows=3837 width=274) (actual time=4208.466..4208.468 rows=3 loops=1)
   Sort Key: branchrevision.sequence
   Sort Method: quicksort Memory: 25kB
   -> Hash Join (cost=3205.68..58648.70 rows=3837 width=274) (actual time=4077.193..4208.380 rows=3 loops=1)
         Hash Cond: (revision.revision_author = revisionauthor.id)
         -> Nested Loop (cost=0.00..55366.28 rows=3837 width=216) (actual time=3865.325..3996.500 rows=3 loops=1)
               -> Index Scan using revisionnumber_branch_sequence_unique on branchrevision (cost=0.00..27457.21 rows=3837 width=12) (actual time=52.879..728.944 rows=10020 loops=1)
                     Index Cond: (branch = 294720)
                     Filter: (sequence IS NOT NULL)
               -> Index Scan using changeset_pkey on revision (cost=0.00..7.26 rows=1 width=204) (actual time=0.325..0.325 rows=0 loops=10020)
                     Index Cond: (revision.id = branchrevision.revision)
                     Filter: ((revision.revision_date >= '2009-12-16 13:25:11.315617'::timestamp without time zone) AND (revision.revision_date <= '2009-12-21 12:24:33.529665'::timestamp without time zone))
         -> Hash (cost=1974.19..1974.19 rows=98519 width=58) (actual time=211.528..211.528 rows=98519 loops=1)
               -> Seq Scan on revisionauthor (cost=0.00..1974.19 rows=98519 width=58) (actual time=0.027..81.535 rows=98519 loops=1)
 Total runtime: 4211.886 ms

SELECT BranchRevision.branch,
       BranchRevision.revision,
       BranchRevision.sequence,
       Revision.date_created,
       Revision.gpgkey,
       Revision.id,
       Revision.karma_allocated,
       Revision.log_body,
       Revision.revision_author,
       Revision.revision_date,
       Revision.revision_id
FROM BranchRevision,
     Revision
WHERE BranchRevision.branch = 294720
  AND BranchRevision.sequence IS NOT NULL
  AND BranchRevision.revision = Revision.id
  AND Revision.revision_date >= '2009-12-16 13:25:11.315617+00:00'
  AND Revision.revision_date <= '2009-12-21 12:24:33.529665+00:00'
ORDER BY BranchRevision.sequence;
->

 Nested Loop (cost=0.00..55366.28 rows=3837 width=216) (actual time=110.288..141.136 rows=3 loops=1)
   -> Index Scan using revisionnumber_branch_sequence_unique on branchrevision (cost=0.00..27457.21 rows=3837 width=12) (actual time=0.045..47.326 rows=10020 loops=1)
         Index Cond: (branch = 294720)
         Filter: (sequence IS NOT NULL)
   -> Index Scan using changeset_pkey on revision (cost=0.00..7.26 rows=1 width=204) (actual time=0.009..0.009 rows=0 loops=10020)
         Index Cond: (revision.id = branchrevision.revision)
         Filter: ((revision.revision_date >= '2009-12-16 13:25:11.315617'::timestamp without time zone) AND (revision.revision_date <= '2009-12-21 12:24:33.529665'::timestamp without time zone))
 Total runtime: 141.217 ms

and 1ms to get the authors (all 1) from the revisionauthor table

Will get a prod timing run done.

New plan runs in 900ms 'cold' on prod, which is a lot happier than the 9000ms in the OOPS.

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