Switching to staging, for cold cache performance - because recursive estimates are probably not trustworthy...


explain analyze WITH RECURSIVE mp_revs AS (
    select sequence as sequence, revision as revision from branchrevision where branch=492361 and sequence = (select max(sequence) from branchrevision where branch=492361)
  UNION
   select branchrevision.sequence, branchrevision.revision from mp_revs, branchrevision, revision where branchrevision.branch=492361 and branchrevision.revision=revision.id and branchrevision.sequence=mp_revs.sequence-1 and Revision.revision_date >= '2009-08-10 21:56:30.623339+00:00'
)
SELECT sequence, revision FROM mp_revs;
                                                                                                   QUERY PLAN                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on mp_revs  (cost=13155.48..13155.90 rows=21 width=8) (actual time=1979.814..25504.341 rows=4180 loops=1)
   CTE mp_revs
     ->  Recursive Union  (cost=1.41..13155.48 rows=21 width=8) (actual time=1979.810..25497.654 rows=4180 loops=1)
           ->  Index Scan using revisionnumber_branch_sequence_unique on branchrevision  (cost=1.41..130.23 rows=1 width=8) (actual time=1979.791..1979.793 rows=1 loops=1)
                 Index Cond: ((branch = 492361) AND (sequence = $2))
                 InitPlan 2 (returns $2)
                   ->  Result  (cost=1.40..1.41 rows=1 width=0) (actual time=1976.047..1976.048 rows=1 loops=1)
                         InitPlan 1 (returns $1)
                           ->  Limit  (cost=0.00..1.40 rows=1 width=4) (actual time=1976.037..1976.038 rows=1 loops=1)
                                 ->  Index Scan Backward using revisionnumber_branch_sequence_unique on branchrevision  (cost=0.00..30513.07 rows=21781 width=4) (actual time=1976.034..1976.034 rows=1 loops=1)
                                       Index Cond: (branch = 492361)
                                       Filter: (sequence IS NOT NULL)
           ->  Nested Loop  (cost=0.00..1302.48 rows=2 width=8) (actual time=5.606..5.621 rows=1 loops=4180)
                 ->  Nested Loop  (cost=0.00..1288.59 rows=2 width=8) (actual time=0.023..0.025 rows=1 loops=4180)
                       ->  WorkTable Scan on mp_revs  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.001 rows=1 loops=4180)
                       ->  Index Scan using revisionnumber_branch_sequence_unique on branchrevision  (cost=0.00..128.82 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=4180)
                             Index Cond: ((public.branchrevision.branch = 492361) AND (public.branchrevision.sequence = (mp_revs.sequence - 1)))
                 ->  Index Scan using changeset_pkey on revision  (cost=0.00..6.94 rows=1 width=4) (actual time=5.581..5.593 rows=1 loops=4180)
                       Index Cond: (revision.id = public.branchrevision.revision)
                       Filter: (revision.revision_date >= '2009-08-10 21:56:30.623339'::timestamp without time zone)
 Total runtime: 25507.406 ms
(21 rows)

so - about 6ms per rev cold cache.

This seems substantially better (but still not -awesome-). It has the nice property of not needing schema changes.