Comment 20 for bug 590708

About the difference in timings on production: my guess is that yes, it's partly statistics and fragmentation (staging, being restored the way it is, has basically zero fragmentation and is fully packed). However, since our entire DB is larger than the RAM on the server, it could also be a case of cold caches (i.e. no queries were using this particular index, for instance). That means that the query would take that long roughly once per DB server restart, which shouldn't be too often. Note that this is just from the perspective from a LP developer with lots of query optimization on Postgres behind him—I am not claiming to know exactly what happens in Postgres other than what explain analyze and \timing (in psql) tell me (and the rest is just educated-guessing :).

FWIW, this query was hitting mostly indexes even in the original version, but the biggest waste of time was in joining all packagebuilds with archives which is unnecessary for this particular query (i.e. you want to join only packagebuilds that you care about). Using a subselect makes postgres prefer such a query plan.