Branch:+index timeouts

[Martin Pool]

https://code.launchpad.net/~launchpad-pqm/launchpad/devel

OOPS-1952N252

116. 828 8931ms SQL-launchpad-main-slave
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 = 24637
  AND Revision.id = BranchRevision.revision
  AND BranchRevision.sequence IS NOT NULL
ORDER BY BranchRevision.sequence DESC LIMIT 10

[Robert Collins (lifeless)]

We're rebuilding a replica at the moment, increasing cache pressure substantially.

Cold on qastaging:
                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..144.25 rows=10 width=216) (actual time=17186.667..17257.529 rows=10 loops=1)
   -> Nested Loop (cost=0.00..55348.62 rows=3837 width=216) (actual time=17186.666..17257.525 rows=10 loops=1)
         -> Index Scan Backward using revisionnumber_branch_sequence_unique on branchrevision (cost=0.00..27458.62 rows=3837 width=12) (actual time=17180.528..17180.550 rows=10 loops=1)
               Index Cond: (branch = 24637)
               Filter: (sequence IS NOT NULL)
         -> Index Scan using changeset_pkey on revision (cost=0.00..7.26 rows=1 width=204) (actual time=7.694..7.695 rows=1 loops=10)
               Index Cond: (revision.id = branchrevision.revision)
 Total runtime: 17257.631 ms

revisionnumber_branch_sequence_unique" UNIQUE, btree (branch, sequence)
 - we probably want that to be partial on sequence is not null: most branches in bzr land are non-trunk to one trunk - so while its reporting that it only found 10 rows, it will have examined potentially hundreds.