[pgsql-jp: 36177] IN演算子を使用したLIKE、NOT LIKE検索の組み合わせについて

[Y.NISHI]

西と申します。

下記の(1)、(2)のSQLを実行させた場合に結果取得までの時間が大きく異なります。
(1)のSQLを実行した時の結果取得までの時間を縮めたいのですが、どのようにす
ればできるのか、また解決方法があるのかが分かりません。

(1)
EXPLAIN ANALYZE SELECT book_id FROM book WHERE book_id IN (SELECT
book_id FROM book_title WHERE title LIKE '%-%') AND book_id IN (SELECT
book_id FROM book_title WHERE title NOT LIKE '%e%') ORDER BY book_id
LIMIT 20 OFFSET 0;
--------------------------------------------------------------------------------
QUERY PLAN
Limit (cost=3510.78..3510.79 rows=1 width=4) (actual
time=12020279.436..12020279.737 rows=20 loops=1)
-> Sort (cost=3510.78..3510.79 rows=1 width=4) (actual
time=12020279.423..12020279.545 rows=20 loops=1)
Sort Key: book.book_id
-> Nested Loop IN Join (cost=1792.32..3510.77 rows=1 width=4) (actual
time=28030.960..12020277.659 rows=210 loops=1)
Join Filter: ("outer".book_id = "inner".book_id)
-> Nested Loop (cost=1792.32..1798.30 rows=1 width=8) (actual
time=781.478..55302.127 rows=61997 loops=1)
-> HashAggregate (cost=1792.32..1792.32 rows=1 width=4) (actual
time=781.412..6582.171 rows=61997 loops=1)
-> Seq Scan on book_title (cost=0.00..1792.31 rows=1 width=4) (actual
time=0.038..408.758 rows=64543 loops=1)
Filter: (title !~~ '%e%'::text)
-> Index Scan using book_primary_key on book (cost=0.00..5.98 rows=1
width=4) (actual time=0.717..0.741 rows=1 loops=61997)
Index Cond: (book.book_id = "outer".book_id)
-> Seq Scan on book_title (cost=0.00..1792.31 rows=65305 width=4)
(actual time=0.772..190.258 rows=263 loops=61997)
Filter: (title ~~ '%-%'::text)
Total runtime: 12020391.064 ms
--------------------------------------------------------------------------------

(2)
EXPLAIN ANALYZE SELECT book_id FROM book WHERE book_id IN (SELECT
book_id FROM book_title WHERE title LIKE '%-%') AND book_id IN (SELECT
book_id FROM book_title WHERE title LIKE '%e%') ORDER BY book_id LIMIT
20 OFFSET 0;
--------------------------------------------------------------------------------
QUERY PLAN
Limit (cost=12554.06..12554.11 rows=20 width=4) (actual
time=1111.320..1111.587 rows=20 loops=1)
-> Sort (cost=12554.06..12685.45 rows=52557 width=4) (actual
time=1111.306..1111.397 rows=20 loops=1)
Sort Key: book.book_id
-> Hash Join (cost=4198.16..8433.17 rows=52557 width=4) (actual
time=435.390..1110.938 rows=40 loops=1)
Hash Cond: ("outer".book_id = "inner".book_id)
-> Hash Join (cost=2099.08..5521.51 rows=57402 width=8) (actual
time=132.122..905.284 rows=749 loops=1)
Hash Cond: ("outer".book_id = "inner".book_id)
-> Seq Scan on book (cost=0.00..2534.94 rows=62694 width=4) (actual
time=7.898..465.134 rows=62694 loops=1)
-> Hash (cost=1955.58..1955.58 rows=57402 width=4) (actual
time=121.853..121.853 rows=0 loops=1)
-> HashAggregate (cost=1955.58..1955.58 rows=57402 width=4) (actual
time=112.040..117.726 rows=749 loops=1)
-> Seq Scan on book_title (cost=0.00..1792.31 rows=65305 width=4)
(actual time=0.188..107.470 rows=762 loops=1)
Filter: (title ~~ '%e%'::text)
-> Hash (cost=1955.58..1955.58 rows=57402 width=4) (actual
time=200.010..200.010 rows=0 loops=1)
-> HashAggregate (cost=1955.58..1955.58 rows=57402 width=4) (actual
time=196.139..198.552 rows=250 loops=1)
-> Seq Scan on book_title (cost=0.00..1792.31 rows=65305 width=4)
(actual time=17.070..194.247 rows=263 loops=1)
Filter: (title ~~ '%-%'::text)
Total runtime: 1140.894 ms
--------------------------------------------------------------------------------

環境は次の通りです。
【CPU】Pentium4 2.0GHz
【メモリ】512MB
【OS】FreeBSD 4.11
【PostgreSQL】7.4.2
【postgresql.conf】
shared_buffers = 40000
sort_mem = 102400

【bookテーブル】約63,000件
book(book_id, ...)
【book_titleテーブル】約65,000件
book_title(book_id, title_id, title, ...)
※bookテーブルとbook_titleテーブルのカーディナリティは 1:n です。

(1)のSQLを
SELECT book_id FROM book WHERE book_id IN (SELECT book_id FROM
book_title WHERE title LIKE '%-%' AND title NOT LIKE '%e%') ORDER BY
book_id LIMIT 20 OFFSET 0;
に変更することはシステムの都合上難しいため、できるだけSQL文の変更は避け
たいと思っています。

ご教示をお願い致します。
よろしくお願い致します。