launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/librariangc-lfa-expiry-index into lp:launchpad.

Commit message:
Optimise librariangc LFA expiry with a new index and tweaked query.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #1575050 in Launchpad itself: "librarian-gc timing out"
  https://bugs.launchpad.net/launchpad/+bug/1575050

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/librariangc-lfa-expiry-index/+merge/293855

Optimise librariangc LFA expiry with a new index and tweaked query.

PostgreSQL would helpfully choose a seqscan to find expiry candidates, probably because the vast majority of expires dates that are set are way in the past. But adding the index and sort makes it nice and snappy.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/librariangc-lfa-expiry-index into lp:launchpad.

=== added file 'database/schema/patch-2209-77-0.sql'
--- database/schema/patch-2209-77-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-77-0.sql	2016-05-05 07:51:09 +0000
@@ -0,0 +1,10 @@
+-- Copyright 2016 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 libraryfilealias__expires__partial__idx
+    ON libraryfilealias(expires)
+    WHERE content IS NOT NULL AND expires IS NOT NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 77, 0);

=== modified file 'lib/lp/services/librarianserver/librariangc.py'
--- lib/lp/services/librarianserver/librariangc.py	2015-07-08 16:05:11 +0000
+++ lib/lp/services/librarianserver/librariangc.py	2016-05-05 07:51:09 +0000
@@ -323,6 +323,7 @@
                     content IS NOT NULL
                     AND expires < CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
                         - interval '1 week'
+                ORDER BY expires
                 LIMIT %d)
             """ % chunksize)
         self.total_expired += cur.rowcount