Comment 14 for bug 276950

SELECT DISTINCT BinaryPackageName.id,
                BinaryPackageName.name
FROM BinaryPackageName,
     BinaryPackagePublishingHistory,
     BinaryPackageRelease,
     DistroArchSeries
WHERE BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id
  AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id
  AND DistroArchSeries.distroseries = 103
  AND BinaryPackagePublishingHistory.status IN (2,
                                                1)
  AND BinaryPackagePublishingHistory.archive IN (1,
                                                 534)
  AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id
  AND BinaryPackageName.id 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)
  AND (1=1)

->

cold

 HashAggregate (cost=15994.44..15994.97 rows=53 width=29) (actual time=201944.860..201944.889 rows=53 loops=1)
   -> Hash Join (cost=165.86..15994.14 rows=60 width=29) (actual time=21018.844..201943.066 rows=720 loops=1)
         Hash Cond: (binarypackagepublishinghistory.distroarchseries = distroarchseries.id)
         -> Nested Loop (cost=163.74..15988.25 rows=843 width=33) (actual time=1090.569..201937.187 rows=5186 loops=1)
               -> Nested Loop (cost=163.74..10210.04 rows=3646 width=33) (actual time=204.618..93025.636 rows=67260 loops=1)
                     -> Bitmap Heap Scan on binarypackagename (cost=163.74..301.93 rows=53 width=29) (actual time=151.925..160.867 rows=53 loops=1)
                           Recheck Cond: (id = 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 binarypackagename_pkey (cost=0.00..163.72 rows=53 width=0) (actual time=148.649..148.649 rows=53 loops=1)
                                 Index Cond: (id = 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[]))
                     -> Index Scan using binarypackagerelease_binarypackagename_key on binarypackagerelease (cost=0.00..186.08 rows=69 width=8) (actual time=14.053..1751.127 rows=1269 loops=53)
                           Index Cond: (binarypackagerelease.binarypackagename = binarypackagename.id)
               -> Index Scan using securebinarypackagepublishinghistory_binarypackagerelease_idx on binarypackagepublishinghistory (cost=0.00..1.57 rows=1 width=8) (actual time=1.543..1.618 rows=0 loops=67260)
                     Index Cond: (binarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id)
                     Filter: ((binarypackagepublishinghistory.status = ANY ('{2,1}'::integer[])) AND (binarypackagepublishinghistory.archive = ANY ('{1,534}'::integer[])))
         -> Hash (cost=2.05..2.05 rows=6 width=4) (actual time=0.078..0.078 rows=6 loops=1)
               -> Seq Scan on distroarchseries (cost=0.00..2.05 rows=6 width=4) (actual time=0.048..0.071 rows=6 loops=1)
                     Filter: (distroseries = 103)
 Total runtime: 201945.176 ms

hot

 HashAggregate (cost=15994.44..15994.97 rows=53 width=29) (actual time=677.240..677.259 rows=53 loops=1)
   -> Hash Join (cost=165.86..15994.14 rows=60 width=29) (actual time=27.846..676.389 rows=720 loops=1)
         Hash Cond: (binarypackagepublishinghistory.distroarchseries = distroarchseries.id)
         -> Nested Loop (cost=163.74..15988.25 rows=843 width=33) (actual time=1.964..673.244 rows=5186 loops=1)
               -> Nested Loop (cost=163.74..10210.04 rows=3646 width=33) (actual time=0.419..143.392 rows=67260 loops=1)
                     -> Bitmap Heap Scan on binarypackagename (cost=163.74..301.93 rows=53 width=29) (actual time=0.389..0.570 rows=53 loops=1)
                           Recheck Cond: (id = 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 binarypackagename_pkey (cost=0.00..163.72 rows=53 width=0) (actual time=0.373..0.373 rows=53 loops=1)
                                 Index Cond: (id = 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[]))
                     -> Index Scan using binarypackagerelease_binarypackagename_key on binarypackagerelease (cost=0.00..186.08 rows=69 width=8) (actual time=0.019..2.091 rows=1269 loops=53)
                           Index Cond: (binarypackagerelease.binarypackagename = binarypackagename.id)
               -> Index Scan using securebinarypackagepublishinghistory_binarypackagerelease_idx on binarypackagepublishinghistory (cost=0.00..1.57 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=67260)
                     Index Cond: (binarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id)
                     Filter: ((binarypackagepublishinghistory.status = ANY ('{2,1}'::integer[])) AND (binarypackagepublishinghistory.archive = ANY ('{1,534}'::integer[])))
         -> Hash (cost=2.05..2.05 rows=6 width=4) (actual time=0.061..0.061 rows=6 loops=1)
               -> Seq Scan on distroarchseries (cost=0.00..2.05 rows=6 width=4) (actual time=0.040..0.053 rows=6 loops=1)
                     Filter: (distroseries = 103)
 Total runtime: 677.433 ms