distro +builds page time out

The longest query:

SELECT COUNT(*) FROM Archive, Build, BuildQueue, Distribution, DistroArchSeries, DistroSeries WHERE distroarchseries IN (81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59, 60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18, 19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12) AND NOT (Build.buildstate = 1 AND Build.datebuilt is NULL) AND BuildQueue.build = Build.id AND buildstate=0 AND Build.sourcepackagerelease IN ( SELECT DISTINCT SourcePackageRelease.id FROM SourcePackageRelease JOIN SourcePackagename ON SourcePackageRelease.sourcepackagename = SourcePackageName.id WHERE Sourcepackagename.name LIKE '%' || 'gnome-' || '%') AND Build.distroarchseries = DistroArchSeries.id AND DistroArchSeries.distroseries = DistroSeries.id AND DistroSeries.distribution = Distribution.id AND Distribution.id = Archive.distribution AND Archive.purpose IN (1,4) AND Archive.id = Build.archive AND (1=1)

EXPLAIN ANALYZE on mawson yields :

 Aggregate (cost=36531.83..36531.84 rows=1 width=0) (actual time=64262.322..64262.322 rows=1 loops=1)
   -> Nested Loop (cost=29022.63..36531.82 rows=1 width=0) (actual time=64262.311..64262.311 rows=0 loops=1)
         -> Hash Join (cost=29022.63..36531.44 rows=1 width=4) (actual time=64262.311..64262.311 rows=0 loops=1)
               Hash Cond: (build.sourcepackagerelease = sourcepackagerelease.id)
               -> Hash Join (cost=2.33..7510.91 rows=25 width=8) (actual time=5173.259..6455.543 rows=41 loops=1)
                     Hash Cond: ((build.distroarchseries = distroarchseries.id) AND (distroseries.id = distroarchseries.distroseries))
                     -> Nested Loop (cost=0.00..7502.76 rows=743 width=16) (actual time=20.410..6454.889 rows=410 loops=1)
                           -> Nested Loop (cost=0.00..173.71 rows=27 width=8) (actual time=0.213..0.816 rows=30 loops=1)
                                 -> Nested Loop (cost=0.00..49.10 rows=26 width=12) (actual time=0.154..0.492 rows=20 loops=1)
                                       -> Index Scan using distrorelease_pkey on distroseries (cost=0.00..16.70 rows=30 width=8) (actual time=0.079..0.172 rows=20 loops=1)
                                       -> Index Scan using distribution_pkey on distribution (cost=0.00..1.07 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=20)
                                             Index Cond: (distribution.id = distroseries.distribution)
                                 -> Index Scan using archive__distribution__purpose__key on archive (cost=0.00..4.78 rows=1 width=8) (actual time=0.008..0.012 rows=2 loops=20)
                                       Index Cond: (archive.distribution = distroseries.distribution)
                           -> Index Scan using build__archive__idx on build (cost=0.00..269.10 rows=188 width=16) (actual time=2.489..215.117 rows=14 loops=30)
                                 Index Cond: (build.archive = archive.id)
                                 Filter: (((build.buildstate <> 1) OR (NOT (build.datebuilt IS NULL))) AND (build.buildstate = 0) AND (build.distroarchseries = ANY ('{81,87,80,82,83,84,85,86,77,75,78,73,76,79,74,66,70,67,71,68,72,69,59,60,61,62,65,63,64,47,49,51,52,48,50,38,35,39,36,40,37,16,21,17,28,18,19,14,26,13,25,15,24,8,7,23,9,22,11,10,12}'::integer[])))
                     -> Hash (cost=1.53..1.53 rows=53 width=8) (actual time=0.125..0.125 rows=63 loops=1)
                           -> Seq Scan on distroarchseries (cost=0.00..1.53 rows=53 width=8) (actual time=0.038..0.073 rows=63 loops=1)
               -> Hash (cost=28960.24..28960.24 rows=4805 width=4) (actual time=57806.577..57806.577 rows=29971 loops=1)
                     -> Unique (cost=28888.17..28912.19 rows=4805 width=4) (actual time=57744.078..57784.116 rows=29971 loops=1)
                           -> Sort (cost=28888.17..28900.18 rows=4805 width=4) (actual time=57744.074..57756.782 rows=29971 loops=1)
                                 Sort Key: sourcepackagerelease.id
                                 Sort Method: quicksort Memory: 1215kB
                        ...

