launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #18263
[Merge] lp:~cjwatson/launchpad/db-index-bpph-datecreated into lp:launchpad/db-devel
Colin Watson has proposed merging lp:~cjwatson/launchpad/db-index-bpph-datecreated into lp:launchpad/db-devel.
Commit message:
Index BPPH (datecreated, id) to speed up Archive.getAllPublishedBinaries(created_since_date=).
Requested reviews:
Launchpad code reviewers (launchpad-reviewers): db
Stuart Bishop (stub): db
Related bugs:
Bug #1441729 in Launchpad itself: "Archive.getPublishedBinaries(created_since_date=) is very slow"
https://bugs.launchpad.net/launchpad/+bug/1441729
For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/db-index-bpph-datecreated/+merge/255539
In the new ddeb world order, ddeb-retriever will need to use Archive.getPublishedBinaries(created_since_date=) on the webservice to catch up with binary publications. This is very slow right now because BPPH.datecreated is unindexed, and it often times out on qastaging. There's currently an index on SPPH (datecreated, id), so let's have a BPPH version of that too.
This will take some time to create; it should be applied live using CREATE INDEX CONCURRENTLY.
Timings on dogfood:
launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT COUNT(*) FROM BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease WHERE BinaryPackagePublishingHistory.archive = 1 AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.binarypackagename = BinaryPackageName.id AND BinaryPackagePublishingHistory.status IN (2) AND BinaryPackagePublishingHistory.datecreated >= '2015-03-01 00:00:00+00:00' AND (1=1);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=885162.09..885162.10 rows=1 width=0) (actual time=4922.615..4922.616 rows=1 loops=1)
Buffers: shared read=287546
-> Nested Loop (cost=34913.25..885161.71 rows=154 width=0) (actual time=4922.612..4922.612 rows=0 loops=1)
Buffers: shared read=287546
-> Nested Loop (cost=34912.82..883857.71 rows=154 width=4) (actual time=4922.612..4922.612 rows=0 loops=1)
Buffers: shared read=287546
-> Bitmap Heap Scan on binarypackagepublishinghistory (cost=34912.39..882600.03 rows=154 width=8) (actual time=4922.610..4922.610 rows=0 loops=1)
Recheck Cond: ((archive = 1) AND (status = 2))
Rows Removed by Index Recheck: 12289303
Filter: (datecreated >= '2015-03-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 2603144
Buffers: shared read=287546
-> Bitmap Index Scan on securebinarypackagepublishinghistory__archive__status__idx (cost=0.00..34912.36 rows=1664779 width=0) (actual time=465.737..465.737 rows=2603559 loops=1)
Index Cond: ((archive = 1) AND (status = 2))
Buffers: shared read=7118
-> Index Only Scan using binarypackagename_pkey on binarypackagename (cost=0.42..8.16 rows=1 width=4) (never executed)
Index Cond: (id = binarypackagepublishinghistory.binarypackagename)
Heap Fetches: 0
-> Index Only Scan using binarypackage_pkey on binarypackagerelease (cost=0.44..8.46 rows=1 width=4) (never executed)
Index Cond: (id = binarypackagepublishinghistory.binarypackagerelease)
Heap Fetches: 0
Total runtime: 4922.678 ms
(22 rows)
Time: 4924.305 ms
launchpad_dogfood=# BEGIN;
BEGIN
Time: 0.176 ms
launchpad_dogfood=# CREATE INDEX binarypackagepublishinghistory__datecreated__id__idx ON binarypackagepublishinghistory (datecreated, id);
CREATE INDEX
Time: 122697.012 ms
launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT COUNT(*) FROM BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease WHERE BinaryPackagePublishingHistory.archive = 1 AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.binarypackagename = BinaryPackageName.id AND BinaryPackagePublishingHistory.status IN (2) AND BinaryPackagePublishingHistory.datecreated >= '2015-03-01 00:00:00+00:00' AND (1=1);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=78891.93..78891.94 rows=1 width=0) (actual time=617.535..617.535 rows=1 loops=1)
Buffers: shared hit=28 read=10081, temp written=823
-> Nested Loop (cost=49327.12..78886.21 rows=2289 width=0) (actual time=617.532..617.532 rows=0 loops=1)
Buffers: shared hit=28 read=10081, temp written=823
-> Hash Join (cost=49326.69..59672.10 rows=2289 width=4) (actual time=617.532..617.532 rows=0 loops=1)
Hash Cond: (binarypackagepublishinghistory.binarypackagename = binarypackagename.id)
Buffers: shared hit=28 read=10081, temp written=823
-> Bitmap Heap Scan on binarypackagepublishinghistory (cost=36476.82..45298.46 rows=2289 width=8) (actual time=415.358..415.358 rows=0 loops=1)
Recheck Cond: ((datecreated >= '2015-03-01 00:00:00'::timestamp without time zone) AND (archive = 1) AND (status = 2))
Buffers: shared hit=8 read=7115
-> BitmapAnd (cost=36476.82..36476.82 rows=2289 width=0) (actual time=415.355..415.355 rows=0 loops=1)
Buffers: shared hit=8 read=7115
-> Bitmap Index Scan on binarypackagepublishinghistory__datecreated__id__idx (cost=0.00..1563.08 rows=68068 width=0) (actual time=0.034..0.034 rows=98 loops=1)
Index Cond: (datecreated >= '2015-03-01 00:00:00'::timestamp without time zone)
Buffers: shared hit=4 read=1
-> Bitmap Index Scan on securebinarypackagepublishinghistory__archive__status__idx (cost=0.00..34912.36 rows=1664779 width=0) (actual time=415.313..415.313 rows=2603559 loops=1)
Index Cond: ((archive = 1) AND (status = 2))
Buffers: shared hit=4 read=7114
-> Hash (cost=6721.05..6721.05 rows=373505 width=4) (actual time=200.514..200.514 rows=373505 loops=1)
Buckets: 16384 Batches: 4 Memory Usage: 3289kB
Buffers: shared hit=20 read=2966, temp written=820
-> Seq Scan on binarypackagename (cost=0.00..6721.05 rows=373505 width=4) (actual time=0.017..87.032 rows=373505 loops=1)
Buffers: shared hit=20 read=2966
-> Index Only Scan using binarypackage_pkey on binarypackagerelease (cost=0.44..8.38 rows=1 width=4) (never executed)
Index Cond: (id = binarypackagepublishinghistory.binarypackagerelease)
Heap Fetches: 0
Total runtime: 617.595 ms
(27 rows)
Time: 620.716 ms
--
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~cjwatson/launchpad/db-index-bpph-datecreated into lp:launchpad/db-devel.
=== added file 'database/schema/patch-2209-53-9.sql'
--- database/schema/patch-2209-53-9.sql 1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-53-9.sql 2015-04-08 16:34:31 +0000
@@ -0,0 +1,9 @@
+-- Copyright 2015 Canonical Ltd. This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE INDEX binarypackagepublishinghistory__datecreated__id__idx
+ ON binarypackagepublishinghistory (datecreated, id);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 53, 9);
References
