Comment 17 for bug 276950

with bpr as (select binarypackagerelease.id, binarypackagename from binarypackagerelease where binarypackagename in (151425,
                               151426, 67843, 67846, 112519,
                               151432, 96654, 112530, 151427,
                               175764, 85790, 85791, 77217,
                               77218, 77219, 77221, 77222,
                               77223, 77224, 81577, 134905,
                               151430, 73520, 71474, 73526,
                               73527, 175544, 100925, 175553,
                               160578, 160579, 88773, 81144,
                               104275, 151431, 88789, 120023,
                               86619, 86620, 67841, 67937,
                               67938, 81768, 84716, 175763,
                               119540, 182134, 182135, 146424,
                               146425, 126586, 67838, 133717))
select bpr.binarypackagename from
     bpr,
     BinaryPackagePublishingHistory
WHERE BinaryPackagePublishingHistory.binarypackagerelease =bpr.id
  AND BinaryPackagePublishingHistory.distroarchseries in ( 97, 98, 96, 99, 101, 102)
  AND BinaryPackagePublishingHistory.status IN (2, 1)
  AND BinaryPackagePublishingHistory.archive IN (1, 534);

about 800:

 Merge Join (cost=425359.81..427362.24 rows=63224 width=4) (actual time=1825.164..1891.782 rows=720 loops=1)
   Merge Cond: (bpr.id = binarypackagepublishinghistory.binarypackagerelease)
   CTE bpr
     -> Bitmap Heap Scan on binarypackagerelease (cost=1770.43..154514.79 rows=63224 width=8) (actual time=43.265..141.848 rows=67260 loops=1)
           Recheck Cond: (binarypackagename = ANY ('{151425,151426,67843,67846,112519,151432,96654,112530,151427,175764,85790,85791,77217,77218,77219,77221,77222,77223,77224,81577,134905,151430,73520,71474,73526,73527,175544,100925,175553,160578,160579,88773,81144,104275,151431,88789,120023,86619,86620,67841,67937,67938,81768,84716,175763,119540,182134,182135,146424,146425,126586,67838,133717}'::integer[]))
           -> Bitmap Index Scan on binarypackagerelease_binarypackagename_key (cost=0.00..1754.62 rows=63224 width=0) (actual time=30.468..30.468 rows=67260 loops=1)
                 Index Cond: (binarypackagename = ANY ('{151425,151426,67843,67846,112519,151432,96654,112530,151427,175764,85790,85791,77217,77218,77219,77221,77222,77223,77224,81577,134905,151430,73520,71474,73526,73527,175544,100925,175553,160578,160579,88773,81144,104275,151431,88789,120023,86619,86620,67841,67937,67938,81768,84716,175763,119540,182134,182135,146424,146425,126586,67838,133717}'::integer[]))
   -> Sort (cost=6306.02..6464.08 rows=63224 width=8) (actual time=280.025..302.738 rows=66539 loops=1)
         Sort Key: bpr.id
         Sort Method: quicksort Memory: 6225kB
         -> CTE Scan on bpr (cost=0.00..1264.48 rows=63224 width=8) (actual time=43.273..210.836 rows=67260 loops=1)
   -> Sort (cost=264539.00..265066.04 rows=210814 width=4) (actual time=1482.507..1534.476 rows=215414 loops=1)
         Sort Key: binarypackagepublishinghistory.binarypackagerelease
         Sort Method: external sort Disk: 3792kB
         -> Bitmap Heap Scan on binarypackagepublishinghistory (cost=55424.70..245897.13 rows=210814 width=4) (actual time=948.872..1080.985 rows=215414 loops=1)
               Recheck Cond: ((archive = ANY ('{1,534}'::integer[])) AND (status = ANY ('{2,1}'::integer[])) AND (distroarchseries = ANY ('{97,98,96,99,101,102}'::integer[])))
               -> BitmapAnd (cost=55424.70..55424.70 rows=210814 width=0) (actual time=937.270..937.270 rows=0 loops=1)
                     -> Bitmap Index Scan on securebinarypackagepublishinghistory__archive__status__idx (cost=0.00..24201.41 rows=1357031 width=0) (actual time=404.608..404.608 rows=1752450 loops=1)
                           Index Cond: ((archive = ANY ('{1,534}'::integer[])) AND (status = ANY ('{2,1}'::integer[])))
                     -> Bitmap Index Scan on securebinarypackagepublishinghistory__distroarchseries__idx (cost=0.00..31117.63 rows=2055072 width=0) (actual time=486.609..486.609 rows=2197089 loops=1)
                           Index Cond: (distroarchseries = ANY ('{97,98,96,99,101,102}'::integer[]))
 Total runtime: 1899.027 ms
(22 rows)