Launchpad itself

Merge lp:~wgrant/launchpad/librariangc-lfa-expiry-index into lp:launchpad

  1. librariangc-lfa-expiry-index
  2. Merge into devel
Proposed by William Grant
Status: Merged
Merged at revision: 18022
Proposed branch: lp:~wgrant/launchpad/librariangc-lfa-expiry-index
Merge into: lp:launchpad
Diff against target: 26 lines (+11/-0)
2 files modified
database/schema/patch-2209-77-0.sql (+10/-0)
lib/lp/services/librarianserver/librariangc.py (+1/-0)
To merge this branch: bzr merge lp:~wgrant/launchpad/librariangc-lfa-expiry-index
Reviewer Review Type Date Requested Status
Colin Watson (community) Approve
Review via email: mp+293855@code.launchpad.net

Commit message

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

Description of the change

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.

To post a comment you must log in.
Revision history for this message
Colin Watson (cjwatson) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'database/schema/patch-2209-77-0.sql'
2--- database/schema/patch-2209-77-0.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2209-77-0.sql 2016-05-05 07:51:09 +0000
4@@ -0,0 +1,10 @@
5+-- Copyright 2016 Canonical Ltd. This software is licensed under the
6+-- GNU Affero General Public License version 3 (see the file LICENSE).
7+
8+SET client_min_messages=ERROR;
9+
10+CREATE INDEX libraryfilealias__expires__partial__idx
11+ ON libraryfilealias(expires)
12+ WHERE content IS NOT NULL AND expires IS NOT NULL;
13+
14+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 77, 0);
15
16=== modified file 'lib/lp/services/librarianserver/librariangc.py'
17--- lib/lp/services/librarianserver/librariangc.py 2015-07-08 16:05:11 +0000
18+++ lib/lp/services/librarianserver/librariangc.py 2016-05-05 07:51:09 +0000
19@@ -323,6 +323,7 @@
20 content IS NOT NULL
21 AND expires < CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
22 - interval '1 week'
23+ ORDER BY expires
24 LIMIT %d)
25 """ % chunksize)
26 self.total_expired += cur.rowcount