just_updated.new.sql is 1350ms faster it scans fewer rows

just_updated.new.sql

rubygems_development=# explain analyze SELECT "versions".* FROM "versions" INNER JOIN "rubygems" ON "rubygems"."id" = "versions"."rubygem_id" WHERE ( versions.rubygem_id IN (SELECT versions.rubygem_id  FROM versions  where versions.indexed = 'true' GROUP BY versions.rubygem_id HAVING COUNT(versions.id) > 1 order by max(created_at) desc limit 50)) AND "versions"."indexed" = 'true' ORDER BY "versions"."created_at" DESC LIMIT 50;
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=79874.05..79874.18 rows=50 width=403) (actual time=643.328..643.334 rows=50 loops=1)
   ->  Sort  (cost=79874.05..79874.83 rows=310 width=403) (actual time=643.326..643.330 rows=50 loops=1)
         Sort Key: versions.created_at DESC
         Sort Method: top-N heapsort  Memory: 66kB
         ->  Nested Loop  (cost=79323.62..79863.75 rows=310 width=403) (actual time=635.250..640.431 rows=5406 loops=1)
               ->  Nested Loop  (cost=79323.19..79737.27 rows=50 width=8) (actual time=635.240..635.412 rows=50 loops=1)
                     ->  Limit  (cost=79322.77..79322.89 rows=50 width=12) (actual time=635.215..635.226 rows=50 loops=1)
                           ->  Sort  (cost=79322.77..79439.34 rows=46630 width=12) (actual time=635.214..635.221 rows=50 loops=1)
                                 Sort Key: (max(versions_1.created_at)) DESC
                                 Sort Method: top-N heapsort  Memory: 28kB
                                 ->  HashAggregate  (cost=77307.45..77773.75 rows=46630 width=12) (actual time=587.467..623.398 rows=109056 loops=1)
                                       Group Key: versions_1.rubygem_id
                                       Filter: (count(versions_1.id) > 1)
                                       Rows Removed by Filter: 49725
                                       ->  Seq Scan on versions versions_1  (cost=0.00..69386.92 rows=1056071 width=16) (actual time=0.111..314.078 rows=1052531 loops=1)
                                             Filter: indexed
                                             Rows Removed by Filter: 102992
                     ->  Index Only Scan using rubygems_pkey on rubygems  (cost=0.42..8.28 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=50)
                           Index Cond: (id = versions_1.rubygem_id)
                           Heap Fetches: 50
               ->  Index Scan using index_versions_on_rubygem_id on versions  (cost=0.43..2.30 rows=23 width=403) (actual time=0.004..0.072 rows=108 loops=50)
                     Index Cond: (rubygem_id = rubygems.id)
                     Filter: indexed
                     Rows Removed by Filter: 17
 Planning time: 2.454 ms
 Execution time: 644.594 ms

just_updated.old.sql

rubygems_development=# explain analyze SELECT "versions".* FROM "versions" INNER JOIN "rubygems" ON "rubygems"."id" = "versions"."rubygem_id" WHERE ( versions.rubygem_id IN (SELECT versions.rubygem_id FROM versions GROUP BY versions.rubygem_id HAVING COUNT(versions.id) > 1)) AND "versions"."indexed" = 'true' ORDER BY "versions"."created_at" DESC LIMIT 50;
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=166655.35..166655.47 rows=50 width=403) (actual time=2062.662..2062.674 rows=50 loops=1)
   ->  Sort  (cost=166655.35..167379.23 rows=289553 width=403) (actual time=2062.659..2062.668 rows=50 loops=1)
         Sort Key: versions.created_at DESC
         Sort Method: top-N heapsort  Memory: 75kB
         ->  Hash Join  (cost=80793.89..157036.60 rows=289553 width=403) (actual time=692.992..1624.813 rows=1007250 loops=1)
               Hash Cond: (versions.rubygem_id = rubygems.id)
               ->  Seq Scan on versions  (cost=0.00..69386.92 rows=1056071 width=403) (actual time=0.095..306.017 rows=1052531 loops=1)
                     Filter: indexed
                     Rows Removed by Filter: 102992
               ->  Hash  (cost=80211.01..80211.01 rows=46630 width=8) (actual time=692.812..692.812 rows=117986 loops=1)
                     Buckets: 131072 (originally 65536)  Batches: 2 (originally 1)  Memory Usage: 3334kB
                     ->  Hash Join  (cost=76680.85..80211.01 rows=46630 width=8) (actual time=595.767..672.374 rows=117986 loops=1)
                           Hash Cond: (rubygems.id = versions_1.rubygem_id)
                           ->  Seq Scan on rubygems  (cost=0.00..3083.71 rows=170071 width=4) (actual time=0.014..14.476 rows=170073 loops=1)
                           ->  Hash  (cost=76097.98..76097.98 rows=46630 width=4) (actual time=595.665..595.665 rows=117986 loops=1)
                                 Buckets: 131072 (originally 65536)  Batches: 2 (originally 1)  Memory Usage: 3103kB
                                 ->  HashAggregate  (cost=75165.38..75631.68 rows=46630 width=4) (actual time=542.062..577.487 rows=117986 loops=1)
                                       Group Key: versions_1.rubygem_id
                                       Filter: (count(versions_1.id) > 1)
                                       Rows Removed by Filter: 52011
                                       ->  Seq Scan on versions versions_1  (cost=0.00..69386.92 rows=1155692 width=8) (actual time=0.006..222.221 rows=1155523 loops=1)
 Planning time: 1.594 ms
 Execution time: 2063.960 ms