Re: Equivalent queries produce different plans

Craig James <craig_james@emolecules.com> writes:
> 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;

Yeah, the planner does not make any attempt to infer implied
inequalities, so it will not generate the last two clauses for you.
There is machinery in there to infer implied *equalities*, which
is cheaper (fewer operators to consider) and much more useful across
typical queries such as multiway joins on the same keys.  I'm pretty
dubious that it'd be worth the cycles to search for implied
inequalities.

            regards, tom lane