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)
with bpr as (select binarypackagere lease.id, binarypackagename from binarypackagere lease 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)) gename from agePublishingHi story blishingHistory .binarypackager elease =bpr.id blishingHistory .distroarchseri es in ( 97, 98, 96, 99, 101, 102) blishingHistory .status IN (2, 1) blishingHistory .archive IN (1, 534);
select bpr.binarypacka
bpr,
BinaryPack
WHERE BinaryPackagePu
AND BinaryPackagePu
AND BinaryPackagePu
AND BinaryPackagePu
about 800:
Merge Join (cost=425359. 81..427362. 24 rows=63224 width=4) (actual time=1825. 164..1891. 782 rows=720 loops=1) blishinghistory .binarypackager elease) lease (cost=1770. 43..154514. 79 rows=63224 width=8) (actual time=43. 265..141. 848 rows=67260 loops=1) 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[ ])) lease_binarypac kagename_ 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[ ])) 02..6464. 08 rows=63224 width=8) (actual time=280. 025..302. 738 rows=66539 loops=1) 273..210. 836 rows=67260 loops=1) 00..265066. 04 rows=210814 width=4) (actual time=1482. 507..1534. 476 rows=215414 loops=1) blishinghistory .binarypackager elease blishinghistory (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[ ]))) 70..55424. 70 rows=210814 width=0) (actual time=937. 270..937. 270 rows=0 loops=1)
-> Bitmap Index Scan on securebinarypac kagepublishingh istory_ _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 securebinarypac kagepublishingh istory_ _distroarchseri es__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[ ]))
Merge Cond: (bpr.id = binarypackagepu
CTE bpr
-> Bitmap Heap Scan on binarypackagere
Recheck Cond: (binarypackagename = ANY ('{151425,
-> Bitmap Index Scan on binarypackagere
-> Sort (cost=6306.
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.
-> Sort (cost=264539.
Sort Key: binarypackagepu
Sort Method: external sort Disk: 3792kB
-> Bitmap Heap Scan on binarypackagepu
-> BitmapAnd (cost=55424.
Total runtime: 1899.027 ms
(22 rows)