launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT binarypackagename.name FROM binarypackagename, distroseriespackagecache WHERE binarypackagename.name LIKE '%german%' AND distroseriespackagecache.archive IN (SELECT id FROM archive WHERE purpose IN (1, 4)) AND distroseriespackagecache.binarypackagename = binarypackagename.id ORDER BY name;
                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=63616.34..63616.34 rows=2 width=30) (actual time=845.685..845.693 rows=133 loops=1)
   Sort Key: binarypackagename.name
   Sort Method: quicksort  Memory: 32kB
   Buffers: shared hit=7872 read=149837 written=2
   ->  Hash Join  (cost=258.18..63616.33 rows=2 width=30) (actual time=2.514..845.435 rows=133 loops=1)
         Hash Cond: (distroseriespackagecache.binarypackagename = binarypackagename.id)
         Buffers: shared hit=7872 read=149837 written=2
         ->  Nested Loop  (cost=0.57..63294.96 rows=16997 width=4) (actual time=0.027..759.947 rows=629201 loops=1)
               Buffers: shared hit=7839 read=149813 written=2
               ->  Index Scan using archive__distribution__purpose__distro_archives__key on archive  (cost=0.14..139.27 rows=69 width=4) (actual time=0.008..0.122 rows=38 loops=1)
                     Index Cond: (purpose = ANY ('{1,4}'::integer[]))
                     Buffers: shared hit=12 read=26
               ->  Index Scan using distroseriespackagecache__archive__idx on distroseriespackagecache  (cost=0.43..912.84 rows=246 width=8) (actual time=0.005..16.497 rows=16558 loops=38)
                     Index Cond: (archive = archive.id)
                     Buffers: shared hit=7827 read=149787 written=2
         ->  Hash  (cost=257.02..257.02 rows=48 width=34) (actual time=2.399..2.399 rows=27 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 2kB
               Buffers: shared hit=33 read=24
               ->  Bitmap Heap Scan on binarypackagename  (cost=80.37..257.02 rows=48 width=34) (actual time=2.326..2.386 rows=27 loops=1)
                     Recheck Cond: (name ~~ '%german%'::text)
                     Rows Removed by Index Recheck: 11
                     Buffers: shared hit=33 read=24
                     ->  Bitmap Index Scan on binarypackagename__name__trgm  (cost=0.00..80.36 rows=48 width=0) (actual time=2.313..2.313 rows=38 loops=1)
                           Index Cond: (name ~~ '%german%'::text)
                           Buffers: shared hit=5 read=24
 Total runtime: 845.775 ms
(26 rows)