SQL Query with Limit of 100 (slow)

limit.sql

with inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL))
 SELECT pi.name AS package_name, pi.version AS version, count(*) count
 FROM package_inventory pi LEFT JOIN certnames ON pi.certname_id = certnames.id
 WHERE ((pi.name ~ 'vim' AND pi.name IS NOT NULL)
   AND NOT ((certnames.certname) in  (SELECT inactive_nodes.certname AS certname FROM inactive_nodes)))
 GROUP BY pi.name, pi.version ORDER BY pi.name, pi.version limit 100;

limit_plan.txt

 Limit  (cost=8.50..9007.16 rows=100 width=23) (actual time=4887.974..5197.506 rows=8 loops=1)
   CTE inactive_nodes
     ->  Index Scan using certnames_inactive_idx on certnames certnames_1  (cost=0.12..7.64 rows=1 width=9) (actual time=0.009..0.009 rows=0 loops=1)
   ->  GroupAggregate  (cost=0.86..495016.73 rows=5501 width=23) (actual time=4887.972..5197.502 rows=8 loops=1)
         Group Key: pi.name, pi.version
         ->  Nested Loop Left Join  (cost=0.86..494755.47 rows=27500 width=23) (actual time=4883.578..5195.079 rows=21920 loops=1)
               Filter: (NOT (hashed SubPlan 2))
               ->  Index Scan using package_name_version_idx on package_inventory pi  (cost=0.56..477315.63 rows=55001 width=31) (actual time=4883.522..5171.214 rows=21920 loops=1)
                     Index Cond: (name IS NOT NULL)
                     Filter: (name ~ 'vim'::text)
                     Rows Removed by Filter: 6017660
               ->  Index Scan using certnames_transform_pkey on certnames  (cost=0.28..0.30 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=21920)
                     Index Cond: (pi.certname_id = id)
               SubPlan 2
                 ->  CTE Scan on inactive_nodes  (cost=0.00..0.02 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)
 Planning time: 1.484 ms
 Execution time: 5197.591 ms
(17 rows)

without_limit.sql

explain analyze
 with inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL))
 SELECT pi.name AS package_name, pi.version AS version, count(*) count
 FROM package_inventory pi LEFT JOIN certnames ON pi.certname_id = certnames.id
 WHERE ((pi.name ~ 'vim' AND pi.name IS NOT NULL)
   AND NOT ((certnames.certname) in  (SELECT inactive_nodes.certname AS certname FROM inactive_nodes)))
 GROUP BY pi.name, pi.version ORDER BY pi.name, pi.version;

without_limit_plan.txt

Sort  (cost=61005.44..61019.19 rows=5501 width=23) (actual time=44.910..44.910 rows=8 loops=1)
   Sort Key: pi.name, pi.version
   Sort Method: quicksort  Memory: 25kB
   CTE inactive_nodes
     ->  Index Scan using certnames_inactive_idx on certnames certnames_1  (cost=0.12..7.64 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1)
   ->  HashAggregate  (cost=60601.02..60656.03 rows=5501 width=23) (actual time=44.890..44.898 rows=8 loops=1)
         Group Key: pi.name, pi.version
         ->  Hash Left Join  (cost=691.58..60394.77 rows=27500 width=23) (actual time=6.376..37.760 rows=21920 loops=1)
               Hash Cond: (pi.certname_id = certnames.id)
               Filter: (NOT (hashed SubPlan 2))
               ->  Bitmap Heap Scan on package_inventory pi  (cost=522.26..59056.68 rows=55001 width=31) (actual time=4.477..28.958 rows=21920 loops=1)
                     Recheck Cond: (name ~ 'vim'::text)
                     Heap Blocks: exact=5632
                     ->  Bitmap Index Scan on bar  (cost=0.00..508.51 rows=55001 width=0) (actual time=3.506..3.506 rows=21920 loops=1)
                           Index Cond: (name ~ 'vim'::text)
               ->  Hash  (cost=100.80..100.80 rows=5480 width=17) (actual time=1.885..1.885 rows=5480 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 267kB
                     ->  Seq Scan on certnames  (cost=0.00..100.80 rows=5480 width=17) (actual time=0.002..0.892 rows=5480 loops=1)
               SubPlan 2
                 ->  CTE Scan on inactive_nodes  (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
 Planning time: 0.536 ms
 Execution time: 44.965 ms
(22 rows)