Индекс для таблицы игнорируемых.
Индекс для таблицы игнорируемых.

До индекса:

HashAggregate  (cost=85.23..85.73 rows=50 width=4)
   ->  Hash Join  (cost=46.44..84.58 rows=261 width=4)
         Hash Cond: (user_tags.tag_id = tags_values.id)
         ->  Seq Scan on user_tags  (cost=45.14..81.27 rows=522 width=8)
               Filter: (is_favorite AND (NOT (hashed SubPlan 1)))
               SubPlan 1
                 ->  Unique  (cost=45.13..45.14 rows=2 width=2)
                       ->  Sort  (cost=45.13..45.13 rows=2 width=2)
                             Sort Key: ignore_list.userid
                             ->  Append  (cost=0.00..45.12 rows=2 width=2)
                                   ->  Seq Scan on ignore_list  (cost=0.00..45.09 rows=1 width=4)
                                         Filter: (ignored = 12345)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=1.27..1.27 rows=2 width=4)
               ->  Seq Scan on tags_values  (cost=0.00..1.27 rows=2 width=4)
                     Filter: ((value)::text = ANY ('{XXX,YYY}'::text[]))

После индекса:
 HashAggregate  (cost=46.37..46.87 rows=50 width=4)
   ->  Nested Loop  (cost=12.65..45.72 rows=261 width=4)
         ->  Seq Scan on tags_values  (cost=0.00..1.27 rows=2 width=4)
               Filter: ((value)::text = ANY ('{XXX,YYY}'::text[]))
         ->  Bitmap Heap Scan on user_tags  (cost=12.65..22.19 rows=3 width=8)
               Recheck Cond: (tag_id = tags_values.id)
               Filter: (is_favorite AND (NOT (hashed SubPlan 1)))
               ->  Bitmap Index Scan on user_tags_tagid_idx  (cost=0.00..4.33 rows=10 width=0)
                     Index Cond: (tag_id = tags_values.id)
               SubPlan 1
                 ->  Unique  (cost=8.31..8.32 rows=2 width=2)
                       ->  Sort  (cost=8.31..8.31 rows=2 width=2)
                             Sort Key: ignore_list.userid
                             ->  Append  (cost=0.00..8.30 rows=2 width=2)
                                   ->  Index Scan using ignore_list_ignored_key on ignore_list  (cost=0.00..8.27 rows=1 width=4)
                                         Index Cond: (ignored = 12345)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=0)

ymn/lorsource,fat0troll/lorsource,hizel/lorsource,bodqhrohro/lorsource,fat0troll/lorsource,maxcom/lorsource,fat0troll/lorsource,kloun/lorsource,hizel/lorsource,kloun/lorsource,maxcom/lorsource,kloun/lorsource,maxcom/lorsource,ymn/lorsource,bodqhrohro/lorsource,ymn/lorsource,kloun/lorsource,hizel/lorsource,fat0troll/lorsource,ymn/lorsource,bodqhrohro/lorsource,maxcom/lorsource,hizel/lorsource