Hi,
I have database with two tables:
test1=# \d messages Table "public.messages" Column | Type | Modifiers
----------+-----------+-----------msg_id | integer | not nullsections | integer[] |
Indexes: "messages_pkey" PRIMARY KEY, btree (msg_id) "messages_sect_idx" gist (sections gist__intbig_ops)
and
test1=# \d message_parts Table "public.message_parts" Column | Type | Modifiers
-----------+----------+-----------msg_id | integer |index_fts | tsvector |
Indexes: "a_gist_key" gist (index_fts) "message_parts_msg_id" btree (msg_id)
Number of records are:
test1=# SELECT count(*) from messages ;count
-------41483
(1 row)
and
test1=# SELECT count(*) from message_parts ;count
--------511136
(1 row)
Then, try to execute query:test1=# EXPLAIN ANALYZE SELECT * from
messages m1, message_parts m2 where m1.msg_id = m2.msg_id and
m1.sections @@ '300000210' and m2.index_fts @@ 'mar';
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=11.07..608.20 rows=1 width=481) (actual
time=744.008..5144.721 rows=4 loops=1) -> Bitmap Heap Scan on messages m1 (cost=3.15..118.46 rows=41
width=38) (actual time=1.734..5.737 rows=348 loops=1) Filter: (sections @@ '300000210'::query_int) ->
BitmapIndex Scan on messages_sect_idx (cost=0.00..3.15
rows=41 width=0) (actual time=1.655..1.655 rows=348 loops=1) Index Cond: (sections @@
'300000210'::query_int) -> Bitmap Heap Scan on message_parts m2 (cost=7.92..11.93 rows=1
width=443) (actual time=14.752..14.752 rows=0 loops=348) Recheck Cond: ("outer".msg_id = m2.msg_id)
Filter:(index_fts @@ '''mar'''::tsquery) -> BitmapAnd (cost=7.92..7.92 rows=1 width=0) (actual
time=14.743..14.743 rows=0 loops=348) -> Bitmap Index Scan on message_parts_msg_id
(cost=0.00..2.88 rows=252 width=0) (actual time=0.026..0.026 rows=6
loops=348) Index Cond: ("outer".msg_id = m2.msg_id) -> Bitmap Index Scan on a_gist_key
(cost=0.00..4.79
rows=511 width=0) (actual time=14.966..14.966 rows=1762 loops=342) Index Cond: (index_fts @@
'''mar'''::tsquery)Totalruntime: 5144.859 ms
(14 rows)
And if I turn enable_bitmapscan = off, then:
test1=# SET enable_bitmapscan = off;
test1=# EXPLAIN ANALYZE SELECT * from messages m1, message_parts m2
where m1.msg_id = m2.msg_id and m1.sections @@ '300000210' and
m2.index_fts @@ 'mar'; QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=158.07..2128.36 rows=1 width=481) (actual
time=65.873..203.738 rows=4 loops=1) Hash Cond: ("outer".msg_id = "inner".msg_id) -> Index Scan using a_gist_key on
message_partsm2
(cost=0.00..1967.73 rows=511 width=443) (actual time=0.170..200.361
rows=481 loops=1) Index Cond: (index_fts @@ '''mar'''::tsquery) Filter: (index_fts @@ '''mar'''::tsquery)
-> Hash (cost=157.96..157.96 rows=41 width=38) (actual
time=2.489..2.489 rows=348 loops=1) -> Index Scan using messages_sect_idx on messages m1
(cost=0.00..157.96 rows=41 width=38) (actual time=0.052..2.020 rows=348
loops=1) Index Cond: (sections @@ '300000210'::query_int) Filter: (sections @@
'300000210'::query_int)Totalruntime: 203.857 ms
(10 rows)
Test suite can be found at http://www.pgsql.ru/optimizer_bug.tar.gz
(WARNING: 22 MB)
Any suggestions?
