Colin Watson has proposed merging lp:~cjwatson/launchpad/optimise-spec-search into lp:launchpad.
Commit message: Move base clauses for specification searches into a CTE to avoid slow sequential scans. Requested reviews: Launchpad code reviewers (launchpad-reviewers) For more details, see: https://code.launchpad.net/~cjwatson/launchpad/optimise-spec-search/+merge/338425 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. -- Your team Launchpad code reviewers is requested to review the proposed merge of lp:~cjwatson/launchpad/optimise-spec-search into lp:launchpad.
=== 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)
_______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : launchpad-reviewers@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp