Equivalent queries produce different plans - Mailing list pgsql-performance

The two queries below produce different plans.

select r.version_id, r.row_num, m.molkeys from my_rownum r
join my_molkeys m on (r.version_id = m.version_id)
where r.version_id >= 3200000
and   r.version_id <  3300000
order by r.version_id;


select r.version_id, r.row_num, m.molkeys from my_rownum r
join my_molkeys m on (r.version_id = m.version_id)
where r.version_id >= 3200000
and   r.version_id <  3300000
and   m.version_id >= 3200000
and   m.version_id <  3300000
order by r.version_id;

I discovered this while looking at the plans for the first query.  It seemed to be ignoring the fact that it could push
the"between" condition along to the second table, since the condition and the join are on the same indexed columns.
So,I added a redundant condition, and bingo, it was a lot faster.  In the analysis shown below, the timing (about 1.0
and1.5 seconds respectively) are for a "hot" database that's been queried a couple of times.  In real life on a "cold"
database,the times are more like 10 seconds and 21 seconds, so it's quite significant. 

Thanks,
Craig



db=> explain analyze
db-> select r.version_id, r.row_num, m.molkeys from my_rownum r
db-> join my_molkeys m on (r.version_id = m.version_id)
db-> where r.version_id >= 3200000
db-> and   r.version_id <  3300000
db-> order by r.version_id;

 Sort  (cost=264979.51..265091.06 rows=44620 width=366) (actual time=1424.126..1476.048 rows=46947 loops=1)
   Sort Key: r.version_id
   ->  Nested Loop  (cost=366.72..261533.64 rows=44620 width=366) (actual time=41.649..1186.331 rows=46947 loops=1)
         ->  Bitmap Heap Scan on my_rownum r  (cost=366.72..41168.37 rows=44620 width=8) (actual time=41.616..431.783
rows=46947loops=1) 
               Recheck Cond: ((version_id >= 3200000) AND (version_id < 3300000))
               ->  Bitmap Index Scan on i_chm_rownum_version_id_4998  (cost=0.00..366.72 rows=44620 width=0) (actual
time=21.244..21.244rows=46947 loops=1) 
                     Index Cond: ((version_id >= 3200000) AND (version_id < 3300000))
         ->  Index Scan using i_chm_molkeys_version_id on my_molkeys m  (cost=0.00..4.93 rows=1 width=362) (actual
time=0.009..0.010rows=1 loops=46947) 
               Index Cond: ("outer".version_id = m.version_id)
 Total runtime: 1534.638 ms
(10 rows)


db=> explain analyze
db-> select r.version_id, r.row_num, m.molkeys from my_rownum r
db-> join my_molkeys m on (r.version_id = m.version_id)
db-> where r.version_id >= 3200000
db-> and r.version_id <    3300000
db-> and m.version_id >=   3200000
db-> and m.version_id <    3300000
db-> order by r.version_id;

 Sort  (cost=157732.20..157732.95 rows=298 width=366) (actual time=985.383..1037.423 rows=46947 loops=1)
   Sort Key: r.version_id
   ->  Hash Join  (cost=41279.92..157719.95 rows=298 width=366) (actual time=502.875..805.402 rows=46947 loops=1)
         Hash Cond: ("outer".version_id = "inner".version_id)
         ->  Index Scan using i_chm_molkeys_version_id on my_molkeys m  (cost=0.00..115717.85 rows=47947 width=362)
(actualtime=0.023..117.270 rows=46947 loops=1) 
               Index Cond: ((version_id >= 3200000) AND (version_id < 3300000))
         ->  Hash  (cost=41168.37..41168.37 rows=44620 width=8) (actual time=502.813..502.813 rows=46947 loops=1)
               ->  Bitmap Heap Scan on my_rownum r  (cost=366.72..41168.37 rows=44620 width=8) (actual
time=41.621..417.508rows=46947 loops=1) 
                     Recheck Cond: ((version_id >= 3200000) AND (version_id < 3300000))
                     ->  Bitmap Index Scan on i_chm_rownum_version_id_4998  (cost=0.00..366.72 rows=44620 width=0)
(actualtime=21.174..21.174 rows=46947 loops=1) 
                           Index Cond: ((version_id >= 3200000) AND (version_id < 3300000))
 Total runtime: 1096.031 ms
(12 rows)