Merge lp:~cjwatson/launchpad/optimise-spec-search into lp:launchpad

[Colin Watson]

Move base clauses for specification searches into a CTE to avoid slow sequential scans.

This should fix timeouts on e.g. Person:+specs; EXPLAIN (ANALYZE, BUFFERS) of the relevant slow query on dogfood goes from about 27s to under 300ms with this change.

[William Grant (wgrant)]

review: Approve (code)

=== modified file 'lib/lp/blueprints/model/specificationsearch.py'
--- lib/lp/blueprints/model/specificationsearch.py	2015-10-26 14:54:43 +0000
+++ lib/lp/blueprints/model/specificationsearch.py	2018-02-21 12:52:03 +0000
@@ -1,4 +1,4 @@
-# Copyright 2013 Canonical Ltd.  This software is licensed under the
+# Copyright 2013-2018 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Helper methods to search specifications."""
@@ -16,11 +16,14 @@
 from storm.expr import (
     And,
     Coalesce,
+    Column,
     Join,
     LeftJoin,
     Not,
     Or,
     Select,
+    Table,
+    With,
     )
 from storm.locals import (
     Desc,
@@ -80,7 +83,18 @@
 
     if not tables:
         tables = [Specification]
-    clauses = base_clauses
+    clauses = []
+    # If there are any base clauses, they typically have good selectivity,
+    # so use a CTE to force PostgreSQL to calculate them up-front rather
+    # than doing a sequential scan for visible specifications.
+    if base_clauses:
+        RelevantSpecification = Table('RelevantSpecification')
+        relevant_specification_cte = With(
+            RelevantSpecification.name,
+            Select(Specification.id, And(base_clauses)))
+        store = store.with_(relevant_specification_cte)
+        clauses.append(Specification.id.is_in(
+            Select(Column('id', RelevantSpecification))))
     product_table, product_clauses = get_specification_active_product_filter(
         context)
     tables.extend(product_table)