PostgreSQL 9.x でサブクエリを使うと Index Scan にならない？

これもいままで見過ごしていた…

「SELECT xxx FROM table_a WHERE xxx IN (SELECT xxx FROM table_b ...) 」だと、table_a(xxx)に対して INDEX を作成してあっても Seq Scan になってしまう…？

jcomi_db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM t_author_info WHERE book_info_id IN (SELECT book_info_id FROM t_book_info WHERE book_title LIKE '%ラブひな%');
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=96.80..96.81 rows=1 width=0) (actual time=4.065..4.066 rows=1 loops=1)
   ->  Hash Semi Join  (cost=59.41..96.76 rows=15 width=0) (actual time=0.542..4.013 rows=14 loops=1)
         Hash Cond: (t_author_info.book_info_id = t_book_info.book_info_id)
         ->  Seq Scan on t_author_info  (cost=0.00..32.78 rows=1678 width=4) (actual time=0.025..1.992 rows=1678 loops=1)
         ->  Hash  (cost=59.23..59.23 rows=14 width=4) (actual time=0.322..0.322 rows=14 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Bitmap Heap Scan on t_book_info  (cost=24.11..59.23 rows=14 width=4) (actual time=0.208..0.287 rows=14 loops=1)
                     Recheck Cond: (book_title ~~ '%ラブひな%'::text)
                     ->  Bitmap Index Scan on idx_t_book_info_book_title_trgm  (cost=0.00..24.11 rows=14 width=0) (actual time=0.155..0.155 rows=14 loops=1)
                           Index Cond: (book_title ~~ '%ラブひな%'::text)
 Total runtime: 4.291 ms
(11 行)

t_author_info(book_info_id) には INDEX を作成してあるので Index Scan になって欲しいところなのに…。

ENABLE_SEQSCAN を OFF にしてみる

jcomi_db=# SET ENABLE_SEQSCAN=OFF;
SET
jcomi_db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM t_author_info WHERE book_info_id IN (SELECT book_info_id FROM t_book_info WHERE book_title LIKE '%ラブひな%');
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=127.57..127.58 rows=1 width=0) (actual time=0.832..0.833 rows=1 loops=1)
   ->  Nested Loop  (cost=24.39..127.54 rows=15 width=0) (actual time=0.292..0.781 rows=14 loops=1)
         ->  Bitmap Heap Scan on t_book_info  (cost=24.11..59.23 rows=14 width=4) (actual time=0.158..0.296 rows=14 loops=1)
               Recheck Cond: (book_title ~~ '%ラブひな%'::text)
               ->  Bitmap Index Scan on idx_t_book_info_book_title_trgm  (cost=0.00..24.11 rows=14 width=0) (actual time=0.110..0.110 rows=14 loops=1)
                     Index Cond: (book_title ~~ '%ラブひな%'::text)
         ->  Index Only Scan using idx_t_author_info_book_info_id on t_author_info  (cost=0.28..4.87 rows=1 width=4) (actual time=0.022..0.025 rows=1 loops=14)
               Index Cond: (book_info_id = t_book_info.book_info_id)
               Heap Fetches: 14
 Total runtime: 1.107 ms
(10 行)

今度は INDEX が使用されるようになった。