Comment 8 for bug 590708

Looking at the code, it seems Postgres doesn't choose an optimal query plan when looking through PackageBuild table. To help it out with that, one can do a subselect. The following query improves the situation immensely for me with warm caches on staging:

explain analyze
SELECT
  BinaryPackageBuild.*
  FROM BinaryPackageBuild
  JOIN PackageBuild
    ON BinaryPackageBuild.package_build = PackageBuild.id
  JOIN BuildFarmJob
    ON PackageBuild.build_farm_job = BuildFarmJob.id
  WHERE
    distro_arch_series IN (109, 110, 111, 112, 113, 114) AND
    (BuildFarmJob.status <> 1 OR
     BuildFarmJob.date_finished IS NOT NULL) AND
    BuildFarmJob.status=2 AND
    PackageBuild.archive IN (
      SELECT Archive.id
        FROM PackageBuild
        JOIN Archive
          ON Archive.id = PackageBuild.archive
        WHERE Archive.purpose IN (1,4) AND
              PackageBuild.id = BinaryPackageBuild.package_build)
    AND (1=1)
  ORDER BY BuildFarmJob.date_finished DESC LIMIT 50 OFFSET 0
;
On cold caches, it's even slower (~8s), but right after that it goes down to 250ms on staging (the original query takes 7s on cold caches, but warm caches don't improve it much as it only goes down to 3-4s). The count is still slow, but takes 2s on staging with warm caches (whereas it stays around 4s on the old count query).

This trick would make use of the fact that any reasonably often used indexes would end up somewhere in the cache of Postgres on our 128GB RAM DB servers. It works well for translations. There are probably even better ways to force Postgres to use a query plan which uses better indexes when joining PackageBuild and BinaryPackageBuild with the Archive.