Comment 14 for bug 730396

This is what we might look to get with a constraint based batch:

explain analyze SELECT BuildFarmJob.date_finished, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM
BinaryPackageBuild, BuildFarmJob, PackageBuild
WHERE distro_arch_series IN (115, 116, 117, 118) AND
BinaryPackageBuild.package_build = PackageBuild.id AND
 PackageBuild.build_farm_job = BuildFarmJob.id AND
BuildFarmJob.date_finished IS NOT NULL AND BuildFarmJob.status=2 AND PackageBuild.archive IN (1, 534) AND (1=1) and BuildFarmJob.date_finished <= '2010-10-11 22:47:02.721324' ORDER BY BuildFarmJob.date_finished DESC LIMIT 76 ;

                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..1650.59 rows=76 width=24) (actual time=0.096..2346.831 rows=1 loops=1)
   -> Nested Loop (cost=0.00..224414.52 rows=10333 width=24) (actual time=0.094..2346.828 rows=1 loops=1)
         -> Nested Loop (cost=0.00..171707.41 rows=102150 width=12) (actual time=0.073..2218.403 rows=22561 loops=1)
               -> Index Scan Backward using buildfarmjob__date_finished__idx on buildfarmjob (cost=0.00..65921.00 rows=102150 width=12) (actual time=0.046..1507.606 rows=131819 loops=1)
                     Index Cond: (date_finished <= '2010-10-11 22:47:02.721324'::timestamp without time zone)
                     Filter: (status = 2)
               -> Index Scan using packagebuild__build_farm_job__idx on packagebuild (cost=0.00..1.02 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=131819)
                     Index Cond: (packagebuild.build_farm_job = buildfarmjob.id)
                     Filter: (packagebuild.archive = ANY ('{1,534}'::integer[]))
         -> Index Scan using binarypackagebuild__package_build__idx on binarypackagebuild (cost=0.00..0.50 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=22561)
               Index Cond: (binarypackagebuild.package_build = packagebuild.id)
               Filter: (binarypackagebuild.distro_arch_series = ANY ('{115,116,117,118}'::integer[]))
 Total runtime: 2346.932 ms

So we do get a cheaper plan here, but there are so many builds in BFJ that we still examine 130K rows (down from 160K rows) before we've reached the end of the data and can tell we're done. So we need something to handle end-of-batch.

However, and this is the nice news: for batches *before* the end we see a marked improvement:

launchpad_qastaging=> explain analyze SELECT BuildFarmJob.date_finished, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM
BinaryPackageBuild, BuildFarmJob, PackageBuild
WHERE distro_arch_series IN (115, 116, 117, 118) AND
BinaryPackageBuild.package_build = PackageBuild.id AND
 PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status=2 AND PackageBuild.archive IN (1, 534) AND (1=1) and BuildFarmJob.date_finished <= '2010-10-17 22:14:10.941925' ORDER BY BuildFarmJob.date_finished DESC LIMIT 76 ;
                                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..1522.08 rows=76 width=24) (actual time=0.080..21.232 rows=76 loops=1)
   -> Nested Loop (cost=0.00..241550.09 rows=12061 width=24) (actual time=0.079..21.184 rows=76 loops=1)
         -> Nested Loop (cost=0.00..180028.03 rows=119234 width=12) (actual time=0.058..20.277 rows=81 loops=1)
               -> Index Scan Backward using buildfarmjob__date_finished__idx on buildfarmjob (cost=0.00..66363.84 rows=119234 width=12) (actual time=0.034..13.481 rows=760 loops=1)
                     Index Cond: (date_finished <= '2010-10-17 22:14:10.941925'::timestamp without time zone)
                     Filter: (status = 2)
               -> Index Scan using packagebuild__build_farm_job__idx on packagebuild (cost=0.00..0.94 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=760)
                     Index Cond: (packagebuild.build_farm_job = buildfarmjob.id)
                     Filter: (packagebuild.archive = ANY ('{1,534}'::integer[]))
         -> Index Scan using binarypackagebuild__package_build__idx on binarypackagebuild (cost=0.00..0.50 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=81)
               Index Cond: (binarypackagebuild.package_build = packagebuild.id)
               Filter: (binarypackagebuild.distro_arch_series = ANY ('{115,116,117,118}'::integer[]))
 Total runtime: 21.354 ms
(13 rows)

vs

explain analyze SELECT BuildFarmJob.date_finished, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM
BinaryPackageBuild, BuildFarmJob, PackageBuild
WHERE distro_arch_series IN (115, 116, 117, 118) AND
BinaryPackageBuild.package_build = PackageBuild.id AND
 PackageBuild.build_farm_job = BuildFarmJob.id AND
BuildFarmJob.date_finished IS NOT NULL AND BuildFarmJob.status=2 AND PackageBuild.archive IN (1, 534) AND (1=1) ORDER BY BuildFarmJob.date_finished DESC LIMIT 76 OFFSET 2700;
                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=53633.52..55143.21 rows=76 width=24) (actual time=600.192..610.905 rows=76 loops=1)
   -> Nested Loop (cost=0.00..274802.29 rows=13834 width=24) (actual time=170.369..610.472 rows=2776 loops=1)
         -> Nested Loop (cost=0.00..204238.23 rows=136758 width=12) (actual time=170.347..592.729 rows=2847 loops=1)
               -> Index Scan Backward using buildfarmjob__date_finished__idx on buildfarmjob (cost=0.00..82493.38 rows=136758 width=12) (actual time=169.407..438.560 rows=25180 loops=1)
                     Filter: ((date_finished IS NOT NULL) AND (status = 2))
               -> Index Scan using packagebuild__build_farm_job__idx on packagebuild (cost=0.00..0.88 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=25180)
                     Index Cond: (packagebuild.build_farm_job = buildfarmjob.id)
                     Filter: (packagebuild.archive = ANY ('{1,534}'::integer[]))
         -> Index Scan using binarypackagebuild__package_build__idx on binarypackagebuild (cost=0.00..0.50 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=2847)
               Index Cond: (binarypackagebuild.package_build = packagebuild.id)
               Filter: (binarypackagebuild.distro_arch_series = ANY ('{115,116,117,118}'::integer[]))
 Total runtime: 611.015 ms
(12 rows)