Some strange plans choosed by postgres for one query:

Details:

Postgres version 8.1

Table structure:
media=# \d fast_links
                                     Table "public.fast_links"
   Column   |            Type             |                       Modifiers
------------+-----------------------------+--------------------------------------------------------
 id         | integer                     | not null default nextval('documents_id_seq'::regclass)
 rub_id     | integer                     | not null
 news_id    | integer                     | not null
 flag       | integer                     | default 0
 finish     | timestamp without time zone |
 start      | timestamp without time zone |
 news_dtime | timestamp without time zone |
Indexes:
    "fast_links_pkey" PRIMARY KEY, btree (id)
    "fast_links_rub_flag_start_finish_idx" btree (rub_id, flag, "start", finish)
    "fast_links_test1_idx" btree (rub_id, news_dtime)
    "fast_links_uniq" btree (news_id, rub_id)

media=# select count(*) from fast_links ;
 count
--------
 346170
table vacuumed/analyzed etc...




Now trying 2 same query with a bit different syntax:

More clear writed one:
SELECT l.* from fast_links as l where l.rub_id=260004335 and l.news_dtime=(select max(t2.news_dtime) from fast_links as
t2where t2.rub_id=l.rub_id) 

And alternative way:
SELECT l.* from fast_links as l where l.rub_id=260004335 and l.news_dtime=(select max(t2.news_dtime) from fast_links as
t2where t2.rub_id=260004335) 

And getting 2 different plan with 1000x difference in speed:

media=# EXPLAIN ANALYZE SELECT l.* from fast_links as l where l.rub_id=260004335 and l.news_dtime=(select
max(t2.news_dtime)from fast_links as t2 where t2.rub_id=l.rub_id); 
                                                                                 QUERY PLAN
      

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on fast_links l  (cost=27.46..7051.66 rows=19 width=59) (actual time=5.136..316.579 rows=1 loops=1)
   Recheck Cond: (rub_id = 260004335)
   Filter: (news_dtime = (subplan))
   ->  Bitmap Index Scan on fast_links_rub_flag_start_finish_idx  (cost=0.00..27.46 rows=3845 width=0) (actual
time=2.264..2.264rows=3844 loops=1) 
         Index Cond: (rub_id = 260004335)
   SubPlan
     ->  Result  (cost=1.13..1.14 rows=1 width=0) (actual time=0.067..0.071 rows=1 loops=3843)
           InitPlan
             ->  Limit  (cost=0.00..1.13 rows=1 width=8) (actual time=0.052..0.056 rows=1 loops=3843)
                   ->  Index Scan Backward using fast_links_test1_idx on fast_links t2  (cost=0.00..2520.38 rows=2231
width=8)(actual time=0.044..0.044 rows=1 loops=3843) 
                         Index Cond: (rub_id = $0)
                         Filter: (news_dtime IS NOT NULL)
 Total runtime: 316.736 ms

VS:

media=# EXPLAIN ANALYZE SELECT l.* from fast_links as l where l.rub_id=260004335 and l.news_dtime=(select
max(t2.news_dtime)from fast_links as t2 where t2.rub_id=260004335); 
                                                                               QUERY PLAN
      

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using fast_links_test1_idx on fast_links l  (cost=0.97..4.48 rows=1 width=59) (actual time=0.182..0.187
rows=1loops=1) 
   Index Cond: ((rub_id = 260004335) AND (news_dtime = $1))
   InitPlan
     ->  Result  (cost=0.96..0.97 rows=1 width=0) (actual time=0.151..0.155 rows=1 loops=1)
           InitPlan
             ->  Limit  (cost=0.00..0.96 rows=1 width=8) (actual time=0.129..0.133 rows=1 loops=1)
                   ->  Index Scan Backward using fast_links_test1_idx on fast_links t2  (cost=0.00..3675.70 rows=3845
width=8)(actual time=0.121..0.121 rows=1 loops=1) 
                         Index Cond: (rub_id = 260004335)
                         Filter: (news_dtime IS NOT NULL)
 Total runtime: 0.280 ms

Are i doing something wrong? Or planner cannot optimize such queries right way and i need use second syntax for optimal
performance? 
Look like planner cannot detect here no real dependance between subquery t2.rub_id value and main query.

PS: sorry for bad enlglish again.



SY Maxim Boguk

astar@rambler-co.ru   ICQ: 99-312-438
(910) 405-47-18