launchpad-reviewers team mailing list archive

[Colin Watson <cjwatson@xxxxxxxxxxxxx>]

You have been requested to review the proposed merge of lp:~cjwatson/launchpad/db-index-bpph-datecreated into lp:launchpad/db-devel.

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);