The following form is much faster when we are dealing with a huge number of matching sourcepackagereleases:

SELECT COUNT(DISTINCT Build.id)
FROM
    Archive, Build, BuildQueue, Distribution, DistroArchSeries, DistroSeries,
    SourcePackageRelease, SourcePackageName
WHERE
    distroarchseries IN (
        81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69,
        59, 60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17,
        28, 18, 19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
    AND NOT (Build.buildstate = 1 AND Build.datebuilt is NULL)
    AND BuildQueue.build = Build.id
    AND buildstate=0
    AND Build.sourcepackagerelease = SourcePackageRelease.id
    AND SourcePackageRelease.sourcepackagename = SourcePackageName.id
    AND SourcepackageName.name LIKE '%' || 'gnome-' || '%'
    AND Build.distroarchseries = DistroArchSeries.id
    AND DistroArchSeries.distroseries = DistroSeries.id
    AND DistroSeries.distribution = Distribution.id
    AND Distribution.id = Archive.distribution
    AND Archive.purpose IN (1,4)
    AND Archive.id = Build.archive;

We should test this variant on edge or staging to see if it also performs well for more normal requests.

The above query is to optimize the slow query in OOPS-1083D1281. The other 4 OOPSes are failing with a later query being too slow.

An optimized version of that query is:

SELECT Build.*
FROM Build
WHERE
    Build.distroarchseries IN (
        81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
        60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
        19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
    AND buildstate = 1
    AND datebuilt IS NOT NULL
    AND archive IN (
        SELECT Archive.id
        FROM
            Archive
            JOIN Distribution ON Archive.distribution = Distribution.id
            JOIN DistroSeries ON Distribution.id = DistroSeries.distribution
            JOIN DistroArchSeries ON DistroSeries.id = DistroArchSeries.distroseries
        WHERE
            Archive.purpose IN (1,4)
            AND DistroArchSeries.id IN (
                81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
                60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
                19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
        )
ORDER BY Build.datebuilt DESC
LIMIT 75 OFFSET 0;

I'm not sure if we need to check the distroarchseries in both places. The following is faster still:
SELECT Build.*
FROM Build
WHERE
    buildstate = 1
    AND datebuilt IS NOT NULL
    AND archive IN (
        SELECT Archive.id
        FROM
            Archive
            JOIN Distribution ON Archive.distribution = Distribution.id
            JOIN DistroSeries ON Distribution.id = DistroSeries.distribution
            JOIN DistroArchSeries ON DistroSeries.id = DistroArchSeries.distroseries
        WHERE
            Archive.purpose IN (1,4)
            AND DistroArchSeries.id IN (
                81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
                60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
                19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
        )
ORDER BY Build.datebuilt DESC
LIMIT 75 OFFSET 0;

Or even better:

SELECT Build.*
FROM Build, Archive
WHERE
    Build.archive = Archive.id
    AND Archive.purpose IN (1,4)
    AND buildstate = 1
    AND datebuilt IS NOT NULL
    AND Build.distroarchseries IN (
                81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
                60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
                19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
ORDER BY Build.datebuilt DESC
LIMIT 75 OFFSET 0;

On Thu, Mar 05, 2009 at 09:33:25PM -0000, Muharem Hrnjadovic wrote:
> ** Changed in: soyuz
> Status: Triaged => New

Why was this changed from triaged back to new?

--
Diogo M. Matsubara

revisions 7921 and 7923