To: Craig James <craig_james@emolecules.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Equivalent queries produce different plans 
In-reply-to: <469429FD.6080504@emolecules.com> 
References: <46898AB3.7090409@emolecules.com>
	<87hcom5p41.fsf@oxford.xeocode.com>
	<469429FD.6080504@emolecules.com>
Comments: In-reply-to Craig James <craig_james@emolecules.com>
	message dated "Tue, 10 Jul 2007 17:53:17 -0700"
Date: Tue, 10 Jul 2007 21:25:03 -0400
Message-ID: <13642.1184117103@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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