derpibooru=# explain analyze select distinct it1.image_id from image_taggings it1
  inner join image_taggings it2 on it2.image_id = it1.image_id
  inner join image_taggings it3 on it3.image_id = it1.image_id
  where it1.tag_id = (select tag_name_to_id('marble pie')) and it2.tag_id = (select tag_name_to_id('pony'))
  limit 25;
                                                                                              QUERY PLAN                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.21..5198.42 rows=25 width=8) (actual time=0.889..7.058 rows=25 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual time=0.504..0.505 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1)
   ->  Unique  (cost=1.69..1253119.18 rows=6029 width=8) (actual time=0.888..7.055 rows=25 loops=1)
         ->  Nested Loop  (cost=1.69..1253070.56 rows=19448 width=8) (actual time=0.888..7.026 rows=623 loops=1)
               ->  Nested Loop  (cost=1.13..1252011.81 rows=116 width=16) (actual time=0.885..6.432 rows=25 loops=1)
                     ->  Index Only Scan using index_image_taggings_on_image_id_and_tag_id on image_taggings it1  (cost=0.56..1224459.50 rows=6086 width=8) (actual time=0.861..6.110 rows=27 loops=1)
                           Index Cond: (tag_id = $0)
                           Heap Fetches: 16
                     ->  Index Only Scan using index_image_taggings_on_image_id_and_tag_id on image_taggings it2  (cost=0.56..4.53 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=27)
                           Index Cond: ((image_id = it1.image_id) AND (tag_id = $1))
                           Heap Fetches: 15
               ->  Index Only Scan using index_image_taggings_on_image_id_and_tag_id on image_taggings it3  (cost=0.56..7.45 rows=168 width=8) (actual time=0.002..0.022 rows=25 loops=25)
                     Index Cond: (image_id = it2.image_id)
                     Heap Fetches: 388
 Planning Time: 0.296 ms
 Execution Time: 7.082 ms
(19 rows)

derpibooru=# explain analyze select distinct it1.image_id from image_taggings it1
  inner join image_taggings it2 on it2.image_id = it1.image_id
  where it1.tag_id = (select tag_name_to_id('quibble pants')) and it2.tag_id = (select tag_name_to_id('pony'))
  limit 25;
                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=43359.20..43359.32 rows=25 width=8) (actual time=45271.620..45271.628 rows=25 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual time=0.430..0.431 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual time=0.038..0.038 rows=1 loops=1)
   ->  Unique  (cost=43358.68..43359.26 rows=116 width=8) (actual time=45271.619..45271.626 rows=25 loops=1)
         ->  Sort  (cost=43358.68..43358.97 rows=116 width=8) (actual time=45271.618..45271.622 rows=25 loops=1)
               Sort Key: it1.image_id
               Sort Method: quicksort  Memory: 109kB
               ->  Hash Join  (cost=21779.13..43354.70 rows=116 width=8) (actual time=45065.496..45271.410 rows=1301 loops=1)
                     Hash Cond: (it1.image_id = it2.image_id)
                     ->  Bitmap Heap Scan on image_taggings it1  (cost=71.73..21631.32 rows=6086 width=8) (actual time=1.886..74.595 rows=1751 loops=1)
                           Recheck Cond: (tag_id = $0)
                           Heap Blocks: exact=1332
                           ->  Bitmap Index Scan on index_image_taggings_on_tag_id  (cost=0.00..70.21 rows=6086 width=0) (actual time=1.508..1.508 rows=1751 loops=1)
                                 Index Cond: (tag_id = $0)
                     ->  Hash  (cost=21631.32..21631.32 rows=6086 width=8) (actual time=45060.938..45060.938 rows=1331816 loops=1)
                           Buckets: 131072 (originally 8192)  Batches: 32 (originally 1)  Memory Usage: 3073kB
                           ->  Bitmap Heap Scan on image_taggings it2  (cost=71.73..21631.32 rows=6086 width=8) (actual time=64.514..42576.986 rows=1331816 loops=1)
                                 Recheck Cond: (tag_id = $1)
                                 Rows Removed by Index Recheck: 41584271
                                 Heap Blocks: exact=45752 lossy=230455
                                 ->  Bitmap Index Scan on index_image_taggings_on_tag_id  (cost=0.00..70.21 rows=6086 width=0) (actual time=56.410..56.410 rows=1331816 loops=1)
                                       Index Cond: (tag_id = $1)
 Planning Time: 0.135 ms
 Execution Time: 45271.654 ms
(26 rows)

derpibooru=#