Re: Equivalent queries produce different plans

Sorry, I forgot to mention: This is 8.1.4, with a fairly ordinary configuration on a 4 GB system.

Craig


Craig James wrote:
> 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 and 1.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=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) (actual time=21.244..21.244
> rows=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.010 rows=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) (actual time=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.508
> rows=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)
> (actual time=21.174..21.174 rows=46947 loops=1)
>                           Index Cond: ((version_id >= 3200000) AND
> (version_id < 3300000))
> Total runtime: 1096.031 ms
> (12 rows)
>