Possible bug in planner (or planner not enough wise in some cases)

postgres version 8.1

all tables fresh vacuumed/analyzed

Problem table:

somedb=# \d el_comment
                                               Table "public.el_comment"
      Column      |            Type             |                               Modifiers

------------------+-----------------------------+-----------------------------------------------------------------------
 id               | integer                     | not null default nextval(('public.documents_id_seq'::text)::regclass)
 user_id          | integer                     | not null
 text_id          | integer                     | not null
 status           | smallint                    | not null default 0
 parent_id        | integer                     |
Indexes:
    "el_comment_pkey" PRIMARY KEY, btree (id)
    "el_comment_parent_id" btree (parent_id)
    "el_comment_text" btree (text_id)
    "el_comment_user" btree (user_id)
Foreign-key constraints:
    "delete_el_text" FOREIGN KEY (text_id) REFERENCES el_text(id) ON DELETE CASCADE

Problem query:

somedb=# EXPLAIN ANALYZE SELECT count(*) FROM el_comment WHERE ((parent_id IN (SELECT tt.id FROM el_comment as tt WHERE
tt.user_id= 112 AND tt.status=1)) OR (text_id IN (SELECT el_text.id FROM el_text WHERE el_text.user_id = 112))) AND
status=1;
                                                                   QUERY PLAN
      

-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=80641.51..80641.52 rows=1 width=0) (actual time=13528.870..13528.873 rows=1 loops=1)
   ->  Seq Scan on el_comment  (cost=56.07..80352.97 rows=1154156 width=0) (actual time=113.866..13528.705 rows=15
loops=1)
         Filter: ((status = 1) AND ((hashed subplan) OR (hashed subplan)))
         SubPlan
           ->  Index Scan using el_text_user on el_text  (cost=0.00..15.92 rows=12 width=4) (actual time=0.992..82.397
rows=12loops=1) 
                 Index Cond: (user_id = 112)
           ->  Index Scan using el_comment_user on el_comment tt  (cost=0.00..40.14 rows=28 width=4) (actual
time=8.748..21.661rows=14 loops=1) 
                 Index Cond: (user_id = 112)
                 Filter: (status = 1)
 Total runtime: 13529.189 ms
(10 rows)

Now lets look output of both subqueries:
SELECT tt.id FROM el_comment as tt WHERE tt.user_id = 112 AND tt.status=1:

 2766039
 2766057
 2244101
 1929350
 1929419
 1929439
 1490610
    1052
 2766033
 2421000
 2420878
  611328
    1019
    1646
(14 rows)

and SELECT el_text.id FROM el_text WHERE el_text.user_id = 112

 3758109
   53688
 1947631
 1915372
 1224421
 1011606
   13772
    1017
  463135
  470614
  575691
  916229
(12 rows)

And put these values into query:

planet=# EXPLAIN ANALYZE SELECT count(*) FROM el_comment WHERE ((parent_id IN
(2766039,2766057,2244101,1929350,1929419,1929439,1490610,1052,2766033,2421000,2420878,611328,1019,1646))OR (text_id IN
(3758109,53688,1947631,1915372,1224421,1011606,13772,1017,463135,470614,575691,916229)))AND status=1; 

                                                        QUERY PLAN

 Aggregate  (cost=340.76..340.77 rows=1 width=0) (actual time=9.452..9.453 rows=1 loops=1)
   ->  Bitmap Heap Scan on el_comment  (cost=52.24..340.71 rows=194 width=0) (actual time=5.431..9.269 rows=15 loops=1)
         Recheck Cond: ((parent_id = 2766039) OR (parent_id = 2766057) OR (parent_id = 2244101) OR (parent_id =
1929350)OR (parent_id = 1929419) OR (parent_id = 1929439) OR (parent_id = 1490610) OR (parent_id = 1052) OR (parent_id
=2766033) OR (parent_id = 2421000) OR (parent_id = 2420878) OR (parent_id = 611328) OR (parent_id = 1019) OR (parent_id
=1646) OR (text_id = 3758109) OR (text_id = 53688) OR (text_id = 1947631) OR (text_id = 1915372) OR (text_id = 1224421)
OR(text_id = 1011606) OR (text_id = 13772) OR (text_id = 1017) OR (text_id = 463135) OR (text_id = 470614) OR (text_id
=575691) OR (text_id = 916229)) 
         Filter: (status = 1)
         ->  BitmapOr  (cost=52.24..52.24 rows=194 width=0) (actual time=4.972..4.972 rows=0 loops=1)
               ->  Bitmap Index Scan on el_comment_parent_id  (cost=0.00..2.00 rows=2 width=0) (actual
time=0.582..0.582rows= 
1 loops=1)
                     Index Cond: (parent_id = 2766039)
....
14 same rows
....

               ->  Bitmap Index Scan on el_comment_text  (cost=0.00..2.02 rows=13 width=0) (actual time=0.983..0.983
rows=0loops=1) 
                     Index Cond: (text_id = 3758109)
....
11 same rows
....

 Total runtime: 10.368 ms
(58 rows)

Complete different result (1000x times faster). Issue look like planner can't/dont want try count both subquery's
resultsand use bitmap scan.  
And planner see amount of results from both subqueris small so bitmap scan must be look way better.
That is intended or bug?

PS: i got reasonable fast results via rewrite query as
select count(*) from
(
select t1.id from el_comment as t1 join el_comment as t2 on t1.parent_id=t2.id and t2.user_id=112 and t2.status=1 where
t1.status=1
union
select t1.id from el_comment as t1 join el_text as t2 on t1.text_id=t2.id and t2.user_id=112 and t2.status=1
) as qqq;

but that is just workaround and work 2-5x time slower.


SY Maxim Boguk