Branch:+register-merge / Specification:+linkbranch time out due to substring matching many tables

[Bryce Harrington]

got this attempting to propose a merge of one of my branches to pitti using the full url to the branch. Tried a couple more times but still got oopses.

Worked correctly when I specified the branch using its short name, "lp:~pitti/postgresql/debian-9.0".

OOPS-1984A11

See also bug 496041 which asks for less hits on substring matches (or better locality/weighting)

The culprit query includes this component:
(SELECT Branch.id
                       FROM Branch
                       WHERE Branch.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%'
                         OR Branch.url = u'https://code.launchpad.net/~pitti/postgresql/debian-9.0')
                    UNION
                      (SELECT Branch.id
                       FROM Branch,
                            Product
                       WHERE Branch.product = Product.id
                         AND Product.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%')
                    UNION
                      (SELECT Branch.id
                       FROM Branch,
                            Person AS OWNER
                       WHERE Branch.OWNER = OWNER.id
                         AND OWNER.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%')
                    UNION
                      (SELECT Branch.id
                       FROM Branch,
                            Distribution,
                            DistroSeries,
                            SourcePackageName
                       WHERE Branch.sourcepackagename = SourcePackageName.id
                         AND Branch.distroseries = DistroSeries.id
                         AND DistroSeries.distribution = Distribution.id
                         AND (SourcePackageName.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%'
                              OR DistroSeries.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%'
                              OR Distribution.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%')

which -hot- has 1 second of table scanning overhead (0.3 in the branch.name check, 0.6 in the person.name check) but may be substantially more given that there are 300K branches and 1.2M person rows.

[Martin Pool (mbp)]

Just bit me in OOPS-2029E2.

[Francis J. Lacoste (flacoste)]

This bug is actually a symptom of bug 372591, which is that unless there is an exact match on the unique name (short URL), we are searching all over the place like the above queries look like.

We should probably do something similar to what Curtis' squad did with the person and target picker (which is kind of what bug 372591 covers.

[Martin Pool (mbp)]

and more recently OOPS-3671d44169e2cdee4fde888380ed0497

what was strange there is that I started proposing the merge by clicking the 'propose merge' button on the branch page, and then accepted the default target branch, so I don't know why it would be doing a heuristic search. it ought to have a perfect match on both.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r16492 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16492>.