Hi,

I have database with two tables:

test1=# \d messages
Table "public.messages"
Column | Type | Modifiers
----------+-----------+-----------
msg_id | integer | not null
sections | 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)
-> Bitmap Index 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)
Total runtime: 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_parts m2
(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)
Total runtime: 203.857 ms
(10 rows)

Test suite can be found at http://www.pgsql.ru/optimizer_bug.tar.gz
(WARNING: 22 MB)

Any suggestions?






---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Alexey Slynko <slynko@tronet.ru> writes:
> Any suggestions?

Fix contrib/intarray to have some selectivity estimation procedures
for its operators?

Without any way to estimate the number of rows matching the @@
condition, the optimizer can hardly be expected to guess right...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match