cold query:

explain analyze SELECT sum(filesize) from (select distinct on (libraryfile) filesize
launchpad_standalone_1(# FROM BinaryPackageFile,
launchpad_standalone_1(#      LibraryFileAlias,
launchpad_standalone_1(#      LibraryFileContent
launchpad_standalone_1(# WHERE binarypackagerelease in (select distinct binarypackagerelease from BinaryPackagePublishingHistory where BinaryPackagePublishingHistory.archive = 14516 AND BinaryPackagePublishingHistory.dateremoved IS NULL)
launchpad_standalone_1(#     AND BinaryPackageFile.libraryfile = LibraryFileAlias.id
launchpad_standalone_1(#   AND LibraryFileAlias.content = LibraryFileContent.id) as _tmp;
                                                                                                      QUERY PLAN                                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=13903.12..13903.13 rows=1 width=8) (actual time=6648.067..6648.068 rows=1 loops=1)
   ->  Unique  (cost=13900.40..13901.18 rows=155 width=12) (actual time=6646.089..6647.376 rows=695 loops=1)
         ->  Sort  (cost=13900.40..13900.79 rows=155 width=12) (actual time=6646.081..6646.504 rows=695 loops=1)
               Sort Key: binarypackagefile.libraryfile
               Sort Method:  quicksort  Memory: 57kB
               ->  Nested Loop  (cost=11753.52..13894.76 rows=155 width=12) (actual time=2671.928..6643.791 rows=695 loops=1)
                     ->  Nested Loop  (cost=11753.52..13662.76 rows=200 width=8) (actual time=2665.323..4892.766 rows=695 loops=1)
                           ->  Nested Loop  (cost=11753.52..13331.84 rows=200 width=4) (actual time=2661.632..3879.262 rows=695 loops=1)
                                 ->  HashAggregate  (cost=11753.52..11757.28 rows=376 width=4) (actual time=2648.004..2649.444 rows=695 loops=1)
                                       ->  Bitmap Heap Scan on binarypackagepublishinghistory  (cost=94.28..11748.42 rows=2039 width=4) (actual time=66.914..2644.608 rows=1804 loops=1)
                                             Recheck Cond: (archive = 14516)
                                             Filter: (dateremoved IS NULL)
                                             ->  Bitmap Index Scan on securebinarypackagepublishinghistory__archive__status__idx  (cost=0.00..93.77 rows=7082 width=0) (actual time=55.909..55.909 rows=7720 loops=1)
                                                   Index Cond: (archive = 14516)
                                 ->  Index Scan using binarypackagefile_binarypackage_idx on binarypackagefile  (cost=0.00..4.17 rows=1 width=8) (actual time=1.764..1.765 rows=1 loops=695)
                                       Index Cond: (binarypackagefile.binarypackagerelease = binarypackagepublishinghistory.binarypackagerelease)
                           ->  Index Scan using libraryfilealias_pkey on libraryfilealias  (cost=0.00..1.64 rows=1 width=8) (actual time=1.438..1.454 rows=1 loops=695)
                                 Index Cond: (libraryfilealias.id = binarypackagefile.libraryfile)
                     ->  Index Scan using libraryfilecontent_pkey on libraryfilecontent  (cost=0.00..1.15 rows=1 width=12) (actual time=2.512..2.515 rows=1 loops=695)
                           Index Cond: (libraryfilecontent.id = libraryfilealias.content)
 Total runtime: 6648.640 ms
(21 rows)

which tells us:
2.6 seconds of IO to get the bpph rows - required both an index read and table read (a pg limitation at the moment). It considered 7.7K rows and filter to 1804 at that point.
1.2 (3.8-2.6) seconds of IO to do the index lookups on binarypackagefile
1.0 (4.8-3.8) seconds of IO to do the index lookups on libraryfilealias
1.8 (6.6-4.8) seconds of IO to do the index lookups on libraryfilecontent
the sort and sum was essentially free.