Project:+index : Project page timeout on qastaging with 11s of SQL

OOPS-1762QS22

This happened several times on https://qastaging.launchpad.net/malone, the recovered. Perhaps the long query is overly broad and suffers under load or when the cache is cold. It has some EXISTS clauses in it that are probably a good first place to look.

Branch: launchpad-rev-11812
Revno: 11812
SQL time: 11159 ms
Non-sql time: 774 ms
Total time: 11933 ms
Statement Count: 65

1 10007.0 1 SQL-launchpad-main-slave SELECT DistributionSourcePackageCache.archive, DistributionSourcePackageCache.binpkgdescriptions, DistributionSourcePackageCache.binpkgnames, DistributionSourcePackageCache.binpkgsummaries, DistributionSourcePackageCache.changelog, DistributionSourcePackageCache.distribution, DistributionSourcePackageCache.id, DistributionSourcePackageCache.name, DistributionSourcePackageCache.sourcepackagename, SourcePackageName.id, SourcePackageName.name, rank(fti, ftq('malone')) AS rank FROM DistributionSourcePackageCache JOIN SourcePackageName ON DistributionSourcePackageCache.sourcepackagename = SourcePackageName.id WHERE DistributionSourcePackageCache.distribution = 1 AND DistributionSourcePackageCache.archive IN (1, 534) AND (DistributionSourcePackageCache.fti @@ ftq('malone') OR DistributionSourcePackageCache.name ILIKE '%' || 'malone' || '%') AND NOT EXISTS ( SELECT 1 FROM Packaging WHERE Packaging.sourcepackagename = SourcePackageName.id ) AND EXISTS ( SELECT 1 FROM SourcePackageRelease spr JOIN SourcePackagePublishingHistory spph ON spph.sourcepackagerelease = spr.id WHERE spr.sourcepackagename = SourcePackageName.id AND spph.distroseries = 106 ) ORDER BY rank DESC LIMIT 8 OFFSET 0