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 t2 where 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 t2 where 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.264 rows=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=1 loops=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(at)rambler-co(dot)ru ICQ: 99-312-438
(910) 405-47-18