Optimizer bug in 8.1.0? - Mailing list pgsql-hackers

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?