Launchpad itself

Merge lp:~wgrant/launchpad/faster-latest-things into lp:launchpad

  1. faster-latest-things
  2. Merge into devel
Proposed by William Grant
Status: Merged
Merged at revision: 18203
Proposed branch: lp:~wgrant/launchpad/faster-latest-things
Merge into: lp:launchpad
Diff against target: 133 lines (+38/-39)
2 files modified
database/schema/patch-2209-67-3.sql (+10/-0)
lib/lp/registry/model/product.py (+28/-39)
To merge this branch: bzr merge lp:~wgrant/launchpad/faster-latest-things
Reviewer Review Type Date Requested Status
Colin Watson (community) Approve
Review via email: mp+306213@code.launchpad.net

Commit message

Let latest questions, specifications and products be efficiently calculated.

Description of the change

Let latest questions, specifications and products be efficiently calculated.

The DB patch will be applied hot.

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-67-3.sql'
2--- database/schema/patch-2209-67-3.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2209-67-3.sql 2016-09-20 13:28:07 +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 question__datecreated__idx ON question(datecreated);
11+CREATE INDEX specification__datecreated__id__idx ON specification(datecreated, id DESC);
12+CREATE INDEX product__datecreated__id__idx ON product(datecreated, id DESC);
13+
14+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 67, 3);
15
16=== modified file 'lib/lp/registry/model/product.py'
17--- lib/lp/registry/model/product.py 2016-09-20 02:35:57 +0000
18+++ lib/lp/registry/model/product.py 2016-09-20 13:28:07 +0000
19@@ -34,6 +34,7 @@
20 And,
21 Coalesce,
22 Desc,
23+ Exists,
24 Join,
25 LeftJoin,
26 Not,
27@@ -1812,7 +1813,7 @@
28 def get_all_active(cls, user, eager_load=True):
29 clause = cls.getProductPrivacyFilter(user)
30 result = IStore(Product).find(Product, Product.active,
31- clause).order_by(Desc(Product.datecreated))
32+ clause).order_by(Desc(Product.datecreated), Product.id)
33 if not eager_load:
34 return result
35
36@@ -1960,71 +1961,59 @@
37 created_before = dateToDatetime(created_before)
38 conditions.append(Product.datecreated <= created_before)
39
40- needs_join = False
41-
42+ subscription_conditions = []
43 if subscription_expires_after is not None:
44 if not isinstance(subscription_expires_after, datetime.datetime):
45 subscription_expires_after = (
46 dateToDatetime(subscription_expires_after))
47- conditions.append(
48+ subscription_conditions.append(
49 CommercialSubscription.date_expires >=
50 subscription_expires_after)
51- needs_join = True
52
53 if subscription_expires_before is not None:
54 if not isinstance(subscription_expires_before, datetime.datetime):
55 subscription_expires_before = (
56 dateToDatetime(subscription_expires_before))
57- conditions.append(
58+ subscription_conditions.append(
59 CommercialSubscription.date_expires <=
60 subscription_expires_before)
61- needs_join = True
62
63 if subscription_modified_after is not None:
64 if not isinstance(subscription_modified_after, datetime.datetime):
65 subscription_modified_after = (
66 dateToDatetime(subscription_modified_after))
67- conditions.append(
68+ subscription_conditions.append(
69 CommercialSubscription.date_last_modified >=
70 subscription_modified_after)
71- needs_join = True
72 if subscription_modified_before is not None:
73 if not isinstance(subscription_modified_before,
74 datetime.datetime):
75 subscription_modified_before = (
76 dateToDatetime(subscription_modified_before))
77- conditions.append(
78+ subscription_conditions.append(
79 CommercialSubscription.date_last_modified <=
80 subscription_modified_before)
81- needs_join = True
82-
83- if needs_join or has_subscription:
84- conditions.append(
85- CommercialSubscription.productID == Product.id)
86-
87- if has_subscription is False:
88- conditions.append(SQL('''
89- NOT EXISTS (
90- SELECT 1
91- FROM CommercialSubscription
92- WHERE CommercialSubscription.product = Product.id
93- LIMIT 1)
94- '''))
95-
96- if licenses is not None and len(licenses) > 0:
97- conditions.append(SQL('''EXISTS (
98- SELECT 1
99- FROM ProductLicense
100- WHERE ProductLicense.product = Product.id
101- AND license IN %s
102- LIMIT 1
103- )
104- ''' % sqlvalues(tuple(licenses))))
105-
106- result = IStore(Product).find(
107- Product, *conditions).config(
108- distinct=True).order_by(
109- Product.datecreated, Product.display_name)
110+
111+ assert not subscription_conditions or has_subscription is not False
112+ if subscription_conditions or has_subscription is not None:
113+ subscription_expr = Exists(Select(
114+ 1, tables=[CommercialSubscription],
115+ where=And(*
116+ [CommercialSubscription.productID == Product.id]
117+ + subscription_conditions)))
118+ if has_subscription is False:
119+ subscription_expr = Not(subscription_expr)
120+ conditions.append(subscription_expr)
121+
122+ if licenses:
123+ conditions.append(Exists(Select(
124+ 1, tables=[ProductLicense],
125+ where=And(
126+ ProductLicense.productID == Product.id,
127+ ProductLicense.license.is_in(licenses)))))
128+
129+ result = IStore(Product).find(Product, *conditions).order_by(
130+ Product.datecreated, Desc(Product.id))
131
132 def eager_load(products):
133 return get_precached_products